Retention Dates in Portal

Jacob_Esho

In admin portal, it would be nice to see/set all the retention dates along with integrations of your auto close in one area. 
# Get retentionperiod for DataMart, OMDataMart, CMDWDataMart Get-SCDWRetentionPeriod –ComputerName SCSMManagementServer –DatamartComputerName SCSMSQL\DW -DatamartDatabaseName DWDataMart Get-SCDWRetentionPeriod –ComputerName SCSMManagementServer –DatamartComputerName SCSMSQL\DW -DatamartDatabaseName OMDWDataMart Get-SCDWRetentionPeriod –ComputerName SCSMManagementServer –DatamartComputerName SCSMSQL\DW -DatamartDatabaseName CMDWDataMart # So to change the retention of the DWDatamart to 5 years  ( 5 years * 365 days * 24 hours * 60 minutes = 2628000 minutes) Set-SCDWRetentionPeriod –ComputerName SCSMManagementServer –DatamartComputerName SCSM-SQL\DW -DatamartDatabaseName DWDataMart -DurationInMinutes 2628000

Erik_Luth

Some additional info here https://blogs.technet.microsoft.com/servicemanager/2011/06/07/how-long-does-the-service-manager-data-warehouse-retain-historical-data/
;-)

Conner_Wood

It's important to note that the "DWRepository" does affect the retention of the DWDataMart Relationships being marked as deleted.

If DWRepository is not same or greater than the SCSM Ticket Retention Settings you can have duplicate relationship/enumeration duration data to occur.  The reason this happens is because it does the following:

1. For a Ticket, Retention Time is up in DWRepository, remove ticket and relationships
2. DWDataMart takes a look, oh, can't find some relationships, mark them as deleted (hence why the DeletedDate from UTC to LocalTime is about Midnight.
3. DWRepository syncs ticket and relationships on a new monthly table
4. DWDataMart sees new data and adds it accordingly.

I personally believe it's a lack of functionality and code logic that causes relationships in the DWDataMart to be marked as deleted in this way, and makes it difficult to maintain accurate reporting, since archived data is being altered by something else later on besides what was done on the actual ticket. One must question if that is allowed under ITIL standards.  Y'know, a year later and archived tickets have relationships marked as deleted.

The current workaround I've found is to alter both RetentionTimes (DWRepository and DWDataMart) to the same amount (and ensuring it's equal to or greater than any Ticket Retention Time in SCSM Settings), obviously there is more data in the DWRepository, but accuracy over efficiency in this case.

Ex.
See how many incidents that are resolved/closed have their very last relationship of Resolved By User marked as deleted.  (I noticed each day the number got bigger and bigger, but by doing workaround it's remained at 0)

USE DWDataMart;

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

Conner_Wood

I have learned much of SCSM, the above post is a good start but it's important to also:

• SCSM Data Warehouse: Fact Groom Retention - Prevent Relationships Getting Marked As Deleted
• SCSM Data Warehouse: Fact Groom Retention - Recover Tickets Latest Relationships Marked Deleted