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.
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:
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.
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.
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.
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:
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 incidentJOIN 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 nullAND 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 naming convention of the foreign key columns in the relationship facts help to guide you as to which dimensions to join to.
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 ReviewerDecisionfrom 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.
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.DisplayNameOrder 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 ) dimRight Join ChangeArea_CTE outrigger ON dim.Area_ChangeAreaId = outrigger.ChangeAreaId Join DisplayStringDimvw Strings ON outrigger.EnumTypeId = Strings.BaseManagedEntityId WHERE Strings.LanguageCode = 'ENU' and Level>=0Order by Ordinal
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 ManagedTypeUNION ALLSelect 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 mtJOIN 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
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
Where does the data stop when there is no transform-code supplied by the developer? In other words, are the queries to populate tables made on Repository or on StagingAndConfig?
To write a custom transform, you need to pull the data from DWStagingandConfig. The transform stored procedure will reside in the Repository database, so for examples you could script out the transform stored procedures we ship out of the box. TransformIncidentStatusDuration is a good example of a custom stored proc we wrote (as oposed to automatically generated).
The data get extracted and stored in the DWStagingandConfig database regardless of whether there are any facts, dimensions or outriggers which use them. After the transforms have run, the data which has been processed or which was not used by any transforms is marked as eligible for grooming.
So I should create a few synonyms(in this case for incident_log) and make my queries on that synonym. And after the procedure ran, the data is transferred to DWDataMart?
Do I insert data on views or on tables?(if I recall correctly, StatusDuration inserts on views..)
why Assigned to user not showing for the incidents which are in DW but not in Service manager DB
Is there a data warehouse schema for Service Manager 2012 ?
For finding the user assigned to a WorkItem I would change the join to look like this:
JOIN WorkItemAssignedToUserFactvw on WorkItemDimvw.WorkItemDimKey = WorkItemAssignedToUserFactvw.WorkItemDimKey AND WorkItemAssignedToUserFactvw.DeletedDate IS NULL
It keeps a log of all users that have been added and removed, and the most recently assigned user will not have a deleted date.