How long do we keep data for in the Data Warehouse and how can I change the values? That’s probably a common question, so if you don’t know you can use a couple of methods.
Option 1: Using SQL
Firstly, we can perform a couple of simple SQL Queries against the OperationsManagerDW Database.
Take note of the GUID’s returned in the DatasetId column and align this with the DatasetDefaultName. So in the above image you can see that the “Alert Data Set” has the GUID starting with “FOC75C2E”.
Now query the dbo.StandardDatasetAggregation Table.
Locate the DatasetID using the GUID you identified in the previous step.
You’ll see the AggregationTypeId Column will contain one of the following values which will tell you what type of data we’re collecting:
0 = Raw (Non-Aggregated Data)
10 = Sub-Hourly
20 = Hourly
30 = Daily
From here you can set the “MaxDataAgeDays” value to whatever you want it to be.
So using our example the GUID starting with “FOC75C2E” we can identify it as being the Alert Data Set with a retention period of 400 days for RAW Data.
Option 2: dwdatarp
Another option (and probably the easier one if you’re unfamiliar with using SQL) is to use the “dwdatarp.exe” tool… (Microsoft Operations Manager Data Warehouse Retention Policy Utility – dwdatarp)
Its pretty simple to use, just run it from a command prompt or a PowerShell window without any parameters and it’ll tell you what to do.
So to find out what your data retention policies are, run it with the -s and -d parameters.
dwdatarp -s servername -d OperationsManagerDW
And you’ll get back the results like this…
So here we can see the same data that our SQL Query returned and right at the top is the Alert Data Set with a retention period of 400 days for RAW Data.
If you want to change anything, then you can use the -ds -a and -m parameters.
So for example if you want to change the Performance Raw Data retention to 5 days you’d do that like this:
dwdatarp -s servername -d OperationsManagerDW -ds Performance -a Raw -m 5
Obviously you’ll need to have appropriate permissions to make the change, but the change will be immediate.