In the first blog post of this series I described the basic handling of data cubes, how to create them and how to slice and dice data by using excel. Now let’s go to the next step with Data Cubes in Service Manager 2012 – I will demonstrate how reports can be saved and published in different ways. Have fun reading!

These are the topics of this blog series:

Part 1 – Overview and basic handling of Data Cubes
Part 2 – Publishing Reports (this article)
Part 3 – Creating your own Data Cubes

1. Store Excel Reports in an Analysis Library

So let’s start by creating a shared folder to store our Excel Worksheets to make them accessible to specific people. It doesn’t matter where this folder lives, and if needed you could also have multiple of those folders. Just don’t forget to set appropriate permissions on the folder to make sure everyone gets the permissions they need.

I will set the share permissions to “Full Control” for the system group “Everyone”. To configure the effective permissions I will use NTFS permissions. You should follow your regular group nesting strategy for this folder the way you do it for everything else. For this example however, I will use the easy approach by just giving the groups direct permissions. I will give the “SCSM Administrators” group “Full Control” permissions and all “Authenticated Users” “Read” permissions.

If you want to store multiple Reports in the same folder, you can also create a subfolder structure in it. I will do this by creating subfolders for the different Report types.

Now as the shared folder is ready, we have to add it as an Analysts Library to Service Manager. This can be done by navigating to the “Data Warehouse” area and then use the context menu on the “Analysis Libraries” folder.

As soon as the library is added, it will be visible in the “Reporting” area of the console. This is pretty cool because you can now access regular reports and Excel-based Reports from the same spot.

Now create one or more Excel-based Reports. For details about that check out the first part of this series. I create a single Report and will store it in the library by using a UNC path.

As soon as the report is saved, it will be visible and accessible through the Service Manager console. Just browse the library by using the “Reporting” wunderbar and open the report in Excel.

Another important configuration in this scenario would be the definition how data is refreshed in the report. To configure that switch to the Data tab and hit “properties.

Select the data refresh interval or even select “when opening file”. Configure the refresh parameter as you need it. Then save your report.

2. Store Excel Reports on a SharePoint Server

Another cool thing you can do is publishing reports to a SharePoint Server. When reading this section please keep in mind that I’m not a SharePoint specialist, I’m just a SharePoint service consumer So there may be other approaches that are better from a performance or usability perspective than the ones described here.

Now, because our workbook contains dynamic content from SQL Analysis Services, the SharePoint Server must use Excel Services that is included in SharePoint Server 2010 Enterprise. With that SharePoint will be able to connect back to the Data Cubes and refresh the data within the excel sheets. After creating a report just use the “Save & Send” option in Excel and store the Report directly to a SharePoint library.

For this example I will save the report to a Team Site that contains a documents library. Make sure you enter the correct URL and select the library afterwards. A login prompt may appear where valid credentials need to be entered.

Now let’s take a look at the result. Connect to the SharePoint site and check out the new excel report. In my example I created a very simple Service Desk Team Site and use the documents library where I saved the report to on the entry page. Now open the report and see what happens: the report is opened in the browser and you can see the result.

When opening the file you may get the following error message depending on the user account that you are using.

This is due to a permissions error when the connecting user does not have enough permissions to connect to the data cubes. For authentication to the analysis database windows integrated authentication is used by default. Open the Excel sheet directly in excel to see this setting by using the “data” tab.

The default settings use windows authentication which means, that the user that opens the report also needs access to the data source. This could be changed here, but I will keep the default settings. Now, to give users access to the data source, I will create a group in Active Directory and add the needed users.

Now I will give this group access to the SQL analysis services. I connect to analysis services with the SQL Management Studio. Then I create a new user role for the data cube readers, add the created ADDS group and give permissions to the needed cubes.

After this configuration, users can open excel reports directly from a SharePoint library and also refresh the data within them by connecting directly to the cubes that hold and manage Service Manager-specific data. Pretty cool, huh?

Have fun!
Marcel