Tool to Groom Reporting DataWarehouse

My previous article was very much focused on how to modify the database grooming settings for OpsMgr reporting database. I have taken this a step further by developing a small and simple tool that saves non-SQL Server administrators the time and effort to do so. Basically this tool will connect to the tables that defines the grooming settings and retrieves the information and display them to a UI. User can then modify the grooming settings by setting the MaxDataAgeDays value in the table and save it.


For those who are interested in this tool can just email me at theankeong@gmail.com

OpsMgr: Grooming Reporting Database

Whilst Database Grooming settings for Operations Manager DB is rather straight forward and can be done directly from the OpsMgr console, Grooming of Reporting Data Warehouse needs some additional configuration.

By default, the retention period for data being stored within Reporting Data Warehouse are:

Data Set	Aggregation Type	Retention Period (Days)
Alert	Raw data	400
State	Raw data	180
State	Hourly aggregations	400
State	Daily aggregations	400
Event	Raw data	100
Aem	Raw data	30
Aem	Daily aggregations	400
Perf	Raw data	10
Perf	Hourly aggregations	400
Perf	Daily aggregations	400

To modify the grooming settings for Alerts data in OpsMgr Reporting Data Warehouse:

• Login to SQL Server Management Studio and connect to the SQL Server Instance that hosts your Reporting Data Warehouse
• Expand the Databases -> OperationsManagerDW -> Tables node
• Right click dbo.DataSet and select Open Table
• On the returned result, locate the dataset that you would want to modify by referring to the value in DataSetDefaultName field in this case it will be "Alert dataset" and get the DataSetId field for that particular row which in this case will be "69baaa10-055f-4f56-9a03-4312dd2b6406" as depicted in the picture below.

• Right click on dbo.StandardDatasetAggregation and click Open
• In the returned results, locate the row which has the DataSetId value that matches the one you got earlier which in this example will be "69baaa10-055f-4f56-9a03-4312dd2b6406"

• You can change the retention period of alert data by setting the value of MaxDataAgeDays field of the corresponding row