mom: some details about mom 2005 summary reporting

get this… since the day summary reporting was rolled out, the dts package has never successfully completed. i’m talking 6 months. so, for 6 months, mom has been force fed data from the systemcenterreporting database. for those of you that may not know, summary reporting is an add-on that aggregates data points in the reporting warehouse database. by using this system, you can effectively reduce the size of your warehouse. this is by no means a comprehensive guide. just some things discovered along the way. more information is in the accompanying guide that comes with the download. to get started, here are the stored procedures that can be executed to change the behavior of summary reporting pack:

• exec p_setweekstartday [value]
  • [value] is 1-7 (sunday through saturday, respectively)
• exec p_setcutoffdate [yyyy-mm-dd]
• exec p_setsamplegroomdays [value]
  • [value] must be greater than 7
• exec p_setalertgroomdays [value]
  • [value] must be greater than 7
• exec p_setlowpercentilevalue [value]
  • [value] 1 through 49
• exec p_sethighpercentilevalue [value]
  • [value] 51 through 99
• exec p_setgroombatchsize [value]
  • [value] must be greater than 10.

most of these are pretty self-explanatory and are explained in much better detail in the guide. i put them up here for my own reference whenever i need to execute one of these things without having to look up pages 12-15. :) there is one in particular that i want to talk about, which is p_setcutoffdate. this is something that you want to pay particular attention to when you setup. if you set this value too far in the past, the dts job may never complete, depending on the amount of data you have. the reason being, this value dictates where aggregation starts. in other words, where do you want the aggregation job to start looking at data points? do you want to start from 3 months in the past? expect it to fail. at any rate, don’t worry about starting it too early. just make sure that the date you start, matches the p_setweekstartday value. let me explain! the previous stuff, you know, the stuff prior to the p_setcutoffdate value, can be brought in manually. you can read in a week, a month, etc. here are the commands (watch for word wrap):

• daily samples
  dtsrun.exe /F "[MOM 2005 Reporting installation directory]\Reporting\BuildDailySampleAggregations_AnyDays.dts" /W True /A "ServerName":"8"="YourServer" /A "DatabaseName":"8"="SystemCenterReporting" /A "StartDate":"8"="YYYY-MM-DD" /A "EndDate":"8"="YYYY-MM-DD"
  
• weekly samples
  dtsrun.exe /F "[MOM 2005 Reporting installation directory]\Reporting\BuildWeeklySampleAggregations_AnyDays.dts" /W True /A "ServerName":"8"="YourServer" /A "DatabaseName":"8"="SystemCenterReporting" /A "StartDate":"8"="YYYY-MM-DD" /A "EndDate":"8"="YYYY-MM-DD"
  
• alerts
  dtsrun.exe /F "[MOM 2005 Reporting installation directory]\Reporting\BuildAlertAggregations_AnyDays.dts" /W True /A "ServerName":"8"="YourServer" /A "DatabaseName":"8"="SystemCenterReporting" /A "StartDate":"8"="YYYY-MM-DD" /A "EndDate":"8"="YYYY-MM-DD"

now that we have that out of the way, why else would the dts job fail? here are the reasons:

1. dts job is extremely cpu intensive and space intensive (tempdb).
2. uploadaggregations table may not have a recent timestamp.

if you look in the dts job itself, the instructions are executed in parallel, instead of serial. this may not be a big deal since ordinarily, summary reporting shouldn’t take very long to run. so a small spike in cpu, in the dead of night… who cares? now if you factor this with tons of data to have to look through, then you’ve got some issues. we modified the dts job to execute in serial initially, thinking this was what was causing all the problems. not quite… refer back to #2.

this uploadaggregations table. hmmm. it’s not in accompanying guide. odd... turns out this table holds the timestamps to indicate from which date forward summary reporting pack should look at data points. remember, the p_setcutoffdate parameter only tells the dts job from which date to start. this only matters for the maiden voyage. to see what i’m talking about, issue this command in query analyzer. it won’t break anything… no, really… it won’t.

select LastDate, LastWeek, AlertLastDate, AlertLastWeek 
from dbo.uploadaggregations 

select top 1 dategenerated_pk as LastDate 
from sc_daily_counterdatafact_table 
order by LastDate desc 

select top 1 weekgenerated_pk as LastWeek 
from sc_weekly_counterdatafact_table 
order by LastWeek desc 

select top 1 dategenerated_pk as AlertLastDate 
from sc_daily_alertfact_table 
order by AlertLastDate desc 

select top 1 weekgenerated_pk as AlertLastWeek 
from sc_weekly_alertfact_table 
order by AlertLastWeek desc 

the query will list the values in the table that need your focus. the fields listed in the first table is a marker to indicate where to begin looking at data points for the dts job’s current run. if you have a value way back in the past, it’s going to go back and look.

 

to correct this, just pair up the names and dates. if the timestamps are older in the first table, modify this table to raise the timestamps to the values in their pair. for example, if lastdate in the first table is older than lastdate in the following table, then change the lastdate in the first table to match the lastdate in the second table. by the way, if you have multiple rows in the first table, that means you're sending sending more than one management group to your warehouse. :)

 

hope this helps.