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:
The Week numbering is based on the [TimeWeekOfTheYear] field which starts on January 1st according to the US standard:
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 :
If week starts on Monday :
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
1–7 days of year
Most of Europe and the United Kingdom
4–7 days of year
ISO 8601, Norway, and Sweden
7 days of year
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
FUNCTION F_ISO_WEEK_OF_YEAR RETURNS THE ISO 8601 WEEK OF THE YEAR FOR THE DATE PASSED.
DECLARE @WEEKOFYEAR INT
-- 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.
-- CASE FINDS START OF YEAR
WHEN NEXTYRSTART <= @DATE THEN NEXTYRSTART
WHEN CURRYRSTART <= @DATE THEN CURRYRSTART
-- 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)
--FIND JAN 4 FOR THE YEAR OF THE INPUT DATE
JAN4 = DATEADD(DD,3,DATEADD(YY,DATEDIFF(YY,0,@DATE),0))
2. Update [FiscalYear], [FiscalQuarter] and [FiscalPeriodName] with new calendar values :
· If the Quarter starts on January 1st:
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'
· If the Quarter starts on first Monday of the year (2010, 2011 and 2012):
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
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.
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, ..).
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.
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
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?
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.
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?