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.

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.

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.

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.

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

Check if the Cube has been created.

Check if a Cube Processing Job has been added.

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.

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

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