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

Article
03/29/2010

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)

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:

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

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.

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.

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:

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)

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)

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:

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:

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

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.

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.

Comments

Anonymous
February 13, 2012
The comment has been removed

Share via

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

Comments

Additional resources