March, 2010

  • System Center Platform Team

    A Deep Dive on Creating Relationship Facts in the Data Warehouse


    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. 

    This blog post is a continuation from our previous post which introduced outriggers and dimensions.  In this blog post, we will take a look at how the user can define relationship facts in the data warehouse.

    Relationship Facts

    A relationship fact in the SM DW is analogous to a relationship in SM. A relationship fact can be used to help answer queries such as:

    • Which work items are currently assigned to the user “Joe Smith” so we can check their status?
    • I need the list of all the computers in our domain which currently have Windows 7 installed so I can update them to SP1.
    • I need to find all Review Activities that lists Sarah Smith as a reviewer so they can be reassigned since she is on vacation.

    In each of these scenarios, there is a source instance and a target instance that is joined together by some relationship.  Without a relationship fact, it would be difficult to determine these associations between instances.

    Consider the relationship Microsoft.Windows.ComputerHostsOperatingSystem in the MP Microsoft.Windows.Library:

    <RelationshipType ID="Microsoft.Windows.ComputerHostsOperatingSystem" Accessibility="Public" Base="System!System.Hosting">  
    Source ID="Computer" Type="Microsoft.Windows.Computer" />  
    Target ID="OperatingSystem" Type="Microsoft.Windows.OperatingSystem" MaxCardinality="1" />

    In a SM relationship, the source and target are always modeled by a MP class. In this relationship, the class Microsoft.Windows.Computer is the source and the class Microsoft.Windows.OperatingSystem is the target.

    Here’s how you define the corresponding RelationshipFact based on the Microsoft.Windows.ComputerHostsOperatingSystem relationship:

    <RelationshipFact ID="ComputerHostsOperatingSystemFact" Accessibility="Public" Domain="Domain.ConfigurationManagement" TimeGrain="Daily" SourceType="Windows!Microsoft.Windows.Computer" SourceDimension="ComputerDim">  
    Relationships RelationshipType="Windows!Microsoft.Windows.ComputerHostsOperatingSystem" TargetDimension="OperatingSystemDim" />

    Notice how the relationship fact defines a source dimension and a target dimension.  If you look closely, you will notice that the source and target dimension target the source and target classes from the original relationship that the relationship fact is modeled upon.

    In the previous section, we learned about dimensions.  Relationship facts really come into use by associating two dimensions together, allowing reports to take advantage of this association to display important information from each dimension in relation to each other.   For example, the relationship WorkItemAssignedToUser can be used to display information about incidents or change requests for a specific user in the report.  This allows the customer to drill down into the data to find information specific to their needs.  This is just one example how relationship facts are useful to create specialized views of data in reports.

    Let’s now take a deeper look at the attributes and sub-element tags required to model a relationship fact in a user-defined MP.  The first table describes all the attributes for the <RelationshipFact> tag

    <RelationshipFact> Tag Attributes




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


    This element should always be set to “Public”, as the deployment process will create system derived MP’s which will refer to this outrigger during the generation of the automated transforms. 


    The scope of the relationship fact.  Possible values include:
    Instance Management
    Activity Management
    Incident Management
    Change Management
    Problem Management

    The value for this attribute must be an enumeration that is a child of the parent “Domain” enumeration defined in the MP Microsoft.SystemCenter.Datawarehouse.Base


    The granularity of the relationship fact.  Value must be one of the following:


    The management pack class for the source of the relationship.


    The dimension which targets the source class.  This is an optional field. If no SourceDimension is specified, we will automatically find the dimension which directly targets the source class itself, or the closest parent class of the source class in the class hierarchy.

    Each relationship fact will contain one or more <Relationships> sub-tags.  The following table describes the attributes within the <Relationships> tag..

    <Relationships> Tag Attributes




    The relationship between the source and target.  A list of relationships can be found in the inbound.relationships table in the data warehouse.


    The dimension which targets the target class.

    In a multiple relationship fact, the source dimension will always remain the same, while the target dimension can change, depending on the specific relationship.  Every relationship type attribute in a multiple relationship fact must be unique. The following is an example of the relationship fact WorkItemAssignedToAndCreatedByUser:

    <RelationshipFact ID="WorkItemAssignedToAndCreatedUserFact" Accessibility="Public" Domain="Domain.InstanceManagement" TimeGrain="Daily" SourceType="WorkItem!System.WorkItem" SourceDimension="WorkItemDim">
    Relationships RelationshipType="WorkItem!System.WorkItemAssignedToUser" TargetDimension="UserDim" />
    Relationships RelationshipType="WorkItem!System.WorkItemCreatedByUser" TargetDimension="UserDim" />

    In the example above, we see that although the target dimension is identical for both relationships, the relationships themselves are unique so the relationship fact is valid.

    For more examples of outriggers, dimensions, and relationship facts, you are encouraged to look at any of the data warehouse MP’s that are shipped with Service Manager.  A great place to start is the base data warehouse management pack : Microsoft.SystemCenter.Datawarehouse.Base

  • System Center Platform Team

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


    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.


    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.




    Honda Automobiles


    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:

            <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" />

    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$" />  

    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. 

    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" />


    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:

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

    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.



    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:

    <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 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" />

    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" />

    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




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


    This element should always be set to “Public”


    The MP class name which the dimension is targeting


    For v1, this value is always to true.


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


    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.


    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.  



    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




    A unique identifier for the attribute element. 


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


    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


    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

    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.

  • System Center Platform Team



    The System Center Service Manager platform team has decided to spin up our own blog discussing platform focused topics separate of the Service Manager team blog. The target audience of this blog is the developer audience and hence we put it on MSDN. We debated about whether or not we need another blog or to use the existing team blog, but decided that the target audience is not the same (even though there is definitely overlap) and, in addition, the platform we deliver is also consumed by our sister System Center products, Operations Manager and Essentials.

    The features of the infrastructure we plan to focus on include, but are not limited to, the CMDB, general data access, data warehousing, extensibility, performance and scale, reporting and security and we’ll have posts from various people (developers, program managers and testers) from all these areas so you will get not only a “straight from the horse’s mouth” perspective, but also direct access to the people working on the features you read about.

    Our goal is to enable customers and partners to deliver solutions on top of our platform that cannot be developed just by using the console (Analyst or Authoring). We also hope to give those interested a deeper look at our platform and direct access to our platform team to answer any questions you might have and to field requests for platform changes or blog posts.

Page 1 of 1 (3 items)