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.