Data Cubes in Service Manager 2012 – Part 1/3 (Overview and basic handling of Data Cubes)

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 Smiley

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.

image

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.

image

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

image

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.

image

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.

image

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

image

Now the connection to the data source must be saved.

image

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.

image

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

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

image

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

image

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.

image

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.

image

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.

image

image

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 Smiley

regards
Marcel

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

23 Responses to Data Cubes in Service Manager 2012 – Part 1/3 (Overview and basic handling of Data Cubes)

  1. 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?

    Thanks.

    Mike.

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

      Mike.

      • Hey Mike

        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 🙂

        regards
        Marcel

  2. P.Johann says:

    Hi Marcel,
    Thanks for this very good and clear article.
    SM12 is very powerfull and usefull.

  3. diel says:

    Hi Marcel,
    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:).

  4. Diel says:

    Hi Marcel,
    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:)

  5. Pingback: Data Cubes in Service Manager 2012 – Part 2/3 (Publishing Reports) | SCSMfaq.ch

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

  7. will says:

    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.

  8. Zoeb says:

    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?

    • Fabian Laußmann says:

      Hey Marcel,
      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!

  9. Ahsan MIsbah says:

    Hi Marcel
    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. 🙂

    Regards
    M Ahsan

  10. bords says:

    hi,

    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

  11. Pingback: SCSM Data Cubes outdated and MPSync Job fails for Cube Library Management Packs | scsmlab

  12. Pingback: SCSM Data Cubes outdated and MPSync Job fails for Cube Library Management Packs – Stefan Johner

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