A Deep Dive on Creating Relationship Facts 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.

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:

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:

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

Attribute	Description
ID	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.
Accessibility	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.
Domain	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
TimeGrain	The granularity of the relationship fact. Value must be one of the following: Hourly Daily Weekly Monthly
SourceType	The management pack class for the source of the relationship.
SourceDimension	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

Attribute	Description
RelationshipType	The relationship between the source and target. A list of relationships can be found in the inbound.relationships table in the data warehouse.
TargetDimension	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:

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

A Deep Dive on Creating Relationship Facts in the Data Warehouse

Additional resources