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;
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:
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]