none
Project Server 2007 - Cube Build Problem RRS feed

  • Question

  • Hi all,

    we've got an issue with our Project Server 2007 (SP2, Dec2009 CU) Cube Build. It's taking quite a long time and before you quit reading: yes, I know the work around with the execution plan (we already had several in use), but the problem is exactly that - the trace is not showing the bad sql.

    But let me start at the beginning: We had a project which could not be published to the Reporting DB due to an error referencing a foreign key constraint problem. We found a work around telling us to rebuild the Reporting DB by doing an administrative Backup and Restore of the Custom Fields. (We finally solved the problem otherwise, the rebuild didn't work).

    The next morning we had a cube build error and the error led us to the following blog http://blogs.msdn.com/b/brismith/archive/2008/02/28/olap-and-data-analysis-timeouts-plan-guides-and-tempdb.aspx: our cube could not be built because of the rebuild of the Reporting DB.

    We followed the blog advice removing all assignment dimensions from the cube configuration, saving it and then adding those dimensions again. And that's where our current problem probably results in:

    By saving a new configuration (though we added the same dimensions in the seemingly right order) it seems that our execution plan we had in place can not be matched any more (at least this hint is given in one of the comments of the above mentioned blog entry) - the cube is now again taking hours to build.

    We thought we could easily get the new execution plan into place (following the steps described by Brian Smith in http://blogs.msdn.com/b/brismith/archive/2008/02/28/olap-and-data-analysis-timeouts-plan-guides-and-tempdb.aspx). But Brian Smith is mentioning to wait a couple of minutes and then search the trace for "MSP_EpmAssignmentByDay_OlapView". But we are tracing for 11 hours now and still there is no sign of the statement we need to create the new execution plan.

    As I already did the tracing a few times I find it rather odd that the Select-Statement is still not there, I never had to wait for such a long time until it showed up in my trace. Do you have any suggestions why this statement might not be there? Do you think it will appear if we wait a while longer? Or might there be another problem with the cube and that's the reason it's taking so long.

    I would be grateful for all suggestions. We need the cube urgently and it is blocking our queue for quite some time now - and it is main reporting time. :-(

    Thanks a lot,

    Gáski

    PS: SQL Analysis Services run on SQL Server 2005 SP2 CU9.

    Thursday, September 23, 2010 8:17 AM

Answers

All replies

  • Hi Gaski,

    I assume you have set the trace flags as described in Brian's blog post?

    Have you tried updating the statistics on your Reporting database?

    Run 'sp_updatestats' on your ProjectServer_Reporting database (this can take some time), and try to rebuild the cube once updating the statistics is done.

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    Thursday, September 23, 2010 8:42 AM
    Moderator
  • Hi Hans,

    thanks for your quick reply. Yes, the trace flags are enabled and the update Statistics is run every night as maintenance job.

    We are momentarily assuming that the corrupt project schedule we tried to repair is causing some sort of a problem and prolonging the runtime. We are currently trying to have a look in the trace file to figure out why it is taking him so long to get to the complex statement.

    Regards,

    Gáski

    Thursday, September 23, 2010 9:01 AM
  • Hi,

    we aborted the cube build processs after 13 hours without a sign of the SQL Statement we were looking for. Our admin had a closer look at the trace file (until then he only looked for the keyword) and it seems that after about 5 minutes after starting the Cube build the following is the last message logged for the application MS Project Server in the trace log:

    <pre lang="x-sql">exec sp_cursor 180150135,33,1,N'[OlapObjects]',@ID='14C1784F-3A6B-435C-B7D2-21144DDBB2BE',@ParentID='00000000-0000-0000-0000-000000000000',@ClassType=2,@ObjectName=N'PWA_OLAPDb_03_1',@LastUpdated='2010-09-22 09:52:19:000',@Version=3,@ObjectDefinition=N'<DATABASE Name="PWA_OLAPCUBE" Description="MOPS Produktiv OLAP-Datenbank
    Project Server Site UID: b41ad25c-9863-4766-9a87-152787085dbb" LastUpdated="9:50:20 09/22/2010"><CUSTOMPROPERTIES><PROPERTY Name="RESERVED_DATABASE_LOCALE_UPDATED2" DataType="0" Value="B"></PROPERTY></CUSTOMPROPERTIES></DATABASE>'
    

    After that (the remaining 12 h and 55 minutes) there is no other message :-( So it seems the CBS is doing nothing at all.

    Why could this be?

    Gáski

    Thursday, September 23, 2010 11:32 AM
  • Hi there,

    iam the colleague from Gáski. She went to her well earned "long Weekend". 

    I ve run a trace against our Testenvironment, and got the following select statement as a extraction from the tracefile :

    SELECT [MSP_EpmAssignmentByDay_OlapView].[AssignmentCost] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentCost0_0],[MSP_EpmAssignmentByDay_OlapView].[AssignmentOvertimeCost] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentOvertimeCost0_1],[MSP_EpmAssignmentByDay_OlapView].[AssignmentActualCost] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentActualCost0_2],[MSP_EpmAssignmentByDay_OlapView].[AssignmentActualOvertimeCost] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentActualOvertimeCost0_3],[MSP_EpmAssignmentByDay_OlapView].[AssignmentWork] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentWork0_4],[MSP_EpmAssignmentByDay_OlapView].[AssignmentOvertimeWork] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentOvertimeWork0_5],[MSP_EpmAssignmentByDay_OlapView].[AssignmentActualWork] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentActualWork0_6],[MSP_EpmAssignmentByDay_OlapView].[AssignmentActualOvertimeWork] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentActualOvertimeWork0_7],[MSP_EpmAssignmentByDay_OlapView].[AssignmentMaterialWork] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentMaterialWork0_8],[MSP_EpmAssignmentByDay_OlapView].[AssignmentMaterialActualWork] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentMaterialActualWork0_9],[MSP_EpmAssignmentByDay_OlapView].[AssignmentBudgetCost] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentBudgetCost0_10],[MSP_EpmAssignmentByDay_OlapView].[AssignmentBudgetWork] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentBudgetWork0_11],[MSP_EpmAssignmentByDay_OlapView].[AssignmentBudgetMaterialWork] AS [MSP_EpmAssignmentByDay_OlapViewAssignmentBudgetMaterialWork0_12],[MSP_EpmAssignmentByDay_OlapView].[AssignmentResourcePlanWork] AS

    and so go on!

    Maybe thats the kind of Statement that we are waiting for also in Main Environment?

    So i tried it again against the Main Environment, but its always the same "error", like before.

    It seams like that the "sp" didnt come to the state to establish a connection to the Reporting DB (but thats much more a feeling than a fact!)

    Its running into a loop, the "error" statement comes all the time in a circle.

    Any Ideas? Hopefully of that comes a little bit more like "error" ;-) thank you a lot!

    Friday, September 24, 2010 2:22 PM
  • Hi Gáski, It looks like that trace is from Analysis Services rather than from the Database Engine?  Also if your SQL Server is patched to the latest CUs mentioned in my previous blog postings and the trace flags are set then you should not need to set the plan.

    Best regards,

    Brian.


    Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Friday, September 24, 2010 10:54 PM
    Owner
  • Hello Gaski,

    Here is an option you can try.  I have had similar problems and after exhausting all efforts, I basically deleted the cube from the ANALSYSIS SERVER and then I cleared all the records from the OlapOjbect table.  Once I did that, I was able to rebuild my cube and with my exisiting configuration.

    What I find intersting with this, is that I did not change any configuration information. So I am puzzled why it works when it is built from scratch.

    Some things to think about, is that this baiscally wipes out all you OLAP history, so if you delete projects from project server after they are complete, then the new cube will not include them. 

    I hope others will add comments, because I am interested in the pros and cons

    Cheers!

    MIchael Wharton, MBA, PMP, MCt


    Sincerely, Michael Wharton, MBA, PMP, MCT, MCSD, MCSE+I, MCDBA www.WhartonComputer.com
    Monday, September 27, 2010 2:17 PM
    Moderator
  • Hello Brian,

    thanks al lot for the hint. We really traced on the Analysis Services Server instead of the Database Server with the Reporting Database.

    After running the trace on the right server we managed to create the execution plan and got the cube working.

    We are not on SP3, but on SP2+CU9 and according to our Microsoft Escalation Engineer the execution plan is not needed for this patch level. I'm afraid, that even though the trace flags are set the cube build time was way too long, exceeding 8 hours. With the new execution plan in place, it is now even quicker than before with a build time of 17 minutes.

    Best regards,

    Gáski

    Thursday, September 30, 2010 8:42 AM