How long does the Service Manager Data Warehouse retain historical data?

How long does the Service Manager Data Warehouse retain historical data?

  • Comments 6
  • Likes

The short answer is that we keep data in the warehouse for 3 years for fact tables and forever for dimension and outrigger tables. Antoni Hanus, a Premier Field Engineer with Microsoft, has put together the detailed steps on how to adjust this retention period so you can retain data longer or groom it out more aggressively.

DISCLAIMER: Microsoft does not support direct querying or manipulation of the SQL Databases. 

To learn more about the different type of tables in the data warehouse, see the blog post which describes the data warehouse schema.

To determine which are the fact tables and which are the dimension tables you can run the appropriate query against your DWDataMart database

SELECT WarehouseEntityName

      ,ViewName

      ,wet.WarehouseEntityTypeName

  FROM etl.WarehouseEntity (nolock) we

  JOIN      etl.WarehouseEntityType (nolock) wet on we.WarehouseEntityTypeId = wet.WarehouseEntityTypeId

  WHERE     wet.WarehouseEntityTypeName = 'Fact'

 

SELECT WarehouseEntityName

      ,ViewName

      ,wet.WarehouseEntityTypeName

  FROM etl.WarehouseEntity (nolock) we

  JOIN      etl.WarehouseEntityType (nolock) wet on we.WarehouseEntityTypeId = wet.WarehouseEntityTypeId

  WHERE     wet.WarehouseEntityTypeName = 'Dimension'

NOTE: Microsoft does not support directly accessing nor managing the tables (dimensions, facts nor outriggers).

Instead, please use the views as defined by the ‘ViewName’ column in the above query.

Fact Table Retention Settings

There are 2 two types of retention setting in the data warehouse:

1) Global - The global retention period (set to 3 years by default) which any subsequently created fact tables use as their default retention setting.

2) Individual Fact – The granular retention period for each individual fact table (uses the global setting of 3 years, unless individually modified).

Global:

The default global retention period for data stored in the Service Manager Data Warehouse is 3 years so all OOB (Out of the box) Fact tables use 3 years as the default retention setting.

Any subsequently created fact tables will use this setting upon creation for their individual retention setting.

The default Global setting value is 1576800, which is 3 years (1576800 = 1440 minutes per day * 365 days * 3 years)

This value can be verified by running the following SQL Query against the DWDataMart database:

select ConfiguredValue from etl.Configuration where ConfigurationFilter = 'DWMaintenance.grooming'

Individual Fact Tables:

Individual fact tables will inherit the global retention value upon creation, or can be customized to a value that is different from the default global setting. 

OOB Individual Fact tables that were created upon installation, can also be individually configured with a specific retention value as required. 

All of the Fact tables in the Database can be returned by running the following query against the DWDataMart Database:

SELECT WarehouseEntityName

      ,ViewName

      ,wet.WarehouseEntityTypeName

  FROM etl.WarehouseEntity (nolock) we

  JOIN      etl.WarehouseEntityType (nolock) wet on we.WarehouseEntityTypeId = wet.WarehouseEntityTypeId

  WHERE     wet.WarehouseEntityTypeName = 'Fact'

An example of an OOB fact table returned is  ActivityStatusDurationFact which has a warehouseentity ID of 81;

clip_image002

The corresponding retention setting for this Fact table is stored in the etl.warehouseentitygroominginfo table, so if we run the following query, the ‘RetentionPeriodInMinutes’ field will show us the individual retention configured for that particular table
Query:

select warehouseEntityID, RetentionPeriodInMinutes from etl.WarehouseEntityGroomingInfo where WarehouseEntityId = 81

Result:

clip_image004

A SQL Statement such as the following could be used to update an individual fact table to an appropriate value:

Use DWDatamart

UPDATE etl.WarehouseEntityGroomingInfo

SET RetentionPeriodInMinutes = [number of minutes to retain data]

WHERE WarehouseEntityId = [WarehouseEntityID of Fact table to update]

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hello Travis,

    we have an issue of very large and growing "DWRepositorylog" and "DWDataMartlog" files. Is there any procedure how to maintain database transaction log files in Service Manager?

  • Sorry, previous comment should be addressed to Chris Lauren (or anyone who can help).

  • @Ljubodrag -

    In theory the log files should be self-maintaining.

    If they are constantly growing and seem to be too large, it could be a symptom of another problem.  You might want to create a support case.

  • We have the same problem:

    The "DWRepositorylog" and "DWDataMartlog" files are growing and yet the are very large (more than 60 Gb) but we have just 20 incidents in our SCSM database.

    have somebody an idea for a solution for this issue ?

  • Ljubodrag and Philippe if your DB logs are growing unbounded you probably have your SQL db's configured with a recovery model set to "full recovery".  If you have a db configured that way and you aren't taking regular backups then the log will just continue to grow. There's more info here msdn.microsoft.com/.../aa173531(v=SQL.80).aspx

  • Has this become configurable in SCSM 2012? Without direct querying or manipulation of the SQL Databases. Only refernce I was able to find is: http://technet.microsoft.com/en-us/library/hh519651.aspx which states

    "Grooming functions typically involve activities on the data warehouse that remove data based on a configurable time period. Note - For this release of Service Manager, grooming functions are handled as a workflow. Settings for this job are not configurable."

    I'm assuming that means no, but I don't like assuming :)