MOM 2005: Understanding MOM Reporting DTS and Interdepencies

Setting up a MOM 2005 Reporting Server requires an understanding of the mechanism of how data transitions from the Management Server to the Reporting Server and some actions that occur behind the scenes. If you have noticed that your database doesn’t maintain a fairly static size (less for changes in the environment such as adding more agents or management packs, turning on additional rules, etc) this article should be of some assistance in finding the end points. It’s really pretty simple to see how the pieces fit together once they have been identified._[Understanding the DTS Scheduled Task]_

The Reporting Server has one real component of which to be aware. This is the MOM.Datawarehousing.DTSPackageGenerator.exe (located in %ProgramFiles%\Microsoft System Center Reporting\Reporting). This is the magic. This is what traverses the ether from the Reporting Server, connects to the Management Server and extracts the data to post to the reporting database. This utility is composed of the following switches:

• silent
• latency
• srcserver
• dwserver
• dwdb
• product

Setting up the Reporting Server will populate most of these switches. Unless the environment changes, the only useful switches are silent and latency. The silent switch will suppress any dialog during the run time of the executable. In actuality, this has no real value even when unsuppressed since most of the useful events are posted to the Event Log, and no status messages are displayed regarding progress until the very end. The latency switch on the other hand is very useful when catching up copious amounts of data. The number specified after /latency: indicates to move data older than the number specified (in days). From what I can tell, the DTS job does not work in batches. It attempts to retrieve the data at once thus has adverse effects if your log size is not adequate or if the amount of data is large enough to cause timeout issues.

By default the setting is 5 minutes, and also by default, when the command switch is built, the latency switch is not used. Here is an example of a composed command line:

MOM.Datawarehousing.DTSPackageGenerator.exe /silent /srcserver:OnePointDBServer /srcdb:OnePoint /dwserver:ReportingServer /dwdb:SystemCenterReporting /product:”Microsoft Operations Manager”

After a successful completion of DTSPackageGenerator, Event ID 80 will post to the event log with a full description of the run, looking similar to the following:

Event Source: DataTransformationServices
Event Category: None
Event ID: 80
…
Description:
The execution of the following DTS Package succeeded:

Package Name: SC_Inner_DTS_Package
Package Description: This package transfers data from OnePointDBServer.OnePoint to ReportingServer.SystemCenterReporting
…
Executed On: ReportingServer
Executed By: DAS Account Name
…
Total Execution Time: 1531.735 seconds

Package Steps execution information:

Step ‘SyncStepBetweDimensionAndFact’ succeeded
Step Execution Started: 1/1/2005 1:14:25 AM
Step Execution Completed: 1/1/2005 1:14:25 AM
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0
…
[truncated for brevity]

If the DTSPackageGenerator fails to complete, Event ID 1001 is posted instead. These events may be followed by Event ID 81.

_[Understanding the MOM Grooming Process]_

On success of the DTSPackageGenerator, the TimeDTSLastRun field (of the ReportingSettings table of the Management Server database) is updated with the timestamp of completion. When the MOMX Partitioning and Grooming job (on the Management Server database) runs, it will look to this value to determine what items can be groomed. For example, assume the following values are set in your environment:

• Management Server Groom Setting: 4 days (default)
• TimeDTSLastRan: 5/20/2005
• Date Management Server runs MOMX: 5/22/2005
• Current Date: 5/22/2005(MS Groom Setting can be located here: Administrator Console\Administration\Global Settings\Database Grooming.)

In this case, the MOMX job runs at 5/22/2005. With Groom Setting set to 4 days, this indicates to the Management Server to always retain 4 days of data. The TimeDTSLastRan value is used as a marker to pick up items older than this date. The calculation works like this:

• @partitionCutTime is determined by taking Groom Setting value and subtracting that from the current date.
• @partitionCutDTSTime is determined from taking the TimeDTSLastRan value from the ReportingSettings table and subtracting 1 day.
• If @partitionCutDTSTime is less than @partitionCutTime, then the @partitionCutDTSTime becomes the @partitionCutTime value.

The value of @partitionCutTime becomes the marker for the items that it can now groom out. Anything earlier than this value is valid for grooming. Taking the example above, @partitionCutTime is 5/18/2005. The @partitionCutDTSTime value is 5/20/2005. Since the @partitionCutDTSTime is not lower than the @partitionCutTime, the value of @partitionCutTime is retained as 5/18/2005 and used as the marker for grooming data. If the TimeDTSLastRan was 5/14/2005, then the @partitionCutTime would become 5/14/2005 which would mean anything newer than 5/14/2005 could not be groomed out.

Issuing the following command will retrieve the TimeDTSLastRan value: select TimeDTSLastRan from ReportingSettings.

_[Grooming the Reporting Server]_

The Reporting Server database has its own grooming cycle which is not displayed in the Administrator Console. In order to modify this date (395 days or 13 months, by default) requires changing some values in the Reporting Server database. Issuing the following command will list all the tables and their relative grooming cycle:

SELECT	cs.CS_TableName, wcs.WCS_GroomDays

FROM	WarehouseClassSchema wcs

JOIN	ClassSchemas cs

ON	cs.CS_ClassID = wcs.WCS_ClassID

WHERE 	cs.CS_TableName = 'SC_AlertFact_Table' OR

 cs.CS_TableName = 'SC_AlertHistoryFact_Table' OR

 cs.CS_TableName = 'SC_AlertToEventFact_Table' OR

 cs.CS_TableName = 'SC_EventFact_Table' OR

 cs.CS_TableName = 'SC_EventParameterFact_Table' OR

 cs.CS_TableName = 'SC_SampledNumericDataFact_Table' AND

 wcs.WCS_MustBeGroomed = 1

It should look similar to this output:

SC_EventParameterFact_Table 395
SC_AlertFact_Table 395
SC_SampledNumericDataFact_Table 395
SC_AlertToEventFact_Table 395
SC_EventFact_Table 395
SC_AlertHistoryFact_Table 395

If 13 months is longer than your business requires, you can modify these tables using the following command:

Exec p_updategroomdays ‘Table_Name_Here’, DaysToRetain

Change the Table_Name_Here value to one of the table names supplied above that requires a change. Also, modify the DaysToRetain to a numeric value for the number of days to retain. In order to change all of the relevant tables quickly, to the same value, this SQL command can be issued (credit to Clive Eastwood):

-- Update the Datawarehouse Groom settings

 Declare @Groomdays int

-- Retain data for 180 days

 Select @Groomdays = 180

exec p_updateGroomDays 'SC_SampledNumericDataFact_Table', @Groomdays

 exec p_updateGroomDays 'SC_AlertFact_Table', @Groomdays

 exec p_updateGroomDays 'SC_EventParameterFact_Table', @Groomdays

 exec p_updateGroomDays 'SC_AlertToEventFact_Table', @Groomdays

 exec p_updateGroomDays 'SC_EventFact_Table', @Groomdays

 exec p_updateGroomDays 'SC_AlertHistoryFact_Table', @Groomdays

Modify the @Groomdays = 180 to a value that makes sense for your business requirements.

_[Sizing the Reporting Server Database]_

There are some calculations that you should consider while you’re sizing your database adequately. Understanding the amount of data that posts daily is the foundation of the calculation. Here are the calculations for proper sizing:

• Database = (OneDaysGrowth * NumberOfDaysToRetain) * 2
• Log = OneDaysGrowth * 5
• TempDB = OneDaysGrowth

OneDaysGrowth can be easily obtained by examining the database size (sp_spaceused) before and after a day’s worth of data posts to the Reporting Server.

_[Miscellaneous]_

• There are other tables that groom out at cycles of 3 days. Issuing the following command will show these. However, for the purpose of grooming, this information is not needed.

  SELECT	cs.CS_TableName, wcs.WCS_GroomDays
  
  FROM	WarehouseClassSchema wcs
  
  JOIN	ClassSchemas cs
  
  ON	cs.CS_ClassID = wcs.WCS_ClassID
  
  Where 	wcs.WCS_MustBeGroomed = 1

• ReportingSettings does not update for any additional Management Groups such as outlined in the solution accelerator.

Comments, suggestions, compliments, corrections, hecklings, rants, curses, or aspersions can be cast, directed, or misguided towards marcus.oh@gmail.com where I shall quickly respond with failure: Unexpected Error.
By: Marcus Oh

Reklam