A Deep Dive on Creating Outriggers and Dimensions in the Data Warehouse

A Deep Dive on Creating Outriggers and Dimensions in the Data Warehouse

  • Comments 1

This blog post is intended for partners and customers who are interested in customizing the data warehouse and reporting capabilities in System Center Service Manager. An introduction and definition of key data warehouse MP elements will be given and examples will demonstrate how to define your own elements. 

Service Manager provides a configuration driven data warehouse and reporting platform that can be extended by partners and end users. If this is the first post you are reading you might want to get a high level overview of the DW & Reporting Infra from the following posts:

The System Center Platform in Service Manager Part 6: The Data Warehouse
Data Warehouse and Reporting Overview
Anatomy of Management Pack Synchronization
Reporting Deployment
Anatomy of Extract, Transform, Load (ETL)

The primary source of data for the warehouse is Service Manager CMDB. As we know the data in CMDB is mainly driven by the model i.e. classes, relationships and enumerations.  This data is mapped into DW via a model of its own which is again driven by the same Management pack schema as that of CMDB, and is made up of Dimensions, Facts and Outriggers which loosely correlates to classes, relationships and enumerations and more.  

We'll now take a closer look at the data warehouse elements that users can define.  This post will introduce Outriggers and Dimensions, and a follow-up post will introduce Relationship Facts.  These elements map very closely the industry standard commonly referred to as the Dimensional model.  In Service Manager they derive their identity and structure from the CMDB model and hence the data can be correlated back and forth between CMDB and DW.

Outriggers

An outrigger is a glorified “list” that can logically group together a set of values. Two examples are shown below which display a logical grouping of values for concept “priority” and for the Honda line-up of automobiles.

PRIORITY

Low
Medium
High

 

Honda Automobiles
Accord
Civic
CR-V
Element
Fit
Odyssey

 

For a user, the usefulness of an outrigger is demonstrated in 2 ways:

1) The set of discrete values from an outrigger can be used as a drop down menu for a report parameter when creating and viewing reports in the SM Console.

2) Outrigger values can be used to group data in reports for more advanced analysis.

Outriggers in the data warehouse can target one or more class properties and consolidate them into a single set of discrete values.  These properties can only be of data type String or ManagementPackEnumeration.   When based on an enumeration, outriggers also preserve the hierarchy. An outrigger defined on any other data type other than above is not supported. 

While the benefit of defining an outrigger on an enumeration is more obvious, the advantage of defining an outrigger on a string column is that the infrastructure will coalesce all the distinct values of a property from the instance space into a small list which can then be used in an easy to use drop down list in a report.  A good example of a string based outrigger is the Manufacturer property on the Computer class, which is modeled as a string in CMDB. By defining an Outrigger on that property we can now provide an ability to pick a value from the drop down rather than search on possible list of Manufacturers that one procured the computers from.

Below is an example of how an outrigger is used in a report, in the parameter header. Here you see the ActivityStatus outrigger in Figure 1.  (Click to enlarge)

 

clip_image001

Figure 1: Activity Status Outrigger Parameter in the Activity Distribution report

 

Here is how this outrigger was modeled:

Consider the class System.WorkItem.Activity defined in the MP System.Workitem.Activity.Library:

        <ClassType ID="System.WorkItem.Activity" Accessibility="Public" Base="WorkItem!System.WorkItem" Hosted="false" Abstract="true">
<
Property ID="SequenceId" Type="int" />  
<
Property ID="Notes" Type="richtext" MaxLength="4000" />
 
<
Property ID="Status" Type="enum" EnumType="ActivityStatusEnum" />
 
<
Property ID="Priority" Type="enum" EnumType="ActivityPriorityEnum" />
 
<
Property ID="Area" Type="enum" EnumType="ActivityAreaEnum" />
 
<
Property ID="Stage" Type="enum" EnumType="ActivityStageEnum" />
                  
</
ClassType>

Now we want an outrigger based on the enumeration property Status. Here is how you define it in a MP of your choice.

<Outrigger ID="ActivityStatus" Accessibility="Public">
<
Attribute ID="Status" PropertyPath="$Context/Property[Type='CoreActivity!System.WorkItem.Activity']/Status$" />  
</
Outrigger>

As mentioned above the MP author can define an outrigger on one or more class properties. Each class property will be modeled by a corresponding attribute in the outrigger.

Now let us walk through an example of enumeration based outrigger viz. Activity Status based on ActivityStatusEnum.  Below is how the ActivityStatusEnum looks like. 

<EnumerationTypes>
<
EnumerationValue ID="ActivityStatusEnum" Accessibility="Public" />
<
EnumerationValue ID="ActivityStatusEnum.Ready" Parent="ActivityStatusEnum" Accessibility="Public" Ordinal="5.0" />
<
EnumerationValue ID="ActivityStatusEnum.Active" Parent="ActivityStatusEnum" Accessibility="Public" Ordinal="10.0" /> 
<
EnumerationValue ID="ActivityStatusEnum.OnHold" Parent="ActivityStatusEnum" Accessibility="Public" Ordinal="15.0" />
<
EnumerationValue ID="ActivityStatusEnum.Completed" Parent="ActivityStatusEnum" Accessibility="Public" Ordinal="20.0" />
<
EnumerationValue ID="ActivityStatusEnum.Failed" Parent="ActivityStatusEnum" Accessibility="Public" Ordinal="25.0" />
<EnumerationValue ID="ActivityStatusEnum.Cancelled" Parent="ActivityStatusEnum" Accessibility="Public" Ordinal="30.0" />
<
EnumerationValue ID="ActivityStatusEnum.Rerun" Parent="ActivityStatusEnum" Accessibility="Public" Ordinal="35.0" />
 
...

</
EnumerationTypes>  

Each of these values will now be included in the outrigger set of discrete values.  The following screenshot displays the column ID and ActivityStatusValue from the ActivityStatus outrigger which contains all the enumeration values from ActivityStatusEnum.

 

clip_image003

Figure 2:  ActivityStatus Outrigger containing all the enumeration values of ActivityStatusEnum

 

Looking at the figure above, the ID column from the outrigger will contain all the EnumerationValue ID’s from the ActivityStatus enumeration type.  The ActivityStatusValue is the actual user-friendly display name that is displayed in the report drop-down menus.

Now that we have a basic understanding of how outriggers relate to enumerations and strings, let’s go into further detail on how we construct and model an outrigger.  We again use the outrigger ActivityStatus as an example:


<
Outrigger ID="ActivityStatus" Accessibility="Public">
<
Attribute ID="Status" PropertyPath="$Context/Property[Type='CoreActivity!System.WorkItem.Activity']/Status$" />  
</
Outrigger>

First, let’s look at the <Outrigger> parent tag.  The following table describes the attributes for this tag. 

<Outrigger> Tag Attributes

Attribute Description
ID A unique identifier for the outrigger element.  This will also be the table name of the outrigger in the data warehouse and data mart.
Accessibility This element should always be set to “Public”.

Each <Outrigger> parent tag will contain one or more <Attribute> sub-element tags.  The following table describes the attributes for this tag.

<Attribute> Tag Attributes

Attribute Description
ID A unique identifier for each outrigger attribute
PropertyPath PropertyPath syntax which must uniquely identify the class and attribute that the outrigger attribute is targeting.

 

Dimensions

Suppose a user wants a report in SM to display some information about the attributes for the computers in a particular domain.  For example, the user may want to know the IP address, number of logical processors, and DNS name for each computer.    Using dimensions, the user would be able to bring this data over from SM to the DW where reports can query and display this data for each computer.

A dimension in the SM data warehouse is roughly analogous to a MP class.  Each MP class has a list of properties, while each dimension contains a list of attributes.  Each dimension attribute will map to one property in a class. 

In the SM DW, a dimension always targets a single class. The dimension attributes will then map to the target class’s properties.  So from the example above, in order to get the information about the attributes from a computer, we would have a computer dimension targeted at the Microsoft.Windows.Computers class. 

In certain cases that will be described in further detail below, a dimension may also map to the properties of a target class’s base and derived classes.  So while a dimension may be roughly analogous to a MP class, it can also contain properties which are within that MP class’s hierarchy.

Below is an example of how a dimension is used in an Activity Distribution report.  A dialog box is used to search for dimension instances in the ConfigItemDim dimension, where you can filter on the Display Name property.  The results shown below filter on the keyword “Computer” in Figure 3. (Click to Enlarge)

 

clip_image005

Figure 3: ConfigItemDim report parameter filtering on the keyword “Computer” in the Display Name property

 

After choosing “All Windows Computer” as the dimension object, the report header is updated with the selected filter value: (Click to Enlarge)

 

image

Figure 4: Activity Distribution report parameter header with "All Windows Computers” selected as a filter

 

When the report is rendered, only activities that affect the selected configuration item “All Windows Computers” are displayed.

Let us now take a look at how the dimension was modeled.

First, consider the class System.Entity and System.ConfigItem classes defined in the MP System.Library:

<ClassType ID="System.Entity" Accessibility="Public" Hosted="false" Abstract="true" Singleton="false">  
<
Property ID="DisplayName" Type="string" MinLength="0" Key="false" CaseSensitive="false" MaxLength="4000" /> 
 
</
ClassType>

<ClassType ID="System.ConfigItem" Base="System.Entity" Accessibility="Public" Hosted="false" Abstract="true"> 
<
Property ID="ObjectStatus" Type="enum" EnumType="System.ConfigItem.ObjectStatusEnum" DefaultValue="System.ConfigItem.ObjectStatusEnum.Active" />
 
<
Property ID="AssetStatus" Type="enum" EnumType="System.ConfigItem.AssetStatusEnum" />
 
<
Property ID="Notes" Type="richtext" MaxLength="4000" />
 
</
ClassType>

We want the configuration item dimension to point at the ObjectStatus and AssetStatus properties of System.ConfigItem as well as the DisplayName property of the base class System.Library.  Here is how you define the dimension with only these 3 properties as attributes:

<Dimension ID="ConfigItemDim" Accessibility="Public" Target="System!System.ConfigItem" InferredDimension="true" HierarchySupport="Exact" Reconcile="true">  
<
InclusionAttribute ID="DisplayName" PropertyPath="$Context/Property[Type='System!System.Entity']/DisplayName$" SlowlyChangingAttribute="false" />
 
<
InclusionAttribute ID="ObjectStatus" PropertyPath="$Context/Property[Type='System!System.ConfigItem']/ObjectStatus$" SlowlyChangingAttribute="false" /> 
 
<
InclusionAttribute ID="AssetStatus" PropertyPath="$Context/Property[Type='System!System.ConfigItem']/AssetStatus$" SlowlyChangingAttribute="false" />
 
</
Dimension>

Let’s go into further detail on how we construct and model a dimension by examining the xml schema elements and attributes for a <Dimension>:

<Dimension> Tag Attributes

Attribute

Description

ID

A unique identifier for the dimension element.  This will also be the table name of the dimension in the data warehouse and data mart.

Accessibility

This element should always be set to “Public”

Target

The MP class name which the dimension is targeting

InferredDimension

For v1, this value is always to true.

HierarchySupport

The hierarchy of classes which will help define the properties that will be included in the dimension.  There are 3 possible values:
1)Exact
2)IncludeDerivedClassProperties
3)IncludeExtendedClassProperties
Please see below for more details.

Extends

Optional boolean flag to indicate whether the dimension is a base dimension or is extending another dimension. After a dimension has been defined, the SM DW allows you to “extend” the dimension and add more attributes at a later point in time.

If the Extends flag is set to “true”, the HierarchySupport must be set to “Exact” and all the extension attributes must be listed.  By default, this flag will be set to false.

Reconcile

Optional boolean flag that to indicate whether two instances which are otherwise identical and only differ from which source the data originated should be coalesced into one single row of data. By default, this flag will be set to false. 

Configuration Item related dimensions should have this flag set to true, and Work Item related dimensions will have this flag set to false.  

 

<HierarchySupport>

The HierarchySupport attribute determines which classes are processed and the specific attributes that are included in the dimension. We’ll now examine each possible value in detail:

Exact – When the HierarchySupport attribute is Exact, the user must manually define each attribute that should be included within the dimension using the <InclusionAttribute> tag.  These attributes can be either from the target class or any of the target class’s base and derived classes. 

Each inclusion attribute will correspond to one class property.  Let’s look at each of the attributes within the <InclusionAttribute> tag:

<InclusionAttribute> Tag Attributes

Attribute

Description

ID

A unique identifier for the attribute element. 

PropertyPath

PropertyPath syntax which must uniquely identify the class and attribute that the dimension attribute is targeting.

SlowlyChangingAttribute

In v1, this attribute should always be false

 

The ConfigItemDim dimension example above had a HierarchySupport value of Exact.  Therefore, only the listed inclusion attributes (DisplayName, ObjectStatus, AssetStatus) will be processed in the transform and included in the dimension table in the DW repository and Datamart.

The Exact HierarchySupport value requires the user to manually list each attribute they want in the dimension.  Users, however, will often want all the attributes for a class as well as attributes from its base and/or derived classes to be included within the dimension.  In these cases, it takes a lot of effort to list each attribute explicitly.  Therefore, we have introduced two other HierarchySupport values that will automatically handle these cases for the user.

IncludeExtendedClassProperties – For a dimension with a HierarchySupport of IncludeExtendedClassProperties, all the attributes of the target class and ALL of its base classes will be included in the dimension table and transform. 

Let’s take a look at the following example of CarDimension, which targets the class Car and has a HierarchySupport of IncludeExtendedClassProperties

clip_image009

Figure 5: CarDimension with HierarchySupport of IncludeExtendedClassProperties targeting the Car Class

 

Since CarDimension targets the Car class and has a HierarchySupport value of IncludeExtendedClassProperties, it will process both the Car class and its base class, Vehicle.  The resulting table and transform will contain the following attributes:

CarDimension Attributes

Color
Make
Model
NumDoors
NumCupHolders
Horsepower
CargoSpace

IncludeDerivedClassProperties  – For a dimension with a HierarchySupport of IncludeDerivedClassProperties, all the attributes of the target class, its base classes, and its derived classes will be included in the dimension table and its associated transform. 

Slightly modifying the previous example, CarDimension now has a HierarchySupport of IncludeDerivedClassProperties below.  Since it will process both base and derived classes of the target class, the dimension will now process the attributes of 3 classes – Vehicle, Car, and Sportscar as shown below.

clip_image011

Figure 6: CarDimension with HierarchySupport of IncludeDerivedClassProperties targeting the Car Class

 

The CarDimension dimension table and transform will contain the following attributes:

CarDimension Attributes
Color
Make
Model
NumDoors
NumCupHolders
Horsepower
CargoSpace
TopSpeed

Finally, here are some miscellaneous tips and tricks:

Tips and Tricks

  • For Service Manager Data Warehouse v1, all user defined data warehouse elements must be in sealed management packs.

  • Dimensions with a HierarchySupport value of IncludeDerivedClassProperties cannot target abstract classes

  • For a non-exact dimension, inclusion attributes cannot be defined. The processing of that dimension will automatically determine which attributes are included in the dimension table schema and transform.

  • Users can also define exclusion attributes so that unneeded data is not transformed.  In some cases, it may be easier for the user to define a dimension with a non-exact HierarchySupport, and then define a few exclusion attributes to preclude any irrelevant data.

  • Exclusion attributes cannot be defined for a Dimension that has a HierarchySupport value of Exact, unless the Dimension is an extension dimension.

  • All extension dimensions must have a HierarchySupport value of Exact

More information about dimension extensibility will be added in a future post, where we will explain both how our data warehouse will be able to automatically extend dimensions without any user input to correlate with class extensions, and how users can also manually extend the dimension schema themselves.

Leave a Comment
  • Please add 5 and 7 and type the answer here:
  • Post
  • hey I need a help:

    I've created a custom mp "Signature.Request": with the following class:

    <ClassTypes>

           <ClassType ID="Signature.FullfilmentClass" Accessibility="Public" Abstract="false" Base="WorkItem!System.WorkItem" Hosted="false" Singleton="false" Extension="false">

             <Property ID="Classification" Type="enum" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="false" Scale="0" EnumType="Classification" />

             <Property ID="Resolution" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="4000" MinLength="0" Required="false" Scale="0" />

             <Property ID="RequestRisk" Type="enum" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="false" Scale="0" EnumType="Risk" />

             <Property ID="Notes" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="8192" MinLength="0" Required="false" Scale="0" />

             <Property ID="RequestType" Type="enum" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="true" Scale="0" EnumType="RequestType" />

             <Property ID="TitleName" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="true" Scale="0" />

           </ClassType>

    </ClassTypes>

    and the relationship:

    <RelationshipTypes>

           <RelationshipType ID="AffectedComputer" Accessibility="Public" Abstract="false" Base="System!System.Reference">

             <Source ID="Source_95867301_599f_4506_bd69_53c765228d3a" MinCardinality="0" MaxCardinality="2147483647" Type="Signature.FullfilmentClass" />

             <Target ID="Target_a64f3261_6824_4a72_919b_c4da699aec48" MinCardinality="0" MaxCardinality="1" Type="System!System.Computer" />

           </RelationshipType>

    </RelationshipTypes>

    I want to create a report for this class:

    so I put a reference into the datawarehouse mp named " Signature.Request.DataWarehouse " to my work item class above :

    <Reference Alias="Request">

           <ID>Signature.Request</ID>

           <Version>1.0.0.1</Version>

           <PublicKeyToken>f5de3f4af6b36ef8</PublicKeyToken>

    </Reference>

    and the outriggers dimensions and facts attributes are :

    <Warehouse>

       <Outriggers>

         <Outrigger ID="Class" Accessibility="Public">

           <Attribute ID="ClassName" PropertyPath="$Context/Property[Type='Request!Signature.FullfilmentClass']/Classification$" />

         </Outrigger>

       </Outriggers>

       <Dimensions>

         <Dimension ID="Signature.FullfilmentDim" Accessibility="Public" InferredDimension="true" Target="Request!Signature.FullfilmentClass" HierarchySupport="Exact" Reconcile="false">

           <InclusionAttribute ID="Classification" PropertyPath="$Context/Property[Type='Request!Signature.FullfilmentClass']/Classification$" SlowlyChangingAttribute="false" />

           <InclusionAttribute ID="Resolution" PropertyPath="$Context/Property[Type='Request!Signature.FullfilmentClass']/Resolution$" SlowlyChangingAttribute="false" />

           <InclusionAttribute ID="RequestRisk" PropertyPath="$Context/Property[Type='Request!Signature.FullfilmentClass']/RequestRisk$" SlowlyChangingAttribute="false" />

           <InclusionAttribute ID="Notes" PropertyPath="$Context/Property[Type='Request!Signature.FullfilmentClass']/Notes$" SlowlyChangingAttribute="false" />

           <InclusionAttribute ID="RequestType" PropertyPath="$Context/Property[Type='Request!Signature.FullfilmentClass']/RequestType$" SlowlyChangingAttribute="false" />

           <InclusionAttribute ID="TitleName" PropertyPath="$Context/Property[Type='Request!Signature.FullfilmentClass']/TitleName$" SlowlyChangingAttribute="false" />

         </Dimension>

       </Dimensions>

       <Facts>

         <RelationshipFact ID="AffComputer" Accessibility="Public" Domain="DWBase!Domain.ConfigurationManagement" TimeGrain="Daily" SourceType="Request!Signature.FullfilmentClass">

           <Relationships RelationshipType="Request!AffectedComputer" TargetDimension="Signature.FullfilmentDim" />

         </RelationshipFact>

       </Facts>

    </Warehouse>

    I sealed this mp and imported it to the scsm console .. everything work fine .. the datawarehouse job mpsyncjob import it .. but the deployment failed .. I check the the server manager/ event viewer / microsoft / operations manager .. warnings and errors are thrown .. kindly check them below :

    the first warning :

    Log Name : Operations Manager

    Source Deployment

    Event ID : 33403  ... with the details below:

    EventData

      CreateAndDropIndexSignature.FullfilmentDim

      Signature.Request.DataWarehouse

      1.0.0.2

      Install

      System.Data.SqlClient.SqlException: Incorrect syntax near '.'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.EnterpriseManagement.DataAccessLayer.SqlRetryHandler.ExecuteReader(ExecuteArguments executeArguments, QueryResults& queryResults) at Microsoft.EnterpriseManagement.DataAccessLayer.SqlRetryHandler.Execute[T](ExecuteArguments executeArguments, RetryPolicy retryPolicy, GenericExecute`1 genericExecute) at Microsoft.EnterpriseManagement.DataAccessLayer.SqlRetryHandler.ExecuteReader(SqlCommand sqlCommand, IList`1 prologEpilogList, IList`1 projection, QueryDefinition queryDefinition, RetryPolicy retryPolicy) at Microsoft.EnterpriseManagement.DataAccessLayer.QueryRequest.Execute(SqlNotificationRequest sqlNotificationRequest) at Microsoft.EnterpriseManagement.DataAccessLayer.QueryRequest.Execute() at Microsoft.SystemCenter.ResourceAccessLayer.SqlDbWriter.AddResource(IResource resource) at Microsoft.SystemCenter.ResourceAccessLayer.SqlResourceStore.AddResource(IResource resource) at Microsoft.SystemCenter.DeploymentEngine.SqlDeployer.Install() at Microsoft.SystemCenter.DeploymentEngine.ExecutionManager.Run(DeployerBase deployer) at Microsoft.SystemCenter.DeploymentEngine.ExecutionManager.Run(IXPathNavigable instance)

    after many tries to deploy the mp ... a final error is thrown with the following description:

    Log Name: Operations Manager

    Source : Deployment

    Event ID : 33410

    and with the following details:

    EventData

      CreateViewForEntitySignature.FullfilmentDim_DWDataMart

      Signature.Request.DataWarehouse

      1.0.0.2

      Install

      Could not find object 'Signature.FullfilmentDimvw' or you do not have permission.

    PS:in the DWStagingAndConfig a " dbo.MT_Signature$FullfilmentClass " table is created

      in the DWRepository and DWDataMart a " dbo.Signature.FullfilmentDim " table is created but still empty .. the record aren't sync to be shown into the report

    I appreciate your reply

    Thanks in advance

Page 1 of 1 (1 items)