次の方法で共有

Get the most recent Assigned To User or any other relationship with one-to-many cardinality!

  • [アーティクル]

Hello, everyone! My name is Chris Howie, Microsoft PFE and current Technology Manager for SCSM in the United States. For nearly the past two years, I have funneled my bloggable content through other PFEs (primarily Brian Barrington and Antoni Hanus), mostly because they already had established blogs and I of course did not. Now, we are all so busy that I am going to have to carry my own weight in this regard. :) In summary, I have been putting off blogging for too long and am ready to get going on my own.

Anyone that has had an onsite or remote engagement with me knows my passion around reporting in SCSM. Unfortunately, there are few out-of-the-box reports and they of course do not accommodate customizations, which most customers do, which results in me doing a TON of custom reporting. For my first post, I am going to address an issue which has plagued many customers who are attempting to retrieve the most recent instance of a relationship which is one-to-many.

First, we must level-set on some terminology and concepts. Everything in SCSM is an object of a class (i.e. an Incident, Service Request, Windows Computer, etc.). These classes are related to each other via relationships. In the case of an Incident being related to a User, there is an Incident object and a User object and they are related to each other in a specific way through a relationship identifier (i.e. Assigned To User, Created By User, Affected User, Resolved By User). Some relationships can be many-to-many, such as About Configuration Item, which is the affected configuration items on work items, or one-to-many such as Assigned To User.

Note: If you ever wanted to know the cardinality for a specific relationship, you can always look it up by running SELECT * FROM RelationshipType against the ServiceManager Database. There you will find the minimum and maximum cardinality settings for every relationship in SCSM.

With many-to-many relationships, there can be multiple objects related to multiple others. For example, you can have multiple configuration items on an Incident, and those configuration items can also be related to multiple incidents. Alternatively, with one-to-many relationships such as Assigned To User, there can only be one active Assigned To User on an Incident, but that Assigned To User can be assigned to multiple Incidents. To maintain this single cardinality on work items, the existing relationship must be destroyed/deleted to allow the new one to exist. Keep this concept in your mind for the next section. :)

So - why did I just tell you all of this? It is important to understand the two different relationship concepts in reporting because it ultimately determines how you need to consume the data in the data mart. In the DWDataMart, we keep relationship changes in the fact tables which hold them. For example, in the case of WorkitemAssignedToUserFactvw, if a work item was reassigned multiple times then there are multiple entries. This can be a good or bad thing, depending on the type of report you are wanting to write. For example, if you are wanting to include all of the people who were involved in a work item's life, then you would simply join to include all of them and move on. However, if you want the last assigned to user, or the last of any other one-to-many relationship, you have to do some determination.

When viewing the WorkItemAssignedToUserFactvw and other fact views, you will likely notice that there is a DeletedDate column. This column is populated with the date/time of when the destroy process occurs for an existing one-to-many relationship that we previously discussed. So - the active (last) relationship should have a value of NULL when it has not yet been deleted, right? Well - yes and no. When the work item still exists in SCSM, the active relationship still exists also, therefore having a DeletedDate of NULL. However, when the work item is groomed, that relationship is also deleted, and therefore has a DeletedDate equal to the date/time it was groomed. Unfortunately, the OOB reports in 2012R2 and most customers' reports expect the DeletedDate = NULL to identify the last relationship. This means that as work items are groomed, the reports will no longer show the Assigned To User because the DeletedDate is now populated.

Now what? Now that you understand the fundamental problem, you understand that we cannot simply use DeletedDate = NULL nor can we simply Max() the DeletedDate column because we would not include the NULL values on the active work items. To achieve the best of both worlds, I have come up with an interesting way to get the most recent assigned to user in both scenarios within a single query. First, I use the SQL functions Coalesce(which replaces NULL with a value) and GETDATE(which gets the current date/time) to convert the NULL DeletedDate values to the current date/time (which would make them the most recent). Then, I use the Max function to get the most recent date/time for each work item. This ensures that I have the last Assigned To User, whether it is active and NULL or it has been groomed. Below is an example of using this concept.

 

--This query retrieves the last Assigned To User on all Work Items in the DWDataMart

SELECT
WI.Id AS [WorkItem ID],
AssignedInfo.DisplayName AS [Assigned To User]
FROM WorkItemDimvw WI
LEFT JOIN (Select Max(COALESCE(DeletedDate,getdate())) as [max deleted date],WorkItemDimKey from WorkItemAssignedToUserFactvw assignedFact group by WorkItemDimKey) assignedfact on wi.WorkItemDimKey = assignedfact.WorkItemDimKey
LEFT JOIN WorkItemAssignedToUserFactvw assignedfact2 on WI.WorkItemDimKey = assignedfact2.WorkItemDimKey AND Coalesce(assignedfact2.DeletedDate,GETDATE()) = assignedfact.[max deleted date]
LEFT JOIN UserDimvw AssignedInfo on AssignedFact2.WorkItemAssignedToUser_UserDimKey = AssignedInfo.UserDimKey
where WI.Id IS NOT NULL

 

Thanks for reading, I hope this makes sense and helps those who are writing custom reports for SCSM!

Have any ideas for me or the other PFEs to blog about? If so, please put them in the comments and we will do our best to get the knowledge out there!! :)

-Chris Howie

 

 

This is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of Use.

Comments

  • Anonymous
    November 08, 2016
    Thank you. It is very help full.