#ProjectServer 2010 cube build status issue, build stages showing incorrect times #PS2010 #MSProject

October 20, 2011 at 3:54 pm | Posted in Issue | Leave a comment
Tags: , ,

I recently looked into an issue (more of an annoyance rather than an issue as it doesn’t cause any errors / problems) for the Project Server 2010 cube build status page displaying the incorrect build stages times. I don’t have a resolution but thought I would share my findings.

Firstly I will show you the issue as I replicated it on my test environment, this can be seen below:

image

As you can see in the tracking comments section the cube was built today (20/10/2011) but the dates displayed in the build stages section shows 19/10/2011.

On my environment I have two cubes, one called test that was built on the 19th and one called zzzz that was built today:

image

On the environment where I was investigating the issue (not my test environment), they only had one cube. I decided to look in the Published database and could see where the dates were coming from, the table is MSP_WEB_CUBE_STATUS. This table keeps historical data for cubes that no longer exist which is fine but for some reason the cube build status page doesn’t show the correct start and end times at each stage for the cube that you are checking. The stored procedure that appears to be used to return this data (MSP_WEB_SP_QRY_CUBESTATUS_GetAllStatus) does filter for the correct cube and returns the correct time/dates so it is unclear how this page shows the incorrect time/dates.

**Update** The stored procedure that does appear to be returning the incorrect results is MSP_WEB_SP_QRY_CUBESTATUS_GetTimeRange. The existing query looks like this:

SELECT DISTINCT
WCSTATUS_MSG_GROUP,
WCSTATUS_START_TIME = (SELECT TOP 1 WCSTATUS_START_TIME FROM MSP_WEB_CUBE_STATUS B WHERE A.WCSTATUS_MSG_GROUP = B.WCSTATUS_MSG_GROUP),
WCSTATUS_END_TIME = (SELECT TOP 1 WCSTATUS_END_TIME FROM MSP_WEB_CUBE_STATUS B WHERE A.WCSTATUS_MSG_GROUP = B.WCSTATUS_MSG_GROUP)
FROM dbo.MSP_WEB_CUBE_STATUS A
WHERE
WCSTATUS_CUBE_UID = @cubeGuid
AND WCSTATUS_MSG_LEVEL = 0
ORDER BY WCSTATUS_MSG_GROUP

but it should filter in the nested select as shown below in bold:

SELECT DISTINCT
WCSTATUS_MSG_GROUP,
WCSTATUS_START_TIME = (SELECT TOP 1 WCSTATUS_START_TIME FROM MSP_WEB_CUBE_STATUS B WHERE A.WCSTATUS_MSG_GROUP = B.WCSTATUS_MSG_GROUP and A.WCSTATUS_CUBE_UID = B.WCSTATUS_CUBE_UID),
WCSTATUS_END_TIME = (SELECT TOP 1 WCSTATUS_END_TIME FROM MSP_WEB_CUBE_STATUS B WHERE A.WCSTATUS_MSG_GROUP = B.WCSTATUS_MSG_GROUP and A.WCSTATUS_CUBE_UID = B.WCSTATUS_CUBE_UID)
FROM dbo.MSP_WEB_CUBE_STATUS A
WHERE
WCSTATUS_CUBE_UID = @cubeGuid
AND WCSTATUS_MSG_LEVEL = 0
ORDER BY WCSTATUS_MSG_GROUP

As mentioned there is currently no resolution to this as it would not be recommended to update the Stored Procedure and this probably needs to be raised to Microsoft if this is causing issues, but hopefully this will explain the “random” time and dates displayed in the build stages section on the cube build status page.

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: