locked
PS 2010 OLAP Cube RRS feed

  • Question

  • I'm trying to figure out if this is normal...my OLAP cube database is set up to automatically be updated every night.  On the OLAP Database Management page (under Server Settings in PWA) the status is Build Success!.  However, when I click on the words 'Build Success', all of the Start Times and End Times are dated 2010 and not the previous night's date.  In the Build Tracking Comments, there are no error messages.  I select Build Now and Items 4-8 will have today's Start date and time.  Any ideas why the nightly update does not show the current date and time?

    Thanks,
    Kathy

    Tuesday, October 11, 2011 8:02 PM

Answers

  • I think the problem is in the stored procedure calls MSP_WEB_SP_QRY_CUBESTATUS_GetTimeRange in the ProjectServer_Published database. This procedure is missing a condition in its subqueries

    SELECT TOP 1 WCSTATUS_START_TIME FROM MSP_WEB_CUBE_STATUS B WHERE A.WCSTATUS_MSG_GROUP = B.WCSTATUS_MSG_GROUP

    It should be

    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

     Same with

    SELECT TOP 1 WCSTATUS_END_TIME ....

    This stored proc should be

     CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_CUBESTATUS_GetTimeRange

    @cubeGuid UID

     

    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 A.WCSTATUS_CUBE_UID =@cubeGuid AND a.WCSTATUS_MSG_LEVEL =  
    ORDER BY WCSTATUS_MSG_GROUP

     We tested in our environment. It works

    Sandy N.

    • Edited by Sandy Nguyen Friday, October 28, 2011 9:36 PM
    • Marked as answer by Kathy-COVB Thursday, November 3, 2011 4:00 PM
    Friday, October 28, 2011 9:33 PM

All replies

  • Hi Kathy,

    I have seen this before on an RTM install and I don't  remember seeing this on an environment with SP1 + June 2011 CU. What patch level is your Project Server farm?

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Tuesday, October 11, 2011 10:15 PM
  • We are at SP1 + June 2011 CU.  This happened before the update but is happening more after the update.
    Wednesday, October 12, 2011 1:39 PM
  • Ok, do you see the the same if you create another cube? Add a second cube and test that.. Are the server times in synch (app/webb and SQL)?

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Wednesday, October 12, 2011 2:01 PM
  • I created a new cube and the new cube reflects 2010 Start dates as well with no errors.  Manual build shows items 4-8 as current date.  Yes, the server times are in sync.  I'm open for other suggestions.

    Thanks

    Friday, October 14, 2011 8:40 PM
  • What is the date on your local system?

    What is the start and finish time of OLAP cube, under manage queue job page?

     

    Friday, October 14, 2011 8:54 PM
  • Execute following query against Project Server published database and compare the dates

     

    SELECT [WADMIN_CUBE_NAME]
          ,[WADMIN_CUBE_DATE_RANGE_FROM]
          ,[WADMIN_CUBE_DATE_RANGE_TO]
          ,[WADMIN_CUBE_LOG_STATUS_TIMESTAMP]
          ,[WADMIN_AUTO_LAST_RUN]     
          ,[CREATED_DATE]
          ,[MOD_DATE]
          ,[CREATED_REV_COUNTER]
        
      FROM [Prod_ProjectServer_Published].[dbo].[MSP_WEB_ADMIN_CUBE]

     

    Thanks,

    Hrishi Deshpande

    DeltaBahn LLC

     

    Friday, October 14, 2011 9:09 PM
  • Hi Kathy,

    Was this an upgrade from PS 2007 by any chance? I think I have seen this before on PWA DBs that were from a 2007 system in PS2010.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Friday, October 14, 2011 9:14 PM
  • SQL Results for 10/18/2011

    Analysis01 Cube (my original cube)

    [WADMIN_CUBE_DATE_RANGE_FROM] 11/30/2010

    [WADMIN_CUBE_DATE_RANGE_TO] 11/30/2010

    [WADMIN_CUBE_LOG_STATUS_TIMESTAMP] 11/30/2010

    [WADMIN_AUTO_LAST_RUN] 10/18/2011

    [CREATED_DATE] 11/30/2010

    [MOD_DATE] 11/30/2010

    [CREATED_REV_COUNTER] 0

    Analysis02 Cube (new cube)

    [WADMIN_CUBE_DATE_RANGE_FROM] 10/12/2011

    [WADMIN_CUBE_DATE_RANGE_TO] 10/12/2011

    [WADMIN_CUBE_LOG_STATUS_TIMESTAMP] 10/12/2011

    [WADMIN_AUTO_LAST_RUN] 10/18/2011

    [CREATED_DATE] 10/12/2011

    [MOD_DATE]10/12/2011

    [CREATED_REV_COUNTER] 0

     

     

     

     

    Tuesday, October 18, 2011 8:50 PM
  • We imported projects from 2010, but all other setups were newly created.

    Tuesday, October 18, 2011 8:51 PM
  • Is it possible to delete the Cube from Analysis Server and rebuild with the same name?

     

    Thanks,

    Hrishi

     

    Wednesday, October 19, 2011 4:23 PM
  • Sure - I think I've done that.
     
    Why?  You could just rebuild the cube from Project Server...
     
     

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Wednesday, October 19, 2011 8:41 PM
  • Hi Kathy,

    I have done some investigations into this issue and found the following.

    When looking at the Cube build status page, the build stages data is not filtered correctly for that particular cube. At the moment I can't find how this is queried as the stored procedure used for this (MSP_WEB_SP_QRY_CUBESTATUS_GetAllStatus) does filter with the correct cube GUID. If you look in the MSP_WEB_CUBE_STATUS table in the published database you will see date and times for the build stages you are seeing in the web. If you run the following example query you will see the Start and end times that you see in the web but this will be against a different cube guid, you can check the correct cube guid in the following table dbo.MSP_WEB_ADMIN_CUBE.

    select * from dbo.MSP_WEB_CUBE_STATUS
    where WCSTATUS_MSG_TEXT like 'Verifying and running pre-build server event handler%'

    I can replicate this issue on a vanilla 2010 environment with SP1 + August 2011 CU that has 1 cube that shows the correct start and end times in the Build Stages section by adding and building a 2nd cube. The start and end times for the build stages for the 2nd cube will show the times for the original cube and not reflect the times for the 2nd cube.

    I would log this to Microsoft to get this resolved.

    Hope that helps

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com
    Thursday, October 20, 2011 1:36 PM
    • Proposed as answer by Sandy Nguyen Friday, October 28, 2011 9:19 PM
    Thursday, October 20, 2011 4:00 PM
  • I think the problem is in the stored procedure calls MSP_WEB_SP_QRY_CUBESTATUS_GetTimeRange in the ProjectServer_Published database. This procedure is missing a condition in its subqueries

    SELECT TOP 1 WCSTATUS_START_TIME FROM MSP_WEB_CUBE_STATUS B WHERE A.WCSTATUS_MSG_GROUP = B.WCSTATUS_MSG_GROUP

    It should be

    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

     Same with

    SELECT TOP 1 WCSTATUS_END_TIME ....

    This stored proc should be

     CREATE PROCEDURE dbo.MSP_WEB_SP_QRY_CUBESTATUS_GetTimeRange

    @cubeGuid UID

     

    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 A.WCSTATUS_CUBE_UID =@cubeGuid AND a.WCSTATUS_MSG_LEVEL =  
    ORDER BY WCSTATUS_MSG_GROUP

     We tested in our environment. It works

    Sandy N.

    • Edited by Sandy Nguyen Friday, October 28, 2011 9:36 PM
    • Marked as answer by Kathy-COVB Thursday, November 3, 2011 4:00 PM
    Friday, October 28, 2011 9:33 PM