Week numbers in Olap Views

[Barbara - Austria]

Hi,

perhaps this is the wrong forum, since English speaking countries do not
have this issue? But the community in this forum is a lot larger and there
are excellent specialists answering here …

Most European countries (except UK) use ISO 8601 week numbering system. That
means that the first week of a year is that one in which January 4th is part
of.
2007-2009 were fine, since January 1st and January 4th were both part of the
same week.

Starting this year it is different (and it will be the next few years).
January 1st and January 4th are in different weeks, so week numbers will be
different.

Project Professional seems to take the numbering system from Regional
Settings, in Outlook you can choose the setting manually, but there is
nothing like that for Project Server.

This is an issue especially for OLAP Views. Data is retrieved from
TimeWeekOfTheYear in MSP_TimeByDay (and MSP_TimeByDay_OlapView) where it is
stored according to US and UK counting system, but not according to storage
on the European continent. Since database changes are not supported I am
looking for another idea. Can someone give me a tip?

Thanks in advance
Barbara

 

[Mike Mahoney]

Hi,

perhaps this is the wrong forum, since English speaking countries do not
have this issue? But the community in this forum is a lot larger and there
are excellent specialists answering here …

Most European countries (except UK) use ISO 8601 week numbering system. That
means that the first week of a year is that one in which January 4th is part
of.
2007-2009 were fine, since January 1st and January 4th were both part of the
same week.

Starting this year it is different (and it will be the next few years).
January 1st and January 4th are in different weeks, so week numbers will be
different.

Project Professional seems to take the numbering system from Regional
Settings, in Outlook you can choose the setting manually, but there is
nothing like that for Project Server.

This is an issue especially for OLAP Views. Data is retrieved from
TimeWeekOfTheYear in MSP_TimeByDay (and MSP_TimeByDay_OlapView) where it is
stored according to US and UK counting system, but not according to storage
on the European continent. Since database changes are not supported I am
looking for another idea. Can someone give me a tip?

Thanks in advance
Barbara

Hi Barbara

The best way to manage this is via timesheet periods. Suggest you
prefix the period label with year reference (09 or 10), bulk build the
periods. There may be 52 or 53 weeks in any year, with subsequent year
being less. In any case you can control which is week 1 in the year.
This dimension is available in the cubes.

rgds

Mike

 

[Barbara - Austria]

Hi Mike,

thanks for your idea!!

Unfortunately I can’t use it by two reasons. Most of our customers are
working with monthly timesheets, so I can’t change that in a system which is
already productive. Moreover timesheet periods are only available in the 3
timesheet cubes, not in the mostly used MSP_Portfolio_Analyzer.

I have already modified MSP_TimeByDay_OlapView on a test system and it is
working. But I am to afraid to change it on a production system since it is
not supported by MS.

I am really wondering what other Europeans are doing?

Regards
Barbara

 

[Mike Mahoney]

Hi Mike,

thanks for your idea!!

Unfortunately I can’t use it by two reasons. Most of our customers are
working with monthly timesheets, so I can’t change that in a system which is
already productive. Moreover timesheet periods are only available in the 3
timesheet cubes, not in the mostly used MSP_Portfolio_Analyzer.

I have already modified MSP_TimeByDay_OlapView on a test system and it is
working. But I am to afraid to change it on a production system since it is
not supported by MS.

I am really wondering what other Europeans are doing?

Regards
Barbara







- Show quoted text -

Hi Barbara

There is a germ of an idea in this blog

http://blogs.msdn.com/brismith/arch...tart-your-data-analysis-week-on-a-monday.aspx

A friendly dba could possibly manipulate week references in similar
manner

rgds

Mike

 

[Barbara - Austria]

Hi Mike,

thank you again!! Great that someone helps thinking.

I knew Brian’s blog and was already disappointed that there isn’t something
like “weekfirstâ€. But your answer raises a new question: If no-one has an
idea for a workaround, do I need to open a support case for SQL Server rather
than for Project Server? ;-)

When I changed searching for my issue in combination with SQL Server instead
of Project Server, I found a lot more posts – with no answers. In most of the
cases the answer was “works as designedâ€â€¦.

I think that I need to open a support call, but I am afraid that I already
know the answer? And will still need to find a workaround.

Regards
Barbara

 

[Mike Mahoney]

Hi Mike,

thank you again!! Great that someone helps thinking.

I knew Brian’s blog and was already disappointed that there isn’t something
like “weekfirst”. But your answer raises a new question: If no-one has an
idea for a workaround, do I need to open a support case for SQL Server rather
than for Project Server? ;-)

When I changed searching for my issue in combination with SQL Server instead
of Project Server, I found a lot more posts – with no answers. In most of the
cases the answer was “works as designed”….

I think that I need to open a support call, but I am afraid that I already
know the answer? And will still need to find a workaround.

Regards
Barbara







- Show quoted text -

Good Luck

Mike

 

[Barbara - Austria]

Hi Mike,

thanks for your "good luck" - it helped. Brian posted a supported solution:
http://blogs.msdn.com/brismith/arch...umbering-in-project-server-2007-and-2010.aspx

Regards
Barbara