Cascade Skyline - with Microsoft Logo and Project Support header - author Brian Smith

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

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

  • Comments 7

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

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 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 :

clip_image002

Once all steps are done, this is the result you should get:

clip_image003

Thanks Marc for the great work here.  The same solution should also be applicable to Project Server 2010.

Technorati Tags: ,
Leave a Comment
  • Please add 6 and 6 and type the answer here:
  • Post
  • Hi Brian,

    thanks for posting this solution.

    Will minor changes to update [FiscalYear], [FiscalQuarter] and [FiscalPeriodName] with new calendar values be also supported? I'd prefer to have weeks sorted by week number instead of an alphabetical sort (replace CAST(DBO.F_ISO_WEEK_OF_YEAR(TIMEBYDAY) AS NVARCHAR(2)) by right(str(100 + DBO.F_ISO_WEEK_OF_YEAR(TIMEBYDAY)),2) to avoid 10, 11, .. before 2,3, ..).

    Thanks

    Barbara

  • Hi Barbara,

    I don't see this would be any problem, and that is a great suggestion. If you do ever need our support help and feel this may have impacted the issue you are seeing then telling the support engineer what has changed should make things easier.

    Best regards,

    Brian.

  • Hi Brian

    Thanks for your solution. As i am not very "techie", does this solution only corrects the week numbering until 2012? If so, is another script needed in 2012 in order to correct the next years week numbering?

    Thanks for your response in advance!

    Kind regards Alex

  • Hi Brian,

    Thank you for this solution. But unfortunately I will get following error on my SQL Server after starting step 2. "The conversion of a varchar data type to a datetime data type resulted in an out-of-range datetime value." Do you know anything about this error? Please, can you help me?

    Thanks.

    Danny

  • Hi Brian,

    I solved this issue by myself. The problem was the date time format. If I took following clause then it starts: "WHERE TIMEBYDAY BETWEEN '2010-01-01 00:00:00.000' AND '2012-31-12 00:00:00.000'". Thanks for this great solution.

    Best regards,

    Danny

  • Thanks for following up with your solution Danny!

  • I'm seeing an issue with Data Analysis reporting under "Week 9" and "Week 10" in my PS 2007.  Basically, Week 9 was Feb 27-Mar 2nd (M-F respectively adn a leap year). My "Week 9" has the last days of Feb but not 3/1 and 3/2 even though we checked and there is time associated with these 2 dates. I used a smaller search to see if the pivot was too long and the dates are still missing. Additionally, they Week 10  starts as "day 4."

    These 2 days are just simeply not showing up under the Week/Day  Does anyone know what thta would be and how It could be fixed?

Page 1 of 1 (7 items)