Thanks to Marc Biarnes for his excellent work on this topic. I’ve given a previous work around for this type of issue by updating the TimeByDay table, but this wasn’t supportable, and also any updates may have reverted to the bad behavior. Marc’s solution has the blessing of the Product Group and is far more elegant than mine – but hey, what would you expect from a Frenchman, if not elegance! This is all based on Project Server 2007 but should be equally applicable to Project Server 2010.
According to the ISO 8601 standards the first week of the year is not correctly calculated in the Analysis Views used by Project Web Access.
By default, in the OLAP Cube, the week1 starts on January 1st.
But, according to ISO 8601, it should start on January 4th (first completed week).
The calendar used to build the OLAP Cube is stored in the Reporting.dbo.MSP_TimeByDay table.
The Time dimension uses the following fields:
· [TimeByDay]
· [TimeDayOfTheWeek]
· [TimeMonthOfTheYear]
· [TimeYear]
· [TimeDayOfTheMonth]
· [TimeWeekOfTheYear]
· [TimeQuarter]
The Week numbering is based on the [TimeWeekOfTheYear] field which starts on January 1st according to the US standard:
|
TimeByDay |
TimeYear |
TimeWeek |
TimeQuarter |
|
2009-12-29 |
2009 |
53 |
4 |
|
2009-12-30 |
2009 |
53 |
4 |
|
2009-12-31 |
2009 |
53 |
4 |
|
2010-01-01 |
2010 |
1 |
1 |
|
2010-01-02 |
2010 |
1 |
1 |
|
2010-01-03 |
2010 |
2 |
1 |
|
2010-01-04 |
2010 |
2 |
1 |
But there are more than standard to calculated the Week 1 and several European customers (Scandinavia, Germany, France) don’t use the US standard but the ISO 8601 standard:
If week starts on Sunday :
|
TimeByDay |
TimeYear |
TimeWeek |
TimeQuarter |
|
2009-12-29 |
2009 |
53 |
4 |
|
2009-12-30 |
2009 |
53 |
4 |
|
2009-12-31 |
2009 |
53 |
4 |
|
2010-01-01 |
2010 |
53 |
4 |
|
2010-01-02 |
2010 |
53 |
4 |
|
2010-01-03 |
2010 |
1 |
1 |
|
2010-01-04 |
2010 |
1 |
1 |
|
TimeByDay |
TimeYear |
TimeWeek |
TimeQuarter |
|
2009-12-29 |
2009 |
53 |
4 |
|
2009-12-30 |
2009 |
53 |
4 |
|
2009-12-31 |
2009 |
53 |
4 |
|
2010-01-01 |
2010 |
53 |
4 |
|
2010-01-02 |
2010 |
53 |
4 |
|
2010-01-03 |
2010 |
53 |
4 |
|
2010-01-04 |
2010 |
1 |
1 |
The numbering system in different countries might not comply with the ISO standard. There are at least six possibilities as shown in the following table:
|
First day of week |
First week of year contains |
Weeks assigned two times |
Used by/in |
|
Sunday |
1 January, First Saturday, 1–7 days of year |
Yes |
United States |
|
Monday |
1 January, First Sunday, 1–7 days of year |
Yes |
Most of Europe and the United Kingdom |
|
Monday |
4 January, First Thursday, 4–7 days of year |
No |
ISO 8601, Norway, and Sweden |
|
Monday |
7 January, First Monday, 7 days of year |
No |
|
|
Wednesday |
1 January, First Tuesday, 1–7 days of year |
Yes |
|
|
Saturday |
1 January, First Friday, 1–7 days of year |
Yes |
We have already provided a workaround to solve the issue. The workaround uses a custom Function F_ISO_WEEK_OF_YEAR and updates the fields [TimeWeekOfTheYear]of the table Reporting.dbo.MSP_TimeByDay with new week numbering.
In addition, we add a SQL Trigger to alert customer in case this table is modified by an external program.
Unfortunately, we have discovered several side effects with this solution, in particular with the SQL Trigger. In addition, this workaround does not update [TimeYear] and [TimeQuarter] fields which creates strange data hierarchy in the OLAP Time dimension.
This solution cannot be supported because we modify directly built-in data in the default OLAP calendar and we cannot warranty this table won’t be updated later with Cumulative Update or a Service Pack.
Finally, there are only 2 fully supported solutions:
· Use the Fiscal fields to store the new week numbering
· Create custom dimensions in the OLAP Cube.
The second solution is out of the scope of the Support. It can be done by MCS or a partner, but is quite complex to implement.
Marc has worked on the first option and he has created another solution, based on the first workaround we provided, but which uses the Fiscal fields and this now has support agreement from the Product Group.
This is the solution :
1. Create an ISO function to return the 1st day of the year
CREATE FUNCTION DBO.F_ISO_WEEK_OF_YEAR
(
@DATE DATETIME
)
RETURNS INT
AS
/*
FUNCTION F_ISO_WEEK_OF_YEAR RETURNS THE ISO 8601 WEEK OF THE YEAR FOR THE DATE PASSED.
*/
BEGIN
DECLARE @WEEKOFYEAR INT
SELECT
— COMPUTE WEEK OF YEAR AS (DAYS SINCE START OF YEAR/7)+1
— DIVISION BY 7 GIVES WHOLE WEEKS SINCE START OF YEAR.
— ADDING 1 STARTS WEEK NUMBER AT 1, INSTEAD OF ZERO.
@WEEKOFYEAR =
(DATEDIFF(DD,
— CASE FINDS START OF YEAR
CASE
WHEN NEXTYRSTART <= @DATE THEN NEXTYRSTART
WHEN CURRYRSTART <= @DATE THEN CURRYRSTART
ELSE PRIORYRSTART
END,@DATE)/7)+1
FROM
(
SELECT
— FIRST DAY OF FIRST WEEK OF PRIOR YEAR
PRIORYRSTART = DATEADD(DD,(DATEDIFF(DD,-53690,DATEADD(YY,-1,AA.JAN4))/7)*7,-53690),
— FIRST DAY OF FIRST WEEK OF CURRENT YEAR
CURRYRSTART = DATEADD(DD,(DATEDIFF(DD,-53690,AA.JAN4)/7)*7,-53690),
— FIRST DAY OF FIRST WEEK OF NEXT YEAR
NEXTYRSTART = DATEADD(DD,(DATEDIFF(DD,-53690,DATEADD(YY,1,AA.JAN4))/7)*7,-53690)
FROM
(
SELECT
–FIND JAN 4 FOR THE YEAR OF THE INPUT DATE
JAN4 = DATEADD(DD,3,DATEADD(YY,DATEDIFF(YY,0,@DATE),0))
) AA
) A
RETURN @WEEKOFYEAR
END
GO
2. Update [FiscalYear], [FiscalQuarter] and [FiscalPeriodName] with new calendar values :
· If the Quarter starts on January 1st:
BEGIN TRAN
UPDATE [MSP_TIMEBYDAY]
SET FISCALYEAR=TIMEYEAR,
FISCALQUARTER=TIMEQUARTER,
FISCALPERIODNAME = ‘Week’ + CAST(DBO.F_ISO_WEEK_OF_YEAR(TIMEBYDAY) AS NVARCHAR(2))
WHERE TIMEBYDAY BETWEEN ‘2010-01-01 0:00:00’ AND ‘2012-12-31 23:59:59’
COMMIT TRAN
· If the Quarter starts on first Monday of the year (2010, 2011 and 2012):
BEGIN TRAN
UPDATE [MSP_TIMEBYDAY]
SET FISCALYEAR=TIMEYEAR,
FISCALQUARTER=
CASE
WHEN DATEPART(DAY,TIMEBYDAY)<DAY(DATEADD(DD,(DATEDIFF(DD,-53690,DATEADD(DD,3,DATEADD(YY,DATEDIFF(YY,0,TIMEBYDAY),0)))/7)*7,-53690)) AND (DATEPART(MONTH, TIMEBYDAY)-1)%3 = 0 THEN TIMEQUARTER-1
ELSE TIMEQUARTER
END,
FISCALPERIODNAME = ‘Week’ + CAST(DBO.F_ISO_WEEK_OF_YEAR(TIMEBYDAY) AS NVARCHAR(2))
WHERE TIMEBYDAY BETWEEN ‘2010-01-01 0:00:00’ AND ‘2012-12-31 23:59:59’
COMMIT TRAN
3. Build an OLAP Cube
4. Create a View based on Fiscal fields :
Once all steps are done, this is the result you should get:
Thanks Marc for the great work here. The same solution should also be applicable to Project Server 2010.
