Data Cubes in Service Manager 2012 – Part 2/3 (Publishing Reports)

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.

image

image

image

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.

image

image

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.

image

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.

image

image

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.

image

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.

image

image

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.

image

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.

image

image

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 Smiley 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.

image

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.

image

image

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.

image

image

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

image

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.

image

image

image

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.

image

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.

image

image

image

image

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

This entry was posted in SCSM and tagged , , , , , , . Bookmark the permalink.

15 Responses to Data Cubes in Service Manager 2012 – Part 2/3 (Publishing Reports)

  1. Pingback: Data Cubes in Service Manager 2012 – Part 1/3 (Overview and basic handling of Data Cubes) | SCSMfaq.ch

  2. Pingback: Data Cubes in Service Manager 2012 – Part 1/3 (Overview and basic handling of Data Cubes) | SCSMfaq.ch

  3. SigmuS says:

    Great article! It was really helpful to unterstand SCSM 2012 Data Cubes Reporting. Will waiting 3rd part :).

  4. Steven says:

    Very good post. I was wondering if you have any article on creating the cubes.
    Thanks

  5. Julia says:

    Thank you for all your great postings. Any idea on timeline for 3/3?

  6. Dennis says:

    im also looking forward for the part 3/3. Hope to see it soon. 🙂

  7. Pingback: Data Cubes in Service Manager 2012 – Part 3/3 (Creating your own Data Cubes) | SCSMfaq.ch

  8. raynald says:

    Hi marcel and thanks for your posts, they are ery helpfull. Just one question about the Service Request Reporting (request offering). By desing, there is no reports for this type of workitems ? How can we generate the reports for the service request ?

    thanks for your help

  9. Pingback: How to create an Incident Dashboard using Excel in System Center 2012 SP1 - Service Manager (SCSM) 2012 - Stay Classy, With System Center Ramblings from San Diego - Site Home - TechNet Blogs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s