Query Pulling First Assigned To and not the current Assigned to. Suggestions?

Brian_Wiest

Managment has a requirment to get an email with a list of any incident not updated in 48 hours. 
The issue I keep running into is that the query will provide me the incident information but the assigned to will display the first agent it was assigned to and not the current assigned to. 
It is causing managment to report agents not performing on work that is no longer assigned to them.

Replace with needed vailues
[Support Group 1] etc

Thanks

SELECT DISTINCT WIM.Id AS ID

,IDM.Title AS Title

,ISV.IncidentStatusValue AS [Incident Status]

,U.IncidentUrgencyValue AS [Incident Priority]

,AssignedTo.DisplayName AS [Assign To]

,AffectedUser.DisplayName AS [Affected User]

,ITQ.IncidentTierQueuesValue As [Support Group]

,WIM.CreatedDate As [Create Date]

,ED.LastModified AS [Last Modified Date]  

,datediff(day,ED.LastModified,getdate()) AS 'Aging'       

FROM [SC2012_DWDataMart].[dbo].[WorkItemDimvw] WIM

LEFT JOIN EntityDimvw ED on WIM.EntityDimKey = ED.EntityDimKey

LEFT join [SC2012_DWDataMart].[dbo].[IncidentDimvw] IDM ON IDM.Id = WIM.Id

LEFT join [SC2012_DWDataMart].[dbo].[IncidentStatusvw] ISV ON ISV.IncidentStatusId = IDM.Status_IncidentStatusId

LEFT join [SC2012_DWDataMart].[dbo].[IncidentTierQueuesvw] ITQ on IDM.Tierqueue = ITQ.[ID]

LEFT join [SC2012_DWDataMart].[dbo].[WorkItemCreatedByUserFactvw] WICB on WICB.WorkItemDimKey = WIM.WorkItemDimKey

LEFT join [SC2012_DWDataMart].[dbo].[WorkItemAssignedToUserFactvw] WIAA on WIM.WorkItemDimKey = WIAA.WorkItemDimKey

LEFT join [SC2012_DWDataMart].[dbo].[UserDimvw] AssignedTo on AssignedTo.UserDimKey = WIAA.WorkItemAssignedToUser_UserDimKey

LEFT join [SC2012_DWDataMart].[dbo].[WorkItemAffectedUserFactvw] WIAU on WIM.WorkItemDimKey = WIAU.WorkItemDimKey

LEFT join [SC2012_DWDataMart].[dbo].[UserDimvw] AffectedUser on AffectedUser.UserDimKey = WIAU.[WorkItemAffectedUser_UserDimKey]

LEFT join [SC2012_DWDataMart].[dbo].[WorkItemCommentLogDimvw] WICL ON WIM.WorkItemDimKey = WICL.WorkItemCommentLogDimKey

LEFT join [SC2012_DWDataMart].[dbo].[IncidentRelatesToActionLogFactvw] ItoALF on ItoALF.IncidentDimKey = IDM.IncidentDimKey

LEFT join [SC2012_DWDataMart].[dbo].[WorkItemActionLogDimvw] WLD on ItoALF.TroubleTicketHasActionLog_WorkItemActionLogDimKey = WLD.WorkItemActionLogDimKey

left join dbo.IncidentUrgencyvw U on U.IncidentUrgencyID = IDM.Urgency_IncidentUrgencyID

LEFT JOIN [SC2012_DWDataMart].[dbo].[IncidentRelatesToAnalystCommentLogFactvw] ItoAC on IDM.IncidentDimKey = ItoAC.IncidentDimKey

LEFT JOIN [SC2012_DWDataMart].[dbo].[WorkItemAnalystCommentLogDimvw] WIAC on WIAC.WorkItemAnalystCommentLogDimKey = ItoAC.TroubleTicketHasAnalystComment_WorkItemAnalystCommentLogDimKey

 WHERE  ITQ.IncidentTierQueuesValue IN ('[Support Group 1]', '[Support Group 2]', '[Support Group 3]')

       AND  (ISV.IncidentStatusValue = 'Active' OR  ISV.IncidentStatusValue = 'Pending')

ORDER BY ED.LastModified ASC

Billy_Wilson

Check your JOIN for the WorkItemAssignedToUserFactvw. There should be a field that is (I think) DeletedDate in that table. Every time a work item is reassigned, a DeletedDate is added to that assignment field for the previous Assigned To user. So if you include in your JOIN "AND (WIAU.DeletedDate Is Null)" that will only bring back the currently Assigned To user. I will check my DW when I get a chance to verify.

Hope this helps. 

Billy_Wilson

Try - LEFT join [SC2012_DWDataMart].[dbo].[WorkItemAssignedToUserFactvw] WIAA on WIM.WorkItemDimKey = WIAA.WorkItemDimKey AND WIAA.DeletedDate IS NULL

Conner_Wood

Billy is correct regarding how the DataWarehouse stores relationships and uses DeletedDate to mark them as deleted.

I've learned the Retention Settings in the DWRepository can cause issues with marking relationships as deleted in the DWDataMart which I elaborated on in one of my previous comments.

You could potentially look for the MAX(CreatedDate) as that would be the latest relationship which is what you want, regardless if it's marked as deleted or not.

USE [SC2012_DWDataMart];--Get a workitemdimkey with multiple assignments
SELECT [WorkItemDimKey], COUNT(*) AS 'CountAssignments'
  FROM [dbo].[WorkItemAssignedToUserFactvw] (nolock)
  GROUP BY WorkItemDimKey
  ORDER BY [CountAssignments] DESC;

--Show the assignments regarding the workitemdimkey
  SELECT WIAA.*, AssignedToUser.DisplayName
  FROM [dbo].[WorkItemAssignedToUserFactvw] WIAA (nolock)
  JOIN dbo.UserDimvw (nolock) AS AssignedToUser
  ON AssignedToUser.UserDimKey = WIAA.WorkItemAssignedToUser_UserDimKey
  WHERE WorkItemDimKey = 36872
  ORDER BY [CreatedDate];

--Display Ticket Info of Type Incident with Latest Assigned To User
--We will speed up the query by generating a table of the latest created date for each workitem assigned to relationship
WITH WIAA_Latest AS (
	SELECT WIAA.[WorkItemDimKey], MAX(WIAA.[CreatedDate]) as 'MaxCreatedDate'
	from [dbo].[WorkItemAssignedToUserFactvw] WIAA (nolock)
	group by WIAA.WorkItemDimKey
)
SELECT DISTINCT WIM.WorkItemDimKey, WIM.[Id], WIM.[Title], AssignedTo.AssignedToUser, AssignedTo.CreatedDate
FROM [dbo].[WorkItemDimvw] WIM (nolock)

INNER JOIN WIAA_Latest ON
WIAA_Latest.WorkItemDimKey = WIM.WorkItemDimKey

LEFT JOIN
(
	SELECT AssignedToFact.WorkItemDimKey, AssignedToFact.CreatedDate, AssignedToFact.DeletedDate, AssignedToUser.DisplayName AS 'AssignedToUser', AssignedToUser.UserDimKey 
	FROM [dbo].[WorkItemAssignedToUserFactvw] AssignedToFact (nolock)
	JOIN dbo.UserDimvw (nolock) AS AssignedToUser
	ON AssignedToUser.UserDimKey = AssignedToFact.WorkItemAssignedToUser_UserDimKey
) AS AssignedTo ON AssignedTo.WorkItemDimKey = WIM.WorkItemDimKey AND AssignedTo.CreatedDate = WIAA_Latest.MaxCreatedDate
  -- AND AssignedTo.DeletedDate IS NULL --!!! UNCOMMENT IF YOU DO NOT WANT A DATE THAT'S BEEN DELETED AND WOULD RATHER RETURN NULL!!!

WHERE WIM.WorkItemDimKey = 36872

Brian_Wiest

Thanks everyone for the comments. 
Billy your addition work just as managment needed.
Connor thanks for the post, correct me if I am wrong but since I am targeting open incidents, I don't have to "stress" that retention is providing me incorrect results. 

Conner_Wood

For this particular instance you will be OK, but know that Management tends to want more over time, and when they want Live Reporting from the SCSM Data Warehouse you will have to break the news to them that by design, by Microsoft Out of the Box design no less, the Data Warehouse does not sync on a per ticket basis, but on job schedules and it varies how long something takes, sometimes tickets won't even sync until later in the day.  Are you looking forward to that ?

Your current situation demands ~48 hours of inactivity which is a reasonable amount for a ticket to be synced to the Data Warehouse so tickets will be in the Data Warehouse.  However it is not Live Reporting and should not be treated as such.

If something happens overnight, and they want a Live Report, you will very likely find tickets that were just updated have not been synced to the Data Warehouse yet so they end up on the report because according to the DW it hasn't been updated.  Also if a ticket was recently created it may not be in the Data Warehouse yet.

"Well you made it work with the last report, so make this one work already!"

So I'll ask you again, are you looking forward to that ?

If the answer is No, I recommend focusing your SQL queries on the "ServiceManager" database as that is where you can achieve Live Reporting.  Yes, the schema is different and is overly complicated, but that is how it was designed and that is what you have.

Brian_Wiest

Thanks for the reply.
Management has been made fully aware, and what is being provided is a morning report of incident matching the "over 48 hour" update. Currenlty the plan is just for spot checking older incidents, and letting the support agents known that "someone is watching" 
There are some reports/dashboards we pull from the cireson database for live current pulls. We are saving those for the critical pulls so our server load is "balanced"

Conner_Wood

Good, just know you'll probably end up learning the ServiceManager schema anyways as scope creep is inevitable.

As long as management and the support agents are educated regarding the limitations of DW Reports, they will have more reasonable expectations.

Chris_Chekaluk

I ran into the same issue with DW reporting and relationships several years ago & our workaround was to look at the IsDeleted flag on the WI itself and if it was groomed use a join with Max(CreatedDate) and if it was not groomed, use DeletedDate is null instead.

My recollection is that Max(CreatedDate) can break down if/when a relationship is 're-activated'.  For example- today a WI is assigned to me, next week it is assigned to you, and the following week it is re-assigned back to me.  My recollection is that the relationship between my User record and the WI is re-used without updating the CreatedDate (instead of creating a 2nd relationship with a new CreatedDate).  The DeletedDate field will be updated (set back to NULL) instead.

While the below sample SQL (for IR's) doesn't really address re-activated relationships in groomed records, I have found it to be reliable with re-activated relationships in un-groomed ones... and less worse for groomed ones.

/* add 'Assigned To' user */
 left join WorkItemAssignedToUserFactvw WIATU on WIATU.WorkItemDimKey=WI.WorkItemDimKey
        and ( (I.IsDeleted=0 and WIATU.DeletedDate is NULL ) or
   (  I.IsDeleted=1 and WIATU.CreatedDate=(select MAX(wau3.CreatedDate)
         from IncidentDimvw idv3
          inner join WorkItemDimvw wi3 on wi3.EntityDimKey=idv3.EntityDimKey
          inner join WorkItemAssignedToUserFactvw wau3 on wau3.WorkItemDimKey=wi3.WorkItemDimKey
         where idv3.IncidentDimKey=I.IncidentDimKey
         ) ) )
 left join UserDimvw AssignedToUser on WIATU.WorkItemAssignedToUser_UserDimKey=AssignedToUser.UserDimKey

Conner_Wood

Chris_Chekaluk said:

My recollection is that Max(CreatedDate) can break down if/when a relationship is 're-activated'.  For example- today a WI is assigned to me, next week it is assigned to you, and the following week it is re-assigned back to me.  My recollection is that the relationship between my User record and the WI is re-used without updating the CreatedDate (instead of creating a 2nd relationship with a new CreatedDate).  The DeletedDate field will be updated (set back to NULL) instead.

Hmmm, my results dictate I must disagree about the 're-activation' aspect, my results show that when something is "re-assigned back" it really is creating a new row, this is how you can determine how many times an entity has been assigned something on an incident.

Using my 1st query to find tickets with high assignment counts and my 2nd query I provided to view each assignment row, I must disagree that a relationship is re-used.  (As you can see, the 2nd row and the 6th row disagree with your very example about assigning back on a different week)



Back when I was creating an assignment history chart, I purposefully had tested re-assigning a ticket back and forth multiple times in the same day to see if the Data Warehouse would be able to keep track of it and not just sync the end result at the end of the day and luckily it showed that it will indeed keep track of each one, so I can confirm it is reliable for both relationships and enumerations that have an Outrigger which is then referenced to a Fact of the Ticket and Enum which does the Duration like the relationships because of the Fact Template being specified as "StateTransition".

Sidenote:  I did find a bug about the StateTransition though which sadly I don't think MS will ever fix, basically if I use "Extends" on the Dimension to reference the table it will try to look for the table that would've been created if I hadn't added "Extends" since there is no logic to see if the one dimension it references has the "Extends" attribute specified and to target that Table instead.

Chris_Chekaluk

I have re-tested over the weekend and agree that the 're-activation' of relationships is no longer applicable in SCSM- that's awesome!