SCSM Data Warehouse: Fact Groom Retention - Prevent Archived Relationships Getting Deleted

In a world where SCSM is all but perfect, one man .... 
*Dramatic Music Sharpens* 
.... must do what he can ..... 
*Music Stops Abruptly* 
.... to ensure that SCSM meets ITIL v3 best practices, by
*Dramatic Pause filled with single guitar note echoing*
.... NOT having 
*Dramatic Pause filled with single guitar note echoing*
..... SCSM ARCHIVED DATA GO BYE-BYE!
*Savage Guitar Chord, Drums smashing, drop the base, heavy metal music begins*

IT'S TIME... To prevent the unpreventable!

1. Using SSMS, Database Engine Connect to your Data Warehouse SQL Server hosting your DWRepository and DWDataMart Databases.

2. Run the following code to increase the Default Retention Period to ~4000 years by running it with USE [DWRepository] uncommented, then comment it out and uncomment USE [DWDataMart] and run it again.

   --Remember that you need to run this query on both Databases
   USE [DWRepository];
   --USE [DWDataMart];
    
   UPDATE DW_Config
   SET [ConfiguredValue] = 2102400000
   FROM [etl].[Configuration] DW_Config
   WHERE [ConfigurationPath] = 'RetentionPeriodInMinutes.Default'
      OR [ConfigurationPath] = 'EntityRelatesToEntityFact'
   

3. Congrats, you've made SCSM not want to delete tickets. However relationships of the tickets are still an issue as they as stored in [etl].[WarehouseEntityGroomingInfo]

4. Run the following code to increase the Default Retention Period to ~4000 years by running it with USE [DWRepository] uncommented, then comment it out and uncomment USE [DWDataMart] and run it again.

   --Remember that you need to run this query on both Databases
   USE [DWRepository];
   --USE [DWDataMart];
    
     UPDATE DW_Groom
     SET [RetentionPeriodInMinutes] = 2102400000
     FROM [etl].[WarehouseEntityGroomingInfo] DW_Groom
    
     JOIN [etl].[WarehouseEntity] DW_Entity (nolock)
     ON DW_Groom.[WarehouseEntityId] = DW_Entity.[WarehouseEntityId]
      
      
     WHERE DW_Entity.WarehouseEntityName LIKE 'WorkItemHasParentWorkItemFact'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemHasBillableTimeFact'
       OR DW_Entity.WarehouseEntityName LIKE 'BillableTimeHasWorkingUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemRelatesToWorkItemFact'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemGroupContainsWorkItemFact'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemAffectedUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemAssignedToUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemAboutConfigItemFact'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemRelatesToConfigItemFact'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemCreatedByUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemContainsActivityFact'
       OR DW_Entity.WarehouseEntityName LIKE 'DependentActivityDependsOnWorkItem'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemRelatesToRequestOfferingFact'
       OR DW_Entity.WarehouseEntityName LIKE 'ServiceOfferingRelatesToRequestOfferingFact'
       OR DW_Entity.WarehouseEntityName LIKE 'ServiceRelatesToServiceOfferingFact'
       OR DW_Entity.WarehouseEntityName LIKE 'IncidentResolvedByUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'IncidentHasPrimaryOwnerFact'
       OR DW_Entity.WarehouseEntityName LIKE 'ProblemResolvedByUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'ReviewActivityHasReviewerFact'
       OR DW_Entity.WarehouseEntityName LIKE 'ReviewerVotedByUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'ReviewerIsUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'ComputerHasPrimaryUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'WorkItemImpactsServiceFact'
       OR DW_Entity.WarehouseEntityName LIKE 'ServiceImpactsUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'ConfigItemServicedByUserFact'
       OR DW_Entity.WarehouseEntityName LIKE 'ConfigItemOwnedByUserFact'
   

5. Congrats, you've told the SCSM Data Warehouse to not mark your relationships as deleted after 90 days and mess up accurate reporting for anything above 90 days!

Important Note:  SCSM Archival implementation is flawed, even after doing this, once grooming is done on the LIVE Database, the DWRepository will not find the relationship it was keeping track of and then decide to mark it as being deleted which is something that break the ITIL standard of Storage for Auditing and Business Requirements.  The DWDataMart is notified of DWRepository changes and makes the change as well.  Unfortunately this is built into the stored procedures and trying to fix their work isn't feasible for a one-man army.  However the above will prevent the relationships being deleted, as once something is marked as deleted, then it is compared against the grooming info retention to see if old enough to delete.

*Explosion, Music Fades*
*Lone man looks towards the sunset, his face, changed but triumphant*
*Fade to black as the below outlined black with white color text appears on screen over his face*

As for fixing what you can by setting the latest relationship entry of a ticket as not deleted.... 
To be continued....
Part 2: SCSM Data Warehouse: Fact Groom Retention - Recover Tickets Latest Relationships Marked Deleted

I'd like to thank my friends, co-workers, family, and most of all TechNet Guru for giving me the SCSM Gold Medal, I know, I know, don't say you got it before you got it but who can compete against such intellectual, well written documentation?  No one.  I got it.  I got it cause I got it.