none
MS Project 2007 OLAP not building issue (loosing hair rapidly) RRS feed

  • Question

  • Hi Guys

    Happy New Year

    I have been cursed by my favourite of problems, the dreaded OLAP cube has decided that it's not going to build again.  Up to the 1st January it's been quite happy, but now that I need one for my month end processing it is stubbornly refusing to build.  Though I have successfully created a cube for a different and much smaller instance of Project.

    This is the error on the build screen I am getting

    "Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to process the Analysis Services database Dec2012f on the gbnh-app03 server. Error: Server: The operation has been cancelled. Errors in the OLAP storage engine: An error occurred while processing the 'Assignment Timephased' partition of the 'Assignment Timephased' measure group for the 'Assignment Timephased' cube from the Dec2012f database. Internal error: The operation terminated unsuccessfully. "

    This is the queue error message

    <?xml version="1.0" encoding="utf-16"?>
    <errinfo>
      <general>
        <class name="CBS message processor failed">
          <error id="17004" name="CBSOlapProcessingFailure" uid="b3896b9b-24bc-4b8a-8cf1-fce3c992749b" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=gbnh-app03 ASDBName=Dec2012f ASExtraNetAddress= RangeChoice=0 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/02/2013 19:00:58 ToDate=01/02/2013 19:00:58 HighPriority=False" Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database Dec2012f on the gbnh-app03 server. Error: Server: The operation has been cancelled.&#xA;Errors in the OLAP storage engine: An error occurred while processing the 'Assignment Timephased' partition of the 'Assignment Timephased' measure group for the 'Assignment Timephased' cube from the Dec2012f database.&#xA;Internal error: The operation terminated unsuccessfully.&#xA;" />
        </class>
        <class name="Queue">
          <error id="26000" name="GeneralQueueJobFailed" uid="0507b386-a74a-4d6c-a714-70684db5d17e" JobUID="62006460-28f8-45e0-b23e-13f5c3dda9e4" ComputerName="GBNH-APP01" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
        </class>
      </general>
    </errinfo>

    I have tried several runs, all of which error in the much same way, except one which complained that it could not add 15 users to one of the permissions groups.  That one turned out to be a bit of a red herring.  I have noticed that it does not always fail on the same Partition/Measure.

    Fixes I have tried -

    Reboot all servers, in case space/sql issues were preventing the process.

    Cleared down Tempdb - I know that should happen on reboot but I thought it was worth a try

    Removed the OLAP read permissions from all groups except admins (red herring)

    Switched to a different Analysis Services server

    If anyone has any ideas or thoughts as to where to look to further diagnose the issue I would very much appreciate the help.

    John.

    Thursday, January 3, 2013 8:58 AM

All replies

  • Hi John,

    have a look at http://blogs.msdn.com/b/brismith/archive/2007/05/29/a-couple-more-cube-build-error-messages-you-might-come-across.aspx. Brian is describing exactly your error, as far as I understand. Perhaps it does help?

    Regards
    Barbara

    Thursday, January 3, 2013 9:14 AM
    Moderator
  • Thanks Barbara, much appreciated.

    One development is that I have managed to process the cube through analysis services, which is at least a step forward.

    John.

    Thursday, January 3, 2013 9:32 AM
  • I had already set the external command timeout to 36000, I think we had that issue before.  On the offchance that it might be a connection timeout I upped that to 600.  Sadly it is still erroring.

    Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Failed to process the Analysis Services database Dec2012g on the gbnh-app03 server. Error: Server: The operation has been cancelled. Errors in the OLAP storage engine: An error occurred while processing the 'Assignment Timephased' partition of the 'Assignment Timephased' measure group for the 'Assignment Timephased' cube from the Dec2012g database. Internal error: The operation terminated unsuccessfully

    Error summary/areas:
    CBS message processor failed
    CBSOlapProcessingFailure
    Queue
    GeneralQueueJobFailed
    Error details:

    <?xml version="1.0" encoding="utf-16"?>
    <errinfo>
      <general>
        <class name="CBS message processor failed">
          <error id="17004" name="CBSOlapProcessingFailure" uid="5333c1b4-3dda-4f9d-a424-6ead2c2538a6" QueueMessageBody="Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=gbnh-app03 ASDBName=Dec2012g ASExtraNetAddress= RangeChoice=0 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=01/03/2013 09:59:00 ToDate=01/03/2013 09:59:00 HighPriority=True" Error="Analysis Services session failed with the following error: Failed to process the Analysis Services database Dec2012g on the gbnh-app03 server. Error: Server: The operation has been cancelled.&#xA;Errors in the OLAP storage engine: An error occurred while processing the 'Assignment Timephased' partition of the 'Assignment Timephased' measure group for the 'Assignment Timephased' cube from the Dec2012g database.&#xA;Internal error: The operation terminated unsuccessfully.&#xA;" />
        </class>
        <class name="Queue">
          <error id="26000" name="GeneralQueueJobFailed" uid="a8643345-9c22-456f-9c43-8de51f73d3e9" JobUID="07170066-232a-4491-a5d4-5b898eee62cc" ComputerName="GBNH-APP01" GroupType="CBSRequest" MessageType="CBSQueueMessage" MessageId="2" Stage="" />
        </class>
      </general>
    </errinfo>

    Thursday, January 3, 2013 10:15 AM
  • Barbara

    My apologies for jumping into the thread, but I have something to share.

    Hi John,

    Above issue could occur due to orphan entry of cube configuration.  Please confirm if you have add custom cube  dimension in to OLAP cube configuration?

    1. Navigate to PWA >>> Server Settings >> Cube Configuration
    2. Note down your existing cube configuration (cube dimensions)
    3. Remove all (Task, Resource, Project etc.) cube dimension from OLAP cube configuration.
    4. Execute following SP on reporting database to validate the result. Ideally no rows should be returned.

    exec MSP_Epm_GetCustomFieldsWithDimensionRole

    1. Identify any listed cube dimension, (think you may need to look for Entity name column, not sure I don’t have 2007 instance at this moment.) You may see something like
    2. From OLAP cube configuration add same custom field, save the settings, once again navigate to cube configuration and remove previously added custom field.
    3. Execute same SP, and validate the result, this time you should get zero rows.
    4. Try building the cube with 1 day duration.
    5. Once successfully built the cube, add your original cube configuration and rebuild the same cube

     


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful.This can be beneficial to other community members reading the thread.


    Thursday, January 3, 2013 6:03 PM
    Moderator
  • Hi Hrishi

    I had already removed all my custom fields (forgot to mention that), running MSP_Epm_GetCustomFieldsWithDimensionRole returned 0 rows.  The 1 day cube ran ok, so I reset it back to the full range and ran the cube, which failed.  I then processed the cube manually from the SQL Manager, which had previously worked, but failed complaining it could not find the custom fields.

    I followed your steps added and removed a custom field to the cube and reran it.  This time it failed to run through PWA, but did process manually.

    If you have any thoughts I'd appreciate it, in the meantime I'm running a cube using different date ranges to see how big I can get it before it falls over again.

    John.

    Friday, January 4, 2013 12:46 PM
  • In the past when I could not get the cube to build, i would delete the cube out of analysis server and have the cube built from scratch.   This fixed many issues.

    The issue with this solution, is that you lose all your previous history.  For example, you have projects that have been deleted, but the data is still in the cube.

    And the the big risk is that, deleting and rebuilding the cube from scratch doesn't fix the issue.

    It's scary, but that fixed many issues for me in the past.


    Michael Wharton, MBA, PMP, MCT, MCSD, MCSE+I, MCDBA, MCC 2011, MCC2012
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Friday, January 4, 2013 4:03 PM
    Moderator
  • John,

    We need to enable SQL trace flags 4101 & 4121. This flags will not cause any issues , however will improve the performance of cube build

    • To validate the current status of these trace flags , execute following query on SQL server master database. 0= Disable, 1=enabled

           DBCC tracestatus (4121,4101)

    • To enable these trace flags

          DBCC traceon (4121,4101)

    • Above setting will be active until SQL services are restarted, to add these trace flags permanently please use following steps

         a) Launch the SQL Server Configuration Manager
         b) Right-click on the SQL Server entry on the rightmost pane and choose Properties
         c) Select  advanced tab
         d) Change the Start-up Parameters entry
         e) Add the following to the end of the list: ";-T4101;-T4121"
         g) Click OK

    • Also updating database statists might help. Use following query on all 4 project databases.

          SP_updatestats


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful.This can be beneficial to other community members reading the thread.

    Friday, January 4, 2013 5:20 PM
    Moderator
  • Hi Hrishi

    I've enabled the trace flags and re run the cube but sadly it still fails.  I've deleted the old cube and set it to run overnight, to see whether it will build completely from scratch.

    I ran tracestatus on the master db and enabled them, but it showed disabled on the project databases, so I enabled the there too.  I'm not entirely sure if that was the right thing to do or not.

    I have a scheduled maintenance package that runs before the cube build which updates the db statistics, it's still running so that may help for this evenings cube run.

    Hi Martin

    As I mentioned above I've now deleted the cube to see if that helps, will let you know how I get on.

    many thanks

    John

    Monday, January 7, 2013 12:53 PM
  • John,

    No you don't have to enable these trace flags on Project Databases

    Did you try building the cube manually from PWA site using "Build Now" option. If this issue is occurring with scheduled OLAP cube build job, try disabling the schedule job by unchecking the check box "update periodically" , save the settings and once again schedule the job


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful if a post has been useful to you.This can be beneficial to other community members reading the thread.


    Monday, January 7, 2013 4:51 PM
    Moderator
  • I've tried running the cube through PWA and via a schedule, the results are the same.  I've also processed the cube after a failure through the SQL Management Interface (connected to the Analysis Server of course).  On the build of a new cube, that seems to work, on an existing cube (even if I had deleted it before running the cube) it is patchy.

    I have managed to run a cube restricting with a restricted timespan (01/01/2012 - 31/01/2013).  I need to try a greater timescale than that, all I know is that 2 years does not work.  I've tried 2012-2013 and 2011-2012, neither work, which in my mind implies that it's a volume issue, not a specific plan issue.

    John.

    Wednesday, January 9, 2013 9:05 AM
  • Thanks for the update

    Please check properties of Tempdb in SQL. Make sure that it has been set to "Unrestricted File Growth"


    Hrishi Deshpande – Senior Consultant DeltaBahn
    Blog | < | LinkedIn

    Please click Mark As Answer; if a post solves your problem or Vote As Helpful if a post has been useful to you.This can be beneficial to other community members reading the thread.

    Wednesday, January 9, 2013 9:49 PM
    Moderator
  • You may have something there Hrishi.  The log file was unrestricted but the db file wasn't.  I've updated it and will let you know how it turns out.

    John.

    Thursday, January 10, 2013 8:47 AM