Addressing ISO 8601 Week numbering in Project Server 2007 (and 2010)

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 1^st.

But, according to ISO 8601, it should start on January 4^th (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

If week starts on Monday :

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 1^st 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 1^st:

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.