Here are the schema diagrams you’ve been waiting for! If you’re not familiar with developing management packs, writing your first few custom queries against the data warehouse can be intimidating. The database schema is based on the common management pack model , which means the relational database objects and relationships benefit from class inheritance, so you should familiarize yourself with the model.
In this blog post I’m going to explain the different types of tables in the data warehouse, which I’ve color coded in the attached schema diagrams. In my next post I’m going to provide a bit deeper knowledge on how to find your way around the views you need and some best practices for using them to write your custom reports.
Click at the end of the blog post to download the Service Manager 2010 Data Warehouse schema or keep reading to learn more about how to use it.
A brief overview of the types of tables in the data warehouse
No matter how many tables are in the warehouse, there are only three types of tables. It’s important to understand what each type of table is used for:
1. Dimensions
Dimensions represent the classes, where each row in the dimension is an instance of the class and each column is a property. Enum properties, however, are stored in “outriggers”, which are like dimensions except they have one row per item in a list which describes a class instance. See Outriggers below.
2. Fact tables
Fact tables are the most notable difference between a data warehouse and a transaction processing system. Generally fact tables are used to track transactions, or things that happen, over time. These transactions are usually quantified and summarized, so they get represented as metrics (called measures in data warehousing terms).
In Service Manager 2010, there are two types of fact tables:
a. Relationship fact tables
Relationship fact tables are used to track the relationships between instances of classes over time.
For example, in the Service Manager model there is a relationship called WorkItemAssignedToUser which enables assigning a user to a WorkItem. As the WorkItem is assigned or reassigned to a user, a new row is inserted into the relationship WorkItemAssignedToUser fact table which targets this relationship.
Relationship fact tables also have CreatedDate and DeletedDate columns which enable determining when the relationship was in effect. If the DeletedDate column is null it is currently an active relationship.
All the code required to populate and maintain these fact tables are automatically generated once the fact table is defined in a management pack. More on creating relationship fact tables in this blog post .
b. Custom fact tables
Custom fact tables are fact tables which a developer can write a custom code for and populate based on their specific business requirements.
Out of the box we have a few custom fact tables which can be quite useful. One of them is the IncidentStatusDurationFact. This fact table tracks every time an Incident’s Status changes. The measure in this fact table is the TotalTimeMeasure, which is the duration in minutes which the incident remained in that status.
This enables measuring both the total process time as the Incident proceeds through it’s lifecycle as well as the number of transitions (i.e how many times did the incident move from Active to Pending and how long was it Pending before being reactivated).
For custom fact tables, the Service Manager Data Warehouse infrastructure will automatically generate the code required to extract the data from Service Manager into the warehouse, and to load the data mart from the Repository database, but the transform code must be provided by the developer creating the custom fact table.
3. Outriggers
An outrigger describes an instance of a class. The “Lists” or enumeration properties in Service Manager are used to populate outriggers which describe their respective classes. For example there’s an IncidentClassification outrigger which describes Incidents, a ChangeCategory outrigger which describes Change Requests, a ProblemResolution outrigger which describes Problems and more.
Interpreting and using the Data Warehouse schema diagram
To make the Visio diagram easier to read, I’ve grouped the tables into separate tabs based on subject matter. However, it’s really important to understand that many types of queries will need to span the subject matters. For example, if you want to identify the Configuration Items with the most incidents, you’ll need to join tables from the Incident tab, Work Item tab and Config Item tab. This may not be readily apparent at first, so let’s dig a bit deeper on how this all works.
1. Dimensions
As I mentioned above, each dimensions represent the classes. However, the dimensions help to abstract the complexity of the class hierarchy. For example, there are several different types of “Computer” classes but they are all represented by the Computer dimension. Each dimension has a row for the class it targets and all the classes which extend or derive from that class.
For example, Incidents and Problems are classes which are types of TroubleTickets which are in turn types of WorkItems which in turn is a type of Entity. Each class in this hierarchy could have a dimension. Each dimension in this hierarchy contains a row not only for each instance of it’s class but also each of it’s descendant classes. In the example below:
- EntityDimvw contains a row for every instance of all classes
- WorkItemDimvw contains a row for each Incident and Problem (and other work items not reflected below)
- TroubleTicket doesn’t have it’s own dimension
- Incident and Problem both have their own dimensions (IncidentDimvw and ProblemDimvw, respectively)
What’s really cool about this is that the EntityDimKey (the surrogate key of the EntityDim) as well as the BaseManagedEntityID is present in each dimension. This enables you to walk up the hierarchy to traverse fact tables which don’t directly join to the dimensions you need.
A classic example of needing to traverse the hierarchy is the WorkItemAssignedtoUserFact. If you want to find out who an Incident is assigned to you can join IncidentDimvw => WorkItemDimvw => WorkItemAssignedtoUserFactvw>UserDimvw:
Select Top 10 incident.Id
, incident.Title
, userdim.UserName as AssignedToUser
From IncidentDim incident
JOIN WorkItemDim workitem on incident.EntityDimKey = workItem.EntityDimKey
JOIN WorkItemAssignedToUserFactvw assignedtouser on workitem.WorkItemDimKey = assignedtouser.WorkItemDimKey
JOIN UserDimvw userdim on assignedtouser.WorkItemAssignedToUser_UserDimKey = userdim.UserDimKey
Where assignedtouser.DeletedDate is null
At first this may seem a bit odd and an unnecessary step. You may be wondering why not simply have an “IncidentAssignedToUserFact” and not worry about this class hierarchy stuff. The truth is, being a model-based data warehouse sometimes makes tables and columns a little harder to figure out at first, but once you understand the usage pattern you can actually see way more uses for the “generic” nature of some of these fact tables. For example, by tracking the history of Work Item assignments in one fact table, you can quickly get a holistic view of every Work Item assigned to a particular user:
Select workitem.Id
, workItem.Title
, mt.TypeName
From WorkItemDim workitem
JOIN WorkItemAssignedToUserFactvw assignedtouser on workitem.WorkItemDimKey = assignedtouser.WorkItemDimKey
JOIN UserDimvw userdim on assignedtouser.WorkItemAssignedToUser_UserDimKey = userdim.UserDimKey
JOIN EntityManagedTypeFactvw entityfact on workItem.EntityDimKey = entityfact.EntityDimKey
JOIN ManagedTypeDim mt on entityfact.ManagedTypeDimKey = mt.ManagedTypeDimKey
WHere assignedtouser.DeletedDate is null
AND userdim.UserName = 'Consetetur Takimata'
You can see this user has Activities, Incidents, and a Change Request Assigned to them.
2. Fact tables
The Relationship fact tables are pretty extensive, but sometimes because they target relationships which are somewhat “generic” it may be hard to visualize how to use them. For example:
- The WorkItemAboutConfigItemFact can be used to get the number of work items (ie incidents, change requests or problems) which affect a config item (computer, service, etc)
- The WorkItemRelatesToConfigItemFact can be used to get the number of work items (ie incidents, change requests or problems) which relate to a config item (computer, service, etc)
The naming convention of the foreign key columns in the relationship facts help to guide you as to which dimensions to join to.
- The foreign key which points to the source endpoint of the relationship is named identically to the primary key of the corresponding dimension. For example, in the WorkItemAboutConfigItemFact, the WorkItemDimKey points to the WorkItemDimvw dimension.
- The foreign key which points to the target endpoint of the relationship is prefixed with the <relationship name>_ then the name of the primary key of the corresponding dimension. In the WorkItemAboutConfigItemFact, the WorkItemAboutConfigItem_ConfigItemDimKey points to the ConfigItemDimvw dimension.
You can “daisy chain” a series of relationship fact tables together to get more indirect relationships for more complex analyses. It’s a bad practice to join fact tables directly to each other, but when they share a common dimension it’s perfectly acceptable to “drill across” from one fact table to another. For example, to determine how many change requests were approved by a user
Select changerequest.ID
, changeRequest.Title
, userdim.UserName
, strings.DisplayName as ReviewerDecision
from ChangeRequestDimvw changerequest
JOIN WorkItemDimvw workitem on changerequest.EntityDimKey = workitem.EntityDimKey
JOIN WorkItemContainsActivityFactvw workitemactivity on workitem.WorkItemDimKey = workitemactivity.WorkItemContainsActivity_ActivityDimKey
JOIN ActivityDimvw activity on workitemactivity.WorkItemContainsActivity_ActivityDimKey = activity.ActivityDimKey
JOIN ReviewActivityDim reviewactivity on activity.EntityDimKey = reviewactivity.EntityDimKey
JOIN ReviewActivityHasReviewerFactvw reviewactivityreviewer on activity.ActivityDimKey = reviewactivityreviewer.ActivityDimKey
JOIN ReviewerDimvw reviewer on reviewactivityreviewer.ReviewActivityHasReviewer_ReviewerDimKey = reviewer.ReviewerDimKey
JOIN ReviewerIsUserFactvw reviewuser on reviewer.ReviewerDimKey = reviewuser.ReviewerDimKey
JOIN UserDimvw userdim on reviewuser.ReviewerIsUser_UserDimKey = userdim.UserDimKey
JOIN ReviewerDecisionvw decision on reviewer.Decision_ReviewerDecisionId = decision.ReviewerDecisionId
JOIN DisplayStringDimvw strings on decision.EnumTypeId = strings.BaseManagedEntityId
WHERE userdim.UserName ='Consequat Vulputate' AND strings.LanguageCode ='ENU'
Notice the last join to DisplayStringDimvw…this enables bringing the localized string for the ReviewerDecision Outrigger into the resultset. There are additional tricks and things to consider when localizing a report and we’ll cover that in more detail in a separate blog post.
3. Outriggers
As I mentioned above, an outrigger describes an instance of a class. Getting a count or list of Incidents, for example, is rarely as useful as filtering or grouping by the Status, Classification or Priority of the Incident. These are a discrete set of known values usually populated in “Lists” or enumerations via the Service Manager console. When an outrigger is populated from an enumeration, it can also be localized and represent a hierarchy.
If you look at the data mart schema, it’s tempting to try to obtain an enum property value from the dimension itself. Yes there’s a column in the IncidentDim called Status, but it’s unfortunately not there for you to use in your reports. We need it for ETL (data processing) purposes. Instead, we provided an outrigger table which understands the hierarchical structure of the Lists in SM. For example, within our Change Management solution, each Change Request can be assigned a Change Area. Each Change Area can roll up into a hierarchy of Change Areas.
You could get the flat list with a query like this one (notice the join to DisplayStringDimvw to get the localized display strings):
SELECT outrigger.ordinal
, Strings.DisplayName AS ChangeArea
, COUNT(*) AS ChangeRequests
FROM ChangeRequestDimvw dim
Join ChangeArea outrigger ON dim.Area_ChangeAreaId = outrigger.ChangeAreaId
Join DisplayStringDimvw Strings ON outrigger.EnumTypeId = Strings.BaseManagedEntityId
WHERE Strings.LanguageCode = 'ENU'
GROUP BY Outrigger.Ordinal, strings.DisplayName
Order by Ordinal
If you’ve taken the time to build out some hierarchies within the list, you could use the additional details in the corresponding outrigger tables to visually represent the hierarchy in your report. One approach is to use a Common Table Expression (CTE) to recursively construct the hierarchy, then order by the Ordinal property to visually represent the hierarchy. We can make this even more visually appealing & useful when we get into custom report authoring, so I’ll leave that for another blog post.
WITH ChangeArea_CTE
( ChangeAreaID
, EnumTypeID
, ParentIDPath
, Level
, Ordinal) as
(Select ChangeAreaID
, EnumTypeID
, isnull(convert(varchar, ParentID),'0') as ParentIDPath
, -1 as Level
, Ordinal
FROM ChangeArea
WHERE ParentId is null
UNION ALL
Select changearea.ChangeAreaID
, changearea.EnumTypeID
, Convert(varchar, changearea_CTE.ParentIDPath + '.' + Convert(varchar, changearea.ParentID)) as ParentIDPath
, ChangeArea_CTE.Level + 1
, ChangeArea.Ordinal
FROM ChangeArea
JOIN ChangeArea_CTE on changeArea.ParentId = ChangeArea_CTE.CHangeAreaID
WHERE ChangeArea.ParentId is not null
)
SELECT Replicate(' ', Level) + Strings.DisplayName AS ChangeArea
, isnull(dim.ChangeRequests, 0) as ChangeRequests
FROM ( Select Area_ChangeAreaId
, Count(*) as ChangeRequests
From ChangeRequestDimvw
Group BY Area_ChangeAreaId
) dim
Right Join ChangeArea_CTE outrigger ON dim.Area_ChangeAreaId = outrigger.ChangeAreaId
Join DisplayStringDimvw Strings ON outrigger.EnumTypeId = Strings.BaseManagedEntityId
WHERE Strings.LanguageCode = 'ENU' and Level>=0
Order by Ordinal
Mapping classes to dimensions in the warehouse
There are many classes & relationships whose data is extracted into the Data Warehouse for which there are no dimensions or fact tables out of the box. It’s easy to create a management pack to create your own dimensions and fact tables without writing any code. However, if you’re not sure whether the warehouse already has a dimension you might need, here’s an unsupported query which will walk up the class hierarchy from a specific class (see the filter on line 36) and tell you which dimensions exist which contain rows for that class. Depending on how high up in the hierarchy and how they were modeled, they may or may not contain the columns you need. However, as I mentioned above, walking up the hierarchy is an important part of drilling across fact tables, so this may help guide you in your exploration of the data mart. This query, unlike all the others provided in this post, must be run in the StagingandConfig database:
WITH ClassHierarchy (
ManagedTypeID
, ManagedTypeName
, DerivedManagedTypeID
, BaseManagedTypeId
, DerivedManagedTypeName
, Level
, DiscoveryPath)
as (
SELECT ManagedTypeID as DerivedManagedTypeID
, TypeName as DerivedManagedTypeName
, ManagedTypeID as DerivedManagedTypeID
, BaseManagedTypeId
, TypeName
, 0 as Level
, convert(varchar(max), '>' + TypeName) as DiscoveryPath
From ManagedType
UNION ALL
Select ch.ManagedTypeID
, ch.ManagedTypeName
, mt.ManagedTypeID
, mt.BaseManagedTypeId
, mt.TypeName
, ch.Level + 1 as Level
, convert(varchar(max), ch.DiscoveryPath + '>' + mt.TypeName ) as DiscoveryPath
From ManagedType mt
JOIN ClassHierarchy ch on ch.DerivedManagedTypeId = mt.BaseManagedTypeID
)
Select
ManagedTypeName
, isnull(DiscoveryPath, '>') as RelationshipPath
, DerivedManagedTypeName
, dim.DimensionName
From ClassHierarchy ch
LEFT JOIN Dimension dim on ch.ManagedTypeID = dim.TargetId
Where DerivedManagedTypeName = 'System.WorkItem.Incident'
Order By Level desc
At last, the diagram itself
I hope this post has gotten you warmed up and excited to write some custom queries. In the diagram I’ve color coded each table as a dimension (blue), fact (green) or outrigger (yellow). Here’s an example, but check out the attached Visio for more details.
An example for the Incident tab is below
