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 🙂
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
Great blog Marcel.
I am trying to deploy a cube right now. It has been loaded and shows up in my
Data Warehouse \ Cubes branch, but the status is UnProcessed and the Process Cube Now button does not seem to work. It brings up a dialog “Click OK to mark this cube as “Ready for Process”, I click OK and then nothing happens. When I do this for the default cubes they change status to “Process Job Running”.
There is no job for my cube in the Data Warehouse Jobs branch like there is for the default cubes that came with 2012. Do you know the steps required to create a job? Is a job required?
Well I have moved one step forward. I changed the ReplacementDimension to a second derived class that was based on the same base class as the old one. This new class had some additional properties. Not sure why this one worked, I was not using the new properties. Once this was done the data warehouse job for my cube was created. I it did not complete processing, in the service manager console there was only message indicating the job failed. I had to process the cube in Analysis Services to get an meaningful message. I was referencing a status field that was not on one of my classes.
Sorry for the delay, I’m very busy these days. Will try to take a closer look at this next weekend. But I cannot promise 🙂
Thanks for this very good and clear article.
SM12 is very powerfull and usefull.
Thanks you for the post. This is really useful to me. I’m looking forward to see the part 2 and 3 of this post:).
Im looking for the Part 2 – Creating and Publishing Reports and
Part 3 – Creating your own Data Cubes of this blog and i cant find any. Is it posted already? Thanks:)
No, unfortunately I did not have time to write the second post. As soon as I publish it, I will also link this post to the new one so that it can be found easily.
Just wanted to let you know that the second part is now published. Check it out –> http://blog.scsmfaq.ch/2012/02/06/data-cubes-in-service-manager-2012-part-23-publishing-reports/
Pingback: Data Cubes in Service Manager 2012 – Part 2/3 (Publishing Reports) | SCSMfaq.ch
Pingback: Data Cubes in Service Manager 2012 – Part 3/3 (Creating your own Data Cubes) | SCSMfaq.ch
Is there anyway to filter the data by date, eg. all Incidents created in the last month ao we can do monthly or weekly reporting? I’ve been playing with IncidentDim/CreatedDate but can’t get the PivotTable to group by date.
I have to test this. But if you can convert the date-String to a real date format, then it should work.
Hi will, you can try using PowerPivot. http://nocentdocent.wordpress.com/2012/12/04/yearmonth-filter-and-aggregation-in-an-excel-pivot-table-in-scsm-2012/
Thanks for posting!
Microsoft published a solution to add a Date Dimension directly to the cube : http://blogs.technet.com/b/servicemanager/archive/2012/12/07/incidents-or-service-requests-sliced-by-months-quarters.aspx
Excellent! Thanks for the info!
Hey Marcel, do you have any idea why the IncidentDim Count could come out inaccurate? I have 3 incidents buta higher number shows up…I did have a whole bunch of Test tickets which I deleted using SMLets, and I am suspecting this could be the reason…any idea how this works?
same here. I analyze the Cube via Excel and i get 15 Incidents showing, but if i use the Pivot Table to filter it shows me all Incidents in the filter list, but i cant show them in the table. Like Zoeb I deleted some Incidents via SMLets but the IR Number is higher then the test tickets were.
Thanks for helping!
This article is really helpfull to me.
Kindly can you also tell me, how can I apply date range filter to this report.
I am very thankfull to you for your expedte responce. 🙂
CHeck this –> http://blogs.technet.com/b/servicemanager/archive/2012/12/07/incidents-or-service-requests-sliced-by-months-quarters.aspx
we have a extention class with a enum property. How do we the outrigger enum into existing SystemCenterWorkItemCube to be use for pivot table?
Thanks in advance
Pingback: SCSM Data Cubes outdated and MPSync Job fails for Cube Library Management Packs | scsmlab
Pingback: SCSM Data Cubes outdated and MPSync Job fails for Cube Library Management Packs – Stefan Johner