Freigeben über

MOM2005 - Wrong date time displayed while the report selection time is over 2010/1/1

  • Artikel

SYMPTOM
=========
Wrong day time displayed while the report selection time is over 2010/1/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.

Comments

  • Anonymous
    January 01, 2003
    Hi. I'm struggling to solve this exact problem. Great post, but where are the attacments?