UPDATE: Be sure to see the more recent post - http://blogs.msdn.com/brismith/archive/2008/03/24/slow-olap-cube-builds-and-large-tempdb-revisited.aspx
Some of this has been in previous postings or comments on postings - but thought it was time for a round-up. As a quick intro this blog will look at more information on tempdb growth some of you may have experienced since Project Server 2007 SP1, as well as timeouts that you might have hit due to the same problem. I'll also talk more about SQL Server 2005 plan guides used to overcome this problem - and finally errors you may see after your reporting database gets rebuilt - or refreshed as we call it in the queue.
In SP1, and in a hotfix just prior to SP1 we introduced some dimensions to the cube that we had missed in the initial release. These related to some of the flags for generic and team resources and a few other things. For customers that were also using many other dimensions and had large datasets these changes gave longer queue build times and also increased the use of tempdb as the SQL execution plan made use of table spools which create temporary tables in the tempdb database. In many cases the workaround described in my previous blog gives some relief from this problem. You may still need a reasonable amount of tempdb so don't bother shrinking tempdb below the level it would normally use - it will just slow things down as it has to grow again next time.
In some cases the increased cube build time may lead to the timeout issue dealt with below - and also applying the workaround can be challenging. To help understand the workaround a bit more on to plan guides...
SQL Server Books Online have always been a great resource since the early days of SQL Server. This is still true and the plan guide documentation can be found at Understanding Plan Guides. Basically whenever SQL runs a query it will look for any plan guides (in the system table sys.plan_guides - so select * from sys.plan_guides will show what is set) and if it has query_text in that table that matches the query it will follow the hints in that table. The query needs to be an exact match so any change in date or dimensions (or even order of dimensions added to a cube) may "break" the plan guide and the workaround would need to be re-applied. As the default name of the plan (from the workaround) is already in existence either a new name needs to be used or the plan guide needs to be dropped from the database. An example of the command to drop a plan guide called guide_forceorder would be:
EXEC sp_control_plan_guide N'DROP', N'guide_forceorder';
You can also enable and disable the plan guides using the sp_control_plan system stored procedure.
To see if a plan guide is being used you can run the profiler trace and add the Showplan XML event type (located under the Performance node). Then in the trace you should find the Showplan XML event for the affected query and it will start something like:
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3054.00"><BatchSequence><Batch><Statements><StmtSimple PlanGuideDB="pwa_Reporting" PlanGuideName="guide_forceorder"><QueryPlan CachedPlanSize="1463" CompileTime="336" CompileCPU="336" CompileMemory="9432"><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="1625" EstimatedTotalSubtreeCost="778.134" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Expr1007"/><ColumnReference….
If you don't find this then perhaps the query in the plan doesn't match what Analysis Services is using to pull the data.
One of my tips for support engineers is that if something fails at very specific times then be suspicious. So when a cube fails at just over an hour suspect that the default External Command Timeout is still set in Analysis Services. The default is 3600 and is in seconds - so is equal to 1 hour. Right click the AS server in Management Studio, then set Show Advanced (All) Properties and check the External Command Timeout. Increase as appropriate - 36000 would be 10 hours. For the SP1 issue you may find that this is the first error you hit - then once you get past this the tempdb problem may give you issues.
The error for the timeout issue is:
Failed to build the OLAP cubes. Error: Analysis Services session failed with the
following error: Failed to process the Analysis Services database <cube name> on
the <Analysis Server name> server. Error: Internal error: The operation terminated
Internal error: The operation terminated unsuccessfully. Internal error: The
operation terminated unsuccessfully. OLE DB error: OLE DB or ODBC error:
Unspecified error. 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 <cube name>
The initial part of the error may be different depending on the exact point the timeout stops things. The first part will also be localized if you are running a language pack.
Refreshing the reporting database loses the Assignment dimensions
The error for this problem is:
Failed to build the OLAP cubes. Error: Invalid call to GetJoinForCustomFieldDimension. Cannot join "Task Non Timephased" fact with a custom field of entity Assignment
And is caused when you have added dimensions to your assignment cube and then have done something that will have caused the reporting database to refresh. Restoring from certain administrative backups will do this. The fix is easy - just remove the dimensions from the assignment cube, save the configuration - then add them back again and save again. To be sure this is your issue take a look at the view MSP_EpmAssignment_OlapView in the reporting database. If the last column is AssignmentBaseline10BudgetMaterialWork then this means no dimensions are added. If the repository is expecting some dimensions you will get the error. A screenshot of the view with added dimensions will look something like:
I hope this helps to understand more about the cube build process and some of the problems you can run into.