SCSM SSRS SQL

CaterhamITSupport

Dear All,

Can anyone help, i need to get fields for affected user and also assigned to user on my reports. I can do one or the other using the following but doesnt work with both (using same code):

WorkItemAffectedUserFactvw AS AffectedUser ON workitem.WorkItemDimKey = affecteduser.WorkItemDimKey LEFT OUTER JOIN

             UserDimvw AS userdim ON AffectedUser.WorkItemAffectedUser_UserDimKey = userdim.UserDimKey

Any help with this is appreciated.

Daniel

Adam_Dzyacky

You've semi answered your own question here actually - you need to alias the same table (UserDim) differently for each reference. Call one AffectedUser and the AssignedTo. Then go about your joins. For example -

select ir.id, ir.title, ir.description, ir.source,
	affectedUser.DisplayName as 'Affected User',
	assignedTo.DisplayName as 'Assigned To'
from incidentdim as ir
	inner join entitydim as e on ir.EntityDimKey = e.EntityDimKey
	inner join workitemdim as wi on e.EntityDimKey = wi.EntityDimKey
	left join workitemaffecteduserfactvw as wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey
		inner join UserDim as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey
	left join WorkItemAssignedToUserFactvw as wiat on wi.WorkItemDimKey = wiat.WorkItemDimKey
		inner join UserDim as assignedTo on wiat.WorkItemAssignedToUser_UserDimKey = assignedTo.UserDimKey

where ir.CreatedDate > '01-01-2020'
	and wiau.DeletedDate is null
	and wiat.DeletedDate is null
order by ir.id

This grabs both of those relationships on an Incident and then only shows the current assigned/affected user by ensuring the date the relationship was deleted is null.

Adam_Dzyacky

So as a troubleshooting step here - take my query and see if you get the results (at their core) that you're expecting. If you do, slowly begin to introduce your changes into it taking note of the number of rows returns each subsequent run. This way you can ensure you are consistently returning the correct data as well as if your joins are correct e.g. inner instead of left.

Adam_Dzyacky

Ok it clears out 2 of the four entries?

I'm not sure I understand what you're asking/saying there.

By introducing the WHERE clause with the relationship whose deletedate is null you should be able to ensure you only pull single records back as opposed to one for each change in assignment/affected user.

CaterhamITSupport

Dear Adam,

I changed your code as below and getting better results now. I moved the two where statements into the joins which seems to have fixed it.

Daniel

LEFT OUTER JOIN workitemaffecteduserfactvw as wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey

AND wiau.DeletedDate is null

LEFT OUTER JOIN UserDim as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey

LEFT OUTER JOIN WorkItemAssignedToUserFactvw as wiat on wi.WorkItemDimKey = wiat.WorkItemDimKey

AND wiat.DeletedDate IS NULL

LEFT OUTER JOIN UserDim as AssignedToUser on wiat.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey

CaterhamITSupport

Found here:

https://community.cireson.com/discussion/475/query-pulling-first-assigned-to-and-not-the-current-assigned-to-suggestions

Adam_Dzyacky

You've semi answered your own question here actually - you need to alias the same table (UserDim) differently for each reference. Call one AffectedUser and the AssignedTo. Then go about your joins. For example -

select ir.id, ir.title, ir.description, ir.source,
	affectedUser.DisplayName as 'Affected User',
	assignedTo.DisplayName as 'Assigned To'
from incidentdim as ir
	inner join entitydim as e on ir.EntityDimKey = e.EntityDimKey
	inner join workitemdim as wi on e.EntityDimKey = wi.EntityDimKey
	left join workitemaffecteduserfactvw as wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey
		inner join UserDim as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey
	left join WorkItemAssignedToUserFactvw as wiat on wi.WorkItemDimKey = wiat.WorkItemDimKey
		inner join UserDim as assignedTo on wiat.WorkItemAssignedToUser_UserDimKey = assignedTo.UserDimKey

where ir.CreatedDate > '01-01-2020'
	and wiau.DeletedDate is null
	and wiat.DeletedDate is null
order by ir.id

This grabs both of those relationships on an Incident and then only shows the current assigned/affected user by ensuring the date the relationship was deleted is null.

CaterhamITSupport

Dear Adam,

Thank you for the help.

Kind Regards

Daniel

Adam_Dzyacky

So as a troubleshooting step here - take my query and see if you get the results (at their core) that you're expecting. If you do, slowly begin to introduce your changes into it taking note of the number of rows returns each subsequent run. This way you can ensure you are consistently returning the correct data as well as if your joins are correct e.g. inner instead of left.

CaterhamITSupport

That seems to have worked! Thank you for the code and also your guidance here.

Kind Regards

Daniel

CaterhamITSupport

Hi Adam,

If an incident has been accident to many different technicians it shows different lines in the report for each technician. Can it only show the current assigned?

Thanks for the help!

Daniel

Adam_Dzyacky

Yes - per my original query notice the WHERE clause that's being used. the "DeletedDate" property of those relationship tables keep track of when the relationship was broken (e.g. unassigned). Therefore the relationship that has a NULL date would be the current/final user.

CaterhamITSupport

Ok it clears out 2 of the four entries? Really appreciate the help with this.

Adam_Dzyacky

Ok it clears out 2 of the four entries?

I'm not sure I understand what you're asking/saying there.

By introducing the WHERE clause with the relationship whose deletedate is null you should be able to ensure you only pull single records back as opposed to one for each change in assignment/affected user.

CaterhamITSupport

You are right, i think it was the data warehouse taking time to catch up.

Thank you for all the help.

Kind Regards

Daniel

CaterhamITSupport

Hi Adam,

I hope you are ok, im still having problems with this actually.

When I put in:

and wiau.DeletedDate is null
	and wiat.DeletedDate is null

It doesn't show up all the records where as when i put it in it shows duplicates with other assigned people to the ticket and not the actual assigned person as it is at the moment?

Are you able to offer any help?

Daniel

CaterhamITSupport

This is my SQL code:

FROM      IncidentDim 

LEFT OUTER JOIN entitydim as e on IncidentDim.EntityDimKey = e.EntityDimKey

LEFT OUTER JOIN workitemdim as wi on e.EntityDimKey = wi.EntityDimKey

LEFT OUTER JOIN workitemaffecteduserfactvw as wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey

LEFT OUTER JOIN UserDim as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey

LEFT OUTER JOIN WorkItemAssignedToUserFactvw as wiat on wi.WorkItemDimKey = wiat.WorkItemDimKey

LEFT OUTER JOIN UserDim as AssignedToUser on wiat.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey

WHERE    

AND (wiau.DeletedDate is null)

AND (wiat.DeletedDate is null)

ORDER BY IncidentDim.CreatedDate DESC

CaterhamITSupport

Dear Adam,

I went through a process of rebuilding the datawarehouse before would this affect data that is shown on the reports? I am trying to identify if new tickets are ok and old are not now.

When I rebuilt the datawarehouse there was still test tickets in the scsm databases.

Daniel

CaterhamITSupport

Dear Adam,

I changed your code as below and getting better results now. I moved the two where statements into the joins which seems to have fixed it.

Daniel

LEFT OUTER JOIN workitemaffecteduserfactvw as wiau on wi.WorkItemDimKey = wiau.WorkItemDimKey

AND wiau.DeletedDate is null

LEFT OUTER JOIN UserDim as affectedUser on wiau.WorkItemAffectedUser_UserDimKey = affectedUser.UserDimKey

LEFT OUTER JOIN WorkItemAssignedToUserFactvw as wiat on wi.WorkItemDimKey = wiat.WorkItemDimKey

AND wiat.DeletedDate IS NULL

LEFT OUTER JOIN UserDim as AssignedToUser on wiat.WorkItemAssignedToUser_UserDimKey = AssignedToUser.UserDimKey

CaterhamITSupport

Found here:

https://community.cireson.com/discussion/475/query-pulling-first-assigned-to-and-not-the-current-assigned-to-suggestions

Adam_Dzyacky

If you're still seeing test tickets from early on, it sounds like your core SCSM Grooming settings are a bit large. You can adjust these settings from the SCSM Console

Administration -> Settings -> Data Retention Settings

There you'll find the retention for each Work Item class along with the History retention. In general, you'll want to keep all of these numbers as low as you possibly can. The faster you groom, the more lean your SCSM deployment will be. However this comes at the potential cost that people looking for "old" Work Items may not find them because they get groomed out so quickly. So this is a bit of a balancing act.

But glad to hear you got this sorted.

CaterhamITSupport

Hi Adam,

Thanks for this but this is definitely sorted now with the change in SQL coding moving them from where part to joins. Thank you for all the help with this, its really appreciated.

Kind Regards

Daniel