One cool new thing in SM12 is the ability to calculate data cubes that allow analysts to slice and dice data to get relevant information out of the data warehouse. This is a good alternative to regular Reporting which can sometimes be very hard to manage, especially when the default reports are not delivering the information you need and you have to customize the reports definitions yourself. This 3 part series will show you how to deal with data cubes in SM12 and will hopefully make your life as a SM12 administrator easier

These are the topics of this blog series:

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

One important thing I have to mention here is that I’m not a SQL reporting or analysis specialist. So I will only cover Service Manager related things with only a little SQL background. If you have some SQL information to share you are very welcome 🙂

1. Overview

The SM12 reporting infrastructure consists of several components. The most important are the following:

• Service Manager DHW Management Server (ETL jobs)
• Service Manager DWH Databases (DWDataMart, DWRepository, DWStagingandConfig)
• Service Manager Reporting Services (Report Definitions)
• Service Manager Analysis Services (Analysis Database to build cubes)
• Cubes (multidimensional databases that allows easy and fast analyzing)

For this blog series I am focussing on the Analysis Services and the data cubes. So let’s check how a default configuration looks like when you have installed the Service Manager components. The Analysis Server should hold an Analysis Database that connects to a minimum of one data warehouse database – the DWDataMart. Based on this information, the cubes are generated on a regular basis. You can check the basic Service Manager Analysis configuration by connecting the SQL Management Studio to the Service Manager Analysis Services Instance.

As you can see, by default there are some cubes available that are stored in specific Management Packs. We’ll take a deeper look at this in a later blog post of this series. For now, we are using the default cubes as they are available. The only thing you have to take care about for now is that the cube you want to analyze data from has been processed. You can check this by using the Service Manager console.

In the data warehouse jobs view you can also see the different cube processing jobs and some details about those.

2. Connect to a data cube

As soon as a cube is ready, we can now connect to it and get some data out. This can be done by using Microsoft Excel. The only thing we have to do is create a new Excel sheet and connect to our Analysis Services database and then select the cube that has the data we need. Let’s go by creating a new Excel sheet and use the data menu to connect to our Analysis Services.

We now have to define the name of the SQL Analysis Services server we use for Service Manager. If SQL Analysis Services have been installed using a named instance make sure you don’t forget to add it in the server name field.

Now the different data sources and the appropriate cubes are presented. For this example I use the cube called “SystemCenterWorkItemsCube”.

Now the connection to the data source must be saved.

As a last step I have to define what to display and where it should be displayed. I simply choose a Pivot Table Report and cell A1 for the start of the table.

After this step you will look at an Excel sheet that looks something like this.

On the right hand side you can see the list with objects and fields to select and display. We’ll take a look at this in an instant. But first we want to check out if there’s an easier way to create the Excel sheet with the connection. Guess what, there’s a much easier way if you have access to the Service Manager console. Just mark the cube you want to connect to and click on the appropriate task. It will do exactly the same thing we have done manually before. But it’s always good to know what happens in the background.

3. Analyze data

As we now have our Excel sheet connected to the cube, it’s time to start analyzing some data. Now start by choosing a Dimension that has the data that you are interested in. In this example I choose the Incident Dimension (IncidentDim) and select some of the fields. The data is displayed immediately in the Excel document.

The fields in the table can now be renamed so that they become better readable. And you can of course add some formatting to the sheet as needed. You can also manually add data outside the pivot table.

Now let’s add another column. I choose “Incidents Active”. By clicking on the little arrow beside the value at the bottom right, you can can change the sorting by moving fields up and down.

Let’s add some more data to the table. If you scroll down a little bit, you will find a dimension called “AssignedToUserDim”. Select the attribute “Display Name” and watch what happens”. The table gets expanded automatically and it will add the Display Names of the users that had an Incident assigned as rows.

Now we want to add the Incident Classifications as well. Again, scroll down until you see “IncidentDim_IncidentClassification” and select the field “IncidentClassificationValue”. Another row will be added to the table holding the values of the classifications for the incidents.

You can collapse the rows if needed to get a better view of the data. Because we added the “Assigned to” field first, all data is now grouped by this field. But this can be changed if needed. Just move the newly added row up and see that the grouping changes immediately in the table.

If you add more rows, the data is grouped multiple times based on the order of the rows.

Now let’s say you want to do some filtering with the data in the table. In this example I add the field “Incident Urgency” to the filter to analyze more deeply on this attribute. Again just hit the small arrow and select “move to report filter”. Instead of using the context menu you could also drag and drop the fields around as needed.

This will add another line at the top of the table that allows to select one or multiple values. Only records that correspond to this value will be displayed in the table. If needed, you can also add multiple fields to the report filter.

Cool, now let’s add some graphics as well. I will first delete some fields so the graphic does not get overloaded. If the table is ready just mark one cell within the table and insert a diagram. Then do some formatting as you like or need it. Of course, when you now change the filter definition within your table, the graphs automatically adjust themselves to the correct values.

Now save the report to a local file so that you can reuse it later. As soon as you open the file, it will connect back to the data source and let’s you change everything as needed. Now start playing around with the available cubes a little bit and find out how far you can go with it. And don’t miss the next blog of this series that takes you to the next step

regards
Marcel