SCSM Data Warehouse: Fact Groom Retention - Recover Tickets Latest Relationships Marked Deleted
Part one dealt with prevention: SCSM Data Warehouse: Fact Groom Retention - Prevent Archived Relationships Getting Deleted
This one deals with recovering the latest relationships on tickets that should NOT have been marked as deleted.
I will provide a few examples, you can go from there!
Note: FROM [TABLE] below SET has to be done on each table, not a view when updating and it is important to run these queries on both DWRepository and DWDataMart
Service Request Created By User
--UPDATE R
--SET R.DeletedDate = NULL
SELECT COUNT(*)
FROM [dbo].[WorkItemCreatedByUserFactvw] R
INNER JOIN
(
SELECT SR.Id
,WI_CreatedByUser.WorkItemDimKey
,WI_CreatedByUser.[CreatedDate]
,WI_CreatedByUser.[DeletedDate]
,scsmUser.DisplayName
FROM [dbo].[WorkItemCreatedByUserFactvw] WI_CreatedByUser (nolock)
--This relationship needs the work item dim key.
INNER JOIN [dbo].[WorkItemDimvw] WI (nolock)
ON WI.WorkItemDimKey = WI_CreatedByUser.WorkItemDimKey
--Filter down to SR
JOIN [dbo].[ServiceRequestDimvw] SR (nolock)
ON SR.EntityDimKey = WI.EntityDimKey
--Get latest date
inner join (
select WorkItemDimKey, max(CreatedDate) as MaxDate
from [dbo].[WorkItemCreatedByUserFactvw] (nolock)
group by WorkItemDimKey
) tm on WI_CreatedByUser.WorkItemDimKey = tm.WorkItemDimKey and WI_CreatedByUser.[CreatedDate] = tm.MaxDate
inner join [dbo].[UserDimvw] scsmUser (nolock)
ON scsmUser.UserDimKey = WI_CreatedByUser.WorkItemCreatedByUser_UserDimKey
--Note, CreatedDate AND ~~~~DimKey are the composite Primary Key, both must match
WHERE WI_CreatedByUser.DeletedDate IS NOT NULL
) AS Filter ON Filter.WorkItemDimKey = R.WorkItemDimKey AND Filter.CreatedDate = R.CreatedDate
Service Request Assigned To User
--UPDATE R
--SET R.DeletedDate = NULL
SELECT COUNT(*)
FROM [dbo].[WorkItemAssignedToUserFactvw] R
INNER JOIN
(
SELECT SR.Id
,WI_User.WorkItemDimKey
,WI_User.[CreatedDate]
,WI_User.[DeletedDate]
,scsmUser.DisplayName
FROM [dbo].[WorkItemAssignedToUserFactvw] WI_User (nolock)
--This relationship needs the work item dim key.
INNER JOIN [dbo].[WorkItemDimvw] WI (nolock)
ON WI.WorkItemDimKey = WI_User.WorkItemDimKey
--Filter down to Service Requests
JOIN [dbo].[ServiceRequestDimvw] SR (nolock)
ON SR.EntityDimKey = WI.EntityDimKey
--Get latest date
inner join (
select WorkItemDimKey, max(CreatedDate) as MaxDate
from [dbo].[WorkItemAssignedToUserFactvw] (nolock)
group by WorkItemDimKey
) tm on WI_User.WorkItemDimKey = tm.WorkItemDimKey and WI_User.[CreatedDate] = tm.MaxDate
inner join [dbo].[UserDimvw] scsmUser (nolock)
ON scsmUser.UserDimKey = WI_User.WorkItemAssignedToUser_UserDimKey
--Note, CreatedDate AND ~~~~DimKey are the composite Primary Key, both must match
WHERE WI_User.DeletedDate IS NOT NULL
) AS Filter ON Filter.WorkItemDimKey = R.WorkItemDimKey AND Filter.CreatedDate = R.CreatedDate
Service Request Affected User
--UPDATE R
--SET R.DeletedDate = NULL
SELECT COUNT(*)
FROM [dbo].[WorkItemAffectedUserFactvw] R
INNER JOIN
(
SELECT SR.Id
,WI_User.WorkItemDimKey
,WI_User.[CreatedDate]
,WI_User.[DeletedDate]
,scsmUser.DisplayName
FROM [dbo].[WorkItemAffectedUserFactvw] WI_User (nolock)
--This relationship needs the work item dim key.
INNER JOIN [dbo].[WorkItemDimvw] WI (nolock)
ON WI.WorkItemDimKey = WI_User.WorkItemDimKey
--Filter down to Service Requests
JOIN [dbo].[ServiceRequestDimvw] SR (nolock)
ON SR.EntityDimKey = WI.EntityDimKey
--Get latest date
inner join (
select WorkItemDimKey, max(CreatedDate) as MaxDate
from [dbo].[WorkItemAffectedUserFactvw] (nolock)
group by WorkItemDimKey
) tm on WI_User.WorkItemDimKey = tm.WorkItemDimKey and WI_User.[CreatedDate] = tm.MaxDate
inner join [dbo].[UserDimvw] scsmUser (nolock)
ON scsmUser.UserDimKey = WI_User.WorkItemAffectedUser_UserDimKey
--Note, CreatedDate AND ~~~~DimKey are the composite Primary Key, both must match
WHERE WI_User.DeletedDate IS NOT NULL
) AS Filter ON Filter.WorkItemDimKey = R.WorkItemDimKey AND Filter.CreatedDate = R.CreatedDate
Incident Resolved By User
--UPDATE R
--SET DeletedDate = NULL
SELECT COUNT(*)
FROM [dbo].[IncidentResolvedByUserFactvw] R
INNER JOIN
(
SELECT IR.Id
,IR_ResolveUser.IncidentDimKey
,IR_ResolveUser.[CreatedDate]
,IR_ResolveUser.[DeletedDate]
,ResolvedUser.DisplayName
FROM [dbo].[IncidentResolvedByUserFactvw] IR_ResolveUser (nolock)
--Filter down to incidents that are closed/resolved
JOIN [dbo].[IncidentDimvw] IR (nolock)
ON IR.IncidentDimKey = IR_ResolveUser.IncidentDimKey
AND IR.Status_IncidentStatusId IN (3, 4)
--Get latest date
inner join (
select IncidentDimKey, max(CreatedDate) as MaxDate
from [dbo].[IncidentResolvedByUserFactvw] (nolock)
group by IncidentDimKey
) tm on IR_ResolveUser.IncidentDimKey = tm.IncidentDimKey and IR_ResolveUser.[CreatedDate] = tm.MaxDate
inner join [dbo].[UserDimvw] ResolvedUser (nolock)
ON ResolvedUser.UserDimKey = IR_ResolveUser.TroubleTicketResolvedByUser_UserDimKey
--Note, CreatedDate AND ~~~DimKey are the composite Primary Key, both must match
WHERE IR_ResolveUser.DeletedDate IS NOT NULL
) AS ResolvedFilter ON ResolvedFilter.IncidentDimKey = R.IncidentDimKey AND ResolvedFilter.CreatedDate = R.CreatedDate
Incident Primary Owner
--UPDATE R
--SET DeletedDate = NULL
SELECT COUNT(*)
FROM [dbo].[IncidentHasPrimaryOwnerFactvw] R
INNER JOIN
(
SELECT IR.Id
,IR_User.IncidentDimKey
,IR_User.[CreatedDate]
,IR_User.[DeletedDate]
,ResolvedUser.DisplayName
FROM [dbo].[IncidentHasPrimaryOwnerFactvw] IR_User (nolock)
--Filter down to incidents
JOIN [dbo].[IncidentDimvw] IR (nolock)
ON IR.IncidentDimKey = IR_User.IncidentDimKey
--Get latest date
inner join (
select IncidentDimKey, max(CreatedDate) as MaxDate
from [dbo].[IncidentHasPrimaryOwnerFactvw] (nolock)
group by IncidentDimKey
) tm on IR_User.IncidentDimKey = tm.IncidentDimKey and IR_User.[CreatedDate] = tm.MaxDate
inner join [dbo].[UserDimvw] ResolvedUser (nolock)
ON ResolvedUser.UserDimKey = IR_User.IncidentPrimaryOwner_UserDimKey
--Note, CreatedDate AND ~~~DimKey are the composite Primary Key, both must match
WHERE IR_User.DeletedDate IS NOT NULL
) AS Filter ON Filter.IncidentDimKey = R.IncidentDimKey AND Filter.CreatedDate = R.CreatedDate
Now you are free, not of the clutches of SCSM, no no no this cannot offer such sweet sweet relief.... Instead you are free to continue on with SCSM with one less problem to deal with, infinity minus one, rejoice! Yes there is a chance someone purposefully deleted the last relationship on a ticket to make it blank but that's a risk you'll have to take.