Data Cubes in Service Manager 2012 – Part 3/3 (Creating your own Data Cubes)

Finally … this is the third part of a series that explains data cubes in Service Manager 2012. The first and the second part have been published in January and February 2012. Sorry for the delay of this last part. This last post of the series will demonstrate how to bring the relevant information to the data warehouse to create a cube based on it. The positive thing is, that I decided to publish some more articles around this topic because there are not many documentations out there that describe this in detail. I will go into much more details  in future posts. But this post should be great for the basic understanding.

These are the topics of this blog series:

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

Step 1 – Preparation

I created a custom class for my Service Manager environment called “Persons” that lives in a sealed Management Pack. The Persons class has attributes like this:

  • ID
  • First Name
  • Last Name
  • Full Name
  • Mail Address
  • etc.

The class also has a one-to-many relationship to the “AD User” class. With this it’s possible to like AD User Accounts to Person Objects.

  • Person has AD User Account

I created some objects. Check these screenshots.

temp

temp

temp2

Because this is a sealed MP, it will be synched to the data warehouse when the MPSync Job runs the next time. You can check if the sync was successful by verifying that the MP is listed in the DWH MP list.

temp

temp

Now we have the structure ready, but we still have no data (no person object details) in the DWH. For this, we need to wait until the complete ETL cycle has transferred the data in the specific DWH databases. Details about this process can be found here.

Step 2 – Create the DWH MP

Now it’s time to prepare the data warehouse dimensions and facts (or even other objects) that we will use later to work with the data. I will not go into any details here because there are very good explanations on the SM Technet Blog. Check these links.

http://blogs.msdn.com/b/scplat/archive/2010/03/29/a-deep-dive-on-creating-relationship-facts-in-the-data-warehouse.aspx

http://blogs.msdn.com/b/scplat/archive/2010/03/29/a-deep-dive-on-creating-outriggers-and-dimensions-in-the-data-warehouse.aspx

http://blogs.technet.com/b/servicemanager/archive/2010/03/30/deep-dive-into-the-data-warehouse-custom-fact-tables-dimensions-and-outriggers.aspx

The new MP could look something like this.

temp

First some references to other MPs are needed. Then I defined a single Dimension and used the class “itnetx.person” as the target.

    <Dimensions>
<Dimension ID=”PersonDim”
Accessibility=”Public”
InferredDimension=”true”
Target=”itnetxIM!itnetx.person”
HierarchySupport=”IncludeExtendedClassProperties”
Reconcile=”true” />
</Dimensions>

One important setting is “HierarchySupport”. With this you can select what properties will be included in the dimension.

  • Exact –> Only the defined attributes in the dimension will be added to the dimension, they must be manually added
  • IncludeExtendedClassProperties –> All attributes of the class and also all attributes of the base classes will be added to the dimension
  • IncludeDerivedClassProperties –> All attributes of the class, all attributes of the base classes and also all attributes of the derived classes will be added to the dimension

The Relationship Fact represents a relationship between objects. In this example, “Persons” can be related to “AD Users”, so such a fact needs to be configured in the MP when reporting is needed based on this information.

    <Facts>
<RelationshipFact ID=”PersonHasADUserAccount”
Accessibility=”Public”
Domain=”DWBase!Domain.ConfigurationManagement”
TimeGrain=”Daily”
SourceType=”itnetxIM!itnetx.person”
SourceDimension=”PersonDim”>
<Relationships RelationshipType=”itnetxIM!itnetx.relationship.personhasaduseraccount” TargetDimension=”DWBase!UserDim” />
</RelationshipFact>
</Facts>

Now the MP can be sealed and imported.

Step 3 – Create Cube MP

After defining the needed base elements in the data warehouse to create standard reports, it’s now time to create the cube. For this, a second MP is needed that relates to the one we just created before. In this MP the Cube is defined and this can be done with just some lines of XML.

temp

  <SystemCenterCube ID=”itnetxIMCube”>
<MeasureGroups>
<MeasureGroup ID=”PersonInformation”
Fact=”MyDWHMP!PersonHasADUserAccount”
DateDimAlias=”PersonHasADUserAccountDateDim” />
</MeasureGroups>
</SystemCenterCube>

As you can see, the cube consists of at least one measure group that needs to be defined with a fact (we created this fact in the first MP). With only one measure group, this will not offer many options when you slice and dice data, but it’s ok for this example.

Now the MP can be sealed and imported.

Take care if you are doing some testing and want to deploy the same MP with an updated cube again. When you delete a MP you also have to delete the cube in SQL Analysis Services. Otherwise the cube deployment will wail.

Step 4 – Verify

Now when both MPs are imported, it needs some time for these steps/jobs to run:

  • MPs need to be synced to DWH (MPSync Job)
  • Data needs to be transferred to DWH (ETL Jobs)
  • Cube needs to be processed (Cube Processing Job)

If you don’t want to wait, you can of course force the jobs to run by manually starting them.

Check if the MPs are in the Data Warehouse.

temp

Check if the Cube has been created.

temp

Check if a Cube Processing Job has been added.

temp

When all data was transferred to the Data Mart and the cube processing was successful, the data is now ready for analysis.

Step 5 – Use the Cube Data

Connect to the newly created cube. The easiest way to achieve this is using the Service Manager console.

temp

After Excel opens, you have the data available to create your report.

temp

Cool! With this knowledge you are now able to create your very own cubes. Of course, there much more things that you can or need to do, but it’s good to start with the basics and them build on top of that. As mentioned at the beginning of this post, I will publish some more articles that go into more detail. So stay tuned for that!

regards
Marcel

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

9 Responses to Data Cubes in Service Manager 2012 – Part 3/3 (Creating your own Data Cubes)

  1. Pingback: Data Cubes in Service Manager 2012 – Part 2/3 (Publishing Reports) | 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:

    It’s a really cool! Thanks a lot! It will be very useful!

  4. Congratulations for the Blog. It is very useful.

    I’m trying to customize the data cube.

    On target = “itnetxIM! Itnetx.person” I know that itnetxIM is a reference. Could you explain what is itnetx.person? Is one of properties Classetype within the custom MP?

    Thanks for the help.

  5. Creating own cubes is not much hard for those who have developing skills. Data cubes in server manager helps in bringing the relevant information to the data warehouse to create a cube.

  6. ScottW says:

    Can you post the XML from the class definition MP so that readers can trace the various elements of the different MPs from the source? When you are new to this, it can be difficult at times to figure out what is XML coding requirements and what is user choice naming conventions.

  7. luis says:

    I would like to know how I can view the history of incidents

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