19 June 2008

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

03 June 2008

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