The first part of this series showed you how to write simple queries to get data from you DWDatamart database (or any other database). And why you need to join 3 views to be able to get the displayname of the lists (enumerations) in SCSM!
This part will show you the more advanced queries to get the different relationships from a incident in the Datawarehouse.
End goal
Remember our end goal is delivering a need report to present to the managers crying for reports!
The list of the end goal report:
- What work items are open?
- Date/time when the work items where logged?
- Duration of the work items being in one state?
- Which people are responsible?
- When was the last time the work item was modified?
- Who did the last modification?
With part 1 “ Basic Queries” we are able to deliver 2 out of the 6 fields to present in our report.
- What work items are open?
- Date/time when the work items where logged
And we are able to deliver much more “standard” information from an incident to get already a real reporting experience.
This part will focus on how you can add the responsible people of the incident and a little “extra” how to get the related CI’s.
Understanding the data model
Before we can start writing the Query you need to familiarize yourself with the data model used in SCSM.
Here a version of the 2010 data model For the Service Manager Database and DWDatamart (which is mostly the same for 2012) Have fun!
Service Manager Database
DW Datamart (Incident)
This makes sense doesn’t it?
Now let’s start writing the query.
Hmm maybe some more details would be nice
First of all the data model is based on 2010 so all new classes / relationships created since 2012 are not in the Visio diagrams!!!
You can download the Visio Diagrams from :
You need to have the DWDatamart Visio which has the model for each workitem / config item.
But before we dive into the Visio’s let’s first take a step back and look at the data model.
Data Model
Now SCSM is build on the same framework as SCOM so any information / knowledge on the SCOM Data Model will greatly benefit!
All classes in SCSM are derived from the base class named “Object” which is the highest base class.
Mainly there are 2 main abstract classes beneath this class in SCSM for “Work Items” and “Configuration Items”.
Let’s take a look at how it all ties together when looking at an incident:
 |
 |
|
In SCSM all work Items like Incident, Change request, Service Request are created from the base class Work Item. |
What does this mean?
This means an Incident = Work Item = Object
Change Request = Work Item = Object
Service Request = Work Item = Object
Now when you look at properties and relationships on a class it is crucial you understand how a class gets it’s properties.
When you look at an incident class it has the properties and relationships defined specific for the Incident class AND all Work Item class properties and relationships AND all Object Class properties and relationships .
This means every object in SCSM which has a base class of work item class (like Change Request, Service Request etc.) has beside it’s own properties also all properties from object class and Work Item Class.
Below is an overview of all class properties defined for the specific class.
When you open the Incident class in the authoring tool and look at the properties and relationships you will notice all properties in above sheet are shown on the Incident Class properties (Except the internal ones from the object class).
The information of where these relationships are defined (on which class) is crucial to be able to pull the information!!!
Let’s take a look the properties of the Incident class to get the relationship we want:
When you select the relationship in the authoring console you need to take a look at the details of the relationship and specifically the SOURCE this will tell you the source class of the relationship, Work Item.
So this relationship we want is not defined on the Incident class but on the work item class!
You don’t have to learn this stuff by hard but you should be aware of the fact Relationships are defined at the specific class and how the high level of the data model is designed
Incident = Work Item = Object
If you understand this concept you can find anything in the DWDatamart and you can really understand the impact of a class extension or DW extension.
If you want to know more on the data model Chapter 16 and 18 of System Center Service Manager 2010 Unleashed explains in detail the data model of SCSM.
For SCOM reference System Center Operations Manager 2012 Unleashed which is basically the same data model as for SCSM.
Getting the relationships
When you look at the DWDatamart Visio this is the diagram of how the relationships are defined in the DWDatamart database views. When you look at the incident class you will notice ONLY the relationships for the Incident class :
 |
 |
| These are all Relationships defined on the Incident class. I have marked the ones which are in the DWDatamart. | Here are the views you will need to be able to pull the information from the DWDatamart Database |
Let’s take a better look at the Incident class in the DWDatamart Visio:
So basically if we want the Primary Owner we need to start joining views again:
We need to join the IncidentDimvw with IncidentHasPrimaryOwnerFactvw by IncidentDimkey = IncidentDimkey
and we need to join IncidentHasPrimaryOwnerFactvw with UserDimvw by IncidentPrimaryOwner_UserDimkey = UserDimkey
Now you should be able to pull the Primary owner with a query:
The Joins:
Left Outer JOIN
dbo.IncidentHasPrimaryOwnerFactvw
ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = I.IncidentDimKey
AND (dbo.IncidentHasPrimaryOwnerFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.UserDimvw PrimaryOwner
ON PrimaryOwner.UserDimKey = IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey
This will get the primary owner of an incident.
Notice the “AND deletedate IS Null” the reason for adding this line is we only want the current owner! When you change the owner of an incident this table is updated with the new owner and the old owner will be “flagged” as deleted.
Now combined with the query we earlier created:
Select I.Id, I.Title, I.CreatedDate, StatusDS.DisplayName as Status, PrimaryOwner.DisplayName as PrimaryOwner
From IncidentDimvw I
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
AND StatusDS.LanguageCode = ‘ENU’
LEFT OUTER JOIN
dbo.IncidentHasPrimaryOwnerFactvw
ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = I.IncidentDimKey
AND (dbo.IncidentHasPrimaryOwnerFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.UserDimvw PrimaryOwner
ON PrimaryOwner.UserDimKey = IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey
Order by I.CreatedDate desc
Will result in a query delivering the Incident Owner as well (if there is one):
This actually looks easy….so why did I explain the whole data model part….well now you can only get two relationships:
- Primary Owner
- Resolved By User
These are nice but you want the related CI’s as well or any other relationship of an incident.
Getting the others
To get the other relationships you need to be aware of where those relationships are targeted. In the example shown Incident = Work Item = Object we have seen these relationships life at the Work Item.
We need to travel one level up in our query to be able to get to these relationships.
To “travel” one level up we are going to join the view IncidentDimvw with WorkItemDimvw:
INNER JOIN dbo.WorkItemDimvw WI
ON I.EntityDimKey = WI.EntityDimKey
With this join we are now able to get to those other relationships because we can now use the WorkItemDimvw to get to these by using WI.
Now you can use the same approach as with the incidentDimvw and IncidentFact views with WorkItemDimvw.
To get the assigned to user:
LEFT OUTER JOIN
dbo.WorkItemAssignedToUserFactvw
ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey
AND (dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.UserDimvw AS AssignedTo
ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey
The first join is on the WorkItemAssignedTouserFactvw, these kind of fact views hold the relationships.
So you first need to get the relationship and after this you are able to join this with the UserDimvw to get to the Display Name of the user!
Now let’s join the other joins with the existing joins in our query:
Select I.Id, I.Title, I.CreatedDate, StatusDS.DisplayName as Status, PrimaryOwner.DisplayName as PrimaryOwner, AssignedTo.DisplayName as AssignedUser
From IncidentDimvw I
INNER JOIN dbo.WorkItemDimvw WI
ON I.EntityDimKey = WI.EntityDimKey
LEFT OUTER JOIN
dbo.WorkItemAssignedToUserFactvw
ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey
AND (dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.UserDimvw AS AssignedTo
ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
AND StatusDS.LanguageCode = ‘ENU’
LEFT OUTER JOIN
dbo.IncidentHasPrimaryOwnerFactvw
ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = I.IncidentDimKey
AND (dbo.IncidentHasPrimaryOwnerFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.UserDimvw PrimaryOwner
ON PrimaryOwner.UserDimKey = IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey
Order by I.CreatedDate desc
The results:
You are now also able to get the affected CI’s:
LEFT OUTER JOIN
dbo.WorkItemAboutConfigItemFactvw
ON dbo.WorkItemAboutConfigItemFactvw.WorkItemDimKey = WI.WorkItemDimKey
AND (dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.ConfigItemDimvw AboutCon
ON AboutCon.ConfigItemDimKey = dbo.WorkItemAboutConfigItemFactvw.WorkItemAboutConfigItem_ConfigItemDimKey
Now let’s write the Query containing this Joining extravaganza!
Select I.Id, I.Title, I.CreatedDate, StatusDS.DisplayName as Status, PrimaryOwner.DisplayName as PrimaryOwner, AssignedTo.DisplayName as AssignedUser, aboutcon.DisplayName as AboutConfigItem
From IncidentDimvw I
INNER JOIN dbo.WorkItemDimvw WI
ON I.EntityDimKey = WI.EntityDimKey
LEFT OUTER JOIN
dbo.WorkItemAssignedToUserFactvw
ON dbo.WorkItemAssignedToUserFactvw.WorkItemDimKey = WI.WorkItemDimKey
AND (dbo.WorkItemAssignedToUserFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.UserDimvw AS AssignedTo
ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey = AssignedTo.UserDimKey
LEFT OUTER JOIN
dbo.WorkItemAboutConfigItemFactvw
ON dbo.WorkItemAboutConfigItemFactvw.WorkItemDimKey = WI.WorkItemDimKey
AND (dbo.WorkItemAboutConfigItemFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.ConfigItemDimvw AboutCon
ON AboutCon.ConfigItemDimKey = dbo.WorkItemAboutConfigItemFactvw.WorkItemAboutConfigItem_ConfigItemDimKey
LEFT OUTER JOIN
dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN
dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
AND StatusDS.LanguageCode = ‘ENU’
LEFT OUTER JOIN
dbo.IncidentHasPrimaryOwnerFactvw
ON IncidentHasPrimaryOwnerFactvw.IncidentDimKey = I.IncidentDimKey
AND (dbo.IncidentHasPrimaryOwnerFactvw.DeletedDate IS NULL)
LEFT OUTER JOIN
dbo.UserDimvw PrimaryOwner
ON PrimaryOwner.UserDimKey = IncidentHasPrimaryOwnerFactvw.IncidentPrimaryOwner_UserDimKey
Order by I.CreatedDate desc
The result:
You are now able to get the other relationships as well!
The initial question was:
- What work items are open?
- Date/time when the work items where logged?
- Duration of the work items being in one state?
- Which people are responsible?
- When was the last time the work item was modified?
- Who did the last modification?
Now we are able to deliver:
- What work items are open?
- Date/time when the work items where logged?
- Which people are responsible?
The next part will describe how to setup the stored procedure and explain what is in there. After the stored procedure I will explain the duration.
Recap
Part 2 of this series showed how the data model is defined and how you can get the relationships into your query.
Now we are able to pull any relationship and property from the DWDatamart!
The next part will explain the stored procedures used in SCSM reporting.
Stay tuned for part 3 – “ Stored Procedures”
Regards,
Oskar Landman
