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)
ELSE IF (@MaxDate < '1/1/2010 12:00 AM')
SET @MaxDate = DATEADD(day, 1, @MaxDate)
EXECUTE p_PopulateDateDimension @startDate = @MaxDate
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.