SYMPTOM ========= Wrong day time displayed while the report selection time is over 2010/1/1.
CAUSE ======================== We have a date Dimension view (SC_DateDimension_View) in the DW database which contains the day definition (such as 2009 12 31). This table is populated with a stored procedure named as "p_populatedateDimesion.sql". However, we defined a time limit t0 '1/1/2010 12:00 AM' in the stored procedure which means the latest day definition in the date Dimension view will be 2010 1 1. So if the incoming performance data (same to alert, event data) will be linked with a wrong day dimension while DTS job transferring data to the DW DB.
Sample code in the stored procedure: ------------------------ IF @startDate IS NULL SET @startDate = '1/1/1998 12:00 AM'
IF @endDate IS NULL SET @endDate = '1/1/2010 12:00 AM' ------------------------
DECLARE @MaxDate DATETIME SELECT @MaxDate = MAX([Date]) FROM dbo.SC_DateDimension_View IF (@MaxDate IS NULL) BEGIN EXECUTE p_PopulateDateDimension END ELSE IF (@MaxDate < '1/1/2010 12:00 AM') BEGIN SET @MaxDate = DATEADD(day, 1, @MaxDate) EXECUTE p_PopulateDateDimension @startDate = @MaxDate END GO
WORKAROUND ======================== As a workaround, we can modify the p_populatedateDimesion stored procedure to adjust the end time to a long value (say: 2020 1 1). Then allow the report be generated with correct date value. I have attached modified stored procedure. We can use the below steps to fix the issue:
1. Backup the current SystemCenterReporting database. 2. Connect to the SystemCenterReporting database, run the content in the p_populatedateDimesion.sql.txt file. 3. Run the below query: Exec p_populatedateDimesion @startdate = '1/2/2010 12:00 AM' 4. Run the below query statement to ensure the max date time is 2020 1 1: Select * from SC_DateDimension_View order by date DESC 5. Run the content in the fixdate.sql.txt file. This will re-link the performance, alert, event date to the accurate one. 6. Re-run the MOM report to see if the returned date value is correct now.
Hi. I'm struggling to solve this exact problem. Great post, but where are the attacments?