February, 2010

*** Update *** see http://blogs.msdn.com/brismith/archive/2010/03/09/office-servers-february-cu-and-the-project-server-database-versions-table.aspx for information regarding the db versions table - no db updates in this project Server CU, so no new number in the versions table.  If you hadn't already loaded the Dec CU then the version will change to December's.

Full details, see the Admin blog at http://blogs.technet.com/projectadministration/archive/2010/02/26/february-2010-cumulative-update-cu-for-project-server-2007-office-sharepoint-server-2007-wss-and-project-2007-now-available.aspx

There were also a few fixes that just missed incorporation in the CU, but are also available – from the Admin blog:

Note:  There has been a follow-up CU build that was released just after the February CU.  This CU contains three fixes that are not included in the February CU.  Please check http://support.microsoft.com/kb/980854 to see if these are fixes you need.  This CU also contains all of the fixes in the February CU so you will not need to install the February CU if you install this fix unless you are a Microsoft Office SharePoint Server (MOSS) user.  Customers running MOSS will want to install the server rollup fixes and then the post-February CU.  This CU will not be downloadable from the KB so you will need to open a Support Case in order to obtain it.  Customers with existing cases open for the February CU do not need to open another case.

As always, there can be delays in the KB’s and if you need the downloads the ‘secret’ is to substitute the desired KB number into the following URL - http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=XXXXXX&kbln=en-us.

Enjoy!

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:

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:

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.

http://blogs.technet.com/elfarodeprojectserver/ 

Hola, buen día a tod@s. Desde el equipo de soporte  de Project Server en castellano, queremos saludar a todo aquel que esté leyendo estas líneas. Llevábamos tiempo consultando otros blogs de Project Server, y nos parecía que ya era hora de poder disponer de un espacio donde se trataran estos temas, precisamente en la lengua de Cervantes.

En este blog pretendemos dar salida a aspectos y cuestiones que nos parecen interesantes y de utilidad para la comunidad de usuarios de Project Server, intentando ser, en cierta manera, una especie de faro o referencia, para que nuestro día a día de navegación con Project Server resulte más sencillo.

En un principio nuestra intención es tratar asuntos de Project Server 2007, aun cuando nos iremos enfocando posteriormente en la nueva versión, Project Server 2010, así como tocaremos temas de aplicaciones que conviven en el día a día con nosotros como Sharepoint, SQL Server, etc

Sirvan estas líneas de bienvenida para expresaros nuestro agradecimiento por estar leyendo este humilde blog. Muchas gracias por todo, esperamos sinceramente  que nuestros artículos, así como la información que podamos incluir os sirvan de tanta ayuda como a nosotros.

Un saludo, estamos a vuestra disposición

El equipo de soporte en castellano de Project Server

http://blogs.technet.com/frenchpjblog/

Le Support Technique EPM rejoint aujourd’hui la communauté des blogueurs EPM. Vous trouverez régulièrement sur ce blog les contributions des différents membres de l’équipe et plus précisément des membres appartement à l’équipe Escalation Services.

Sur ce blog, nous allons nous focaliser essentiellement sur 2 types d’informations :
- Des informations et des méthodes de Troubleshooting que nous voulons partager avec la communauté et qui parfois ne sont pas assez documentés dans les articles disponibles sur le web. Ces informations seront souvent issues de notre propre expérience et de nos propres résultats de résolution d’incidents clients.

- Des informations sur les ressources, outils, logiciel, évènements techniques qui vous aideront à utiliser la solution EPM dans votre environnement. Par exemple, nous pouvons créer un article sur la disponibilité des correctifs et des Service Packs disponibles et y ajouter des informations et des bonnes pratiques d’installation sur le serveur et les stations.

Nous attendons avec impatience de pouvoir partager des idées et des informations tous ensemble sur ce blog.

Marc Biarnès

Escalation Engineer, Microsoft CSS - France

I’ve been a bit quiet on the blog since the holidays (thanks Ben for giving me the push I needed ;)) – so apologies if I haven’t answered your questions.  I did have a bit of a catch up today, so there may be some new answers.  I’ve also seen a few direct e-mails from the blog and have answered some – but will re-set expectations here – I don’t always have a chance to respond to these.

I will be making a couple of posts in the next day or two with details of two new local language blogs for Spanish and French – with a German one following soon!

Other news this week:

• From the Project Programmability blog – new 2010 impersonation article and availability of a Project Server 2010 VM
• Webcast this Wednesday – overview of Project Server 2010
• Microsoft Project 2010 Webcast: Project Server 2010 Overview
  Pradeep GanapathyRaj, Senior Program Manager Lead, Microsoft
• Date: Wednesday, February 17, 2010 Time: 7:00am – 8:00am PST
• Register now for this session!
• The Project blog has a new look and continues the excellent 2010 series with an article on the new grid
• The Project Admin blog continues with 2010 topics – and also has an article on the new partner community work for the new release.
• Christophe has been really busy on the blog, between his various world-wide 2010 training deliveries – and my favorite was the complete list of great RSS feeds for project complete with opml file!

As we approach the home straight on Project 2010 I will be making more frequent posts, as well as some guest appearances on the Admin blog.  This release is going to be great!