System Center 2012 Service Manager Data Warehouse Retention Settings

Q: How can I change the retention settings of my System Center 2012 Service Manager data warehouse?

A: System Center 2012 Service Manager (SCSM) features a data warehouse for long-term data retention. By default, most data is stored for three years (although some data is stored for 50 years), and the three years is set by a global value that can't be modified. Microsoft discusses retention settings in a TechNet blog post.  However, I've tweaked some of the SQL to make it easier to check the retention period for all the tables:

SELECT WarehouseEntityName    ,we.warehouseEntityID    ,RetentionPeriodInMinutesFROM etl.WarehouseEntity (nolock) weJOIN etl.WarehouseEntityType (nolock) wet on we.WarehouseEntityTypeId = wet.WarehouseEntityTypeIdJOIN etl.WarehouseEntityGroomingInfo (nolock) weg on we.WarehouseEntityId = weg.WarehouseEntityIdWHERE wet.WarehouseEntityTypeName = 'Fact'

If, for example, in a lab environment you wanted to change the value for all the entities that currently use three years, you could use the following (which just changes retention to keep data for three years and a hour):

UPDATE etl.WarehouseEntityGroomingInfoSET RetentionPeriodInMinutes = 1576860WHERE RetentionPeriodInMinutes = 1576800