See http://blogs.msdn.com/brismith/archive/2008/03/24/slow-olap-cube-builds-and-large-tempdb-revisited.aspx for the latest information on this problem.
There is an issue with some of the SP1 and hotfix 939594 fixes which resolve earlier problems of data missing in the cubes. The query has become more complex and SQL Server 2005 is not coming up with the right execution plan for this new more complex query. The upshot of this is much use of tempdb - which can grow very large, and the cube build takes much longer than it did.
You may have already seen the blog which gives a workaround for this issue - but as many of you may see this anew after loading SP1 I wanted to raise awareness again. Thanks to Noel, Kermit, JF and Thuy for helping get to the bottom of this one.
The problem is mostly seen if you have added lots of dimensions to the various cubes and also if you specify a dynamic date range. A full build from earliest to latest is sometimes a workaround which reduces the impact on tempdb but is still slower than it should be. My suggested steps which are also mentioned in the referenced blog and given even more coverage in the MSDN article are:-
1. Create a cube set exactly how you want it (earliest to latest please - no dynamic date range) and while it is building capture a trace in SQL Profiler. SQL Database Engine should be profiled – can be restricted to the reporting db.
2. After a few minutes search in the running trace for MSP_EpmAssignmentByDay_OlapView. If it isn’t found then wait a little while longer and try again.
3. Once found then right click the line in the trace and select Extract Event Data. (DO NOT COPY FROM THE bottom pane). In the save as dialog save this as badquery.sql.
4. The current cube can be stopped at this point. If no one else is using Analysis Service then just restarting will kill this off. If you do restart then you will need to close out of the Cube Status window otherwise you can get errors with the next cube build.
5. In SQL Server Management Studio open a new query to the Database Engine and select the Reporting DB. Copy the following text into the query window.
EXEC sp_create_plan_guide N'guide_forceorder',
N'OPTION (force order)'
6. Open the badquery.sql file and select all (Ctrl-A or Edit, Select All) then Copy (CTRL_C or Edit Copy) and then select the <exact query> from the text in the query window and Paste (CTRL-V). You should have selected the < > character too. This may leave some space between the ‘ and the SELECT but this is fine.
7. Execute this command – which should finish successfully. Build a new cube and this should use this execution plan and process more quickly. To get an indication of the speed you could open badquery.sql and then at the end of the query paste OPTION (FORCE ORDER) and then execute.
8. You can also monitor in SQL Profiler to see if it is finishing quicker by looking for the MSP_EpmAssignmentByDay_OlapView and seeing if the SQL BatchCompleted comes in a reasonable time.
The reason to avoid dynamic date ranges is that this will make the query change every time it is run - and then the execution plan will not match - and will be ignored.
If you use a constant date range then this method can be applied - but read the MSDN article on the need to escape single quotations marks. The will be around the dates - so for instance '12/31/2008' would need to be ''12/31/2008''. Note that these are two individual quotes, not a double quote.
As an example this workaround can mean the tempdb hardly gets touched (rather than growing 30GB or more) and the cube builds in less than 1 tenth of the time. Your mileage may vary.
I hope this helps - and if you don't understand any of the steps above then you probably shouldn't attempt this - speak to your DBA. But if you are seeing this issue and need some assistance to address it then please open an incident with our support teams. Http://support.microsoft.comwill give you the options.
Technorati Tags: Project Server 2007
Thanks to our writers, we have a new publication for you. In their own words:-
It can sometimes be a challenge for new users to find their way through Project 2007 on their way to becoming project managers. The Project team has just produced another tool that will help you understand Project 2007 —The Project Management Quick Reference Guide. You can download and print this template for ease of use. Now you have another tool to help your organization achieve your project goals.
I have been waiting for SP1 before blogging this as it might have frustrated a few people as pre SP1 there were some broken pieces here. The filter that you use when creating a view for everyone through Server Settings, Manage Views behaves slightly differently from that found on the client side when a user creates their own filters. So why is this? To explain I will use the example of filtering for particular project types. I will base this on the out-of-the-box Project Center, Summary view, with the field Project Type added.
Here is a view of my starting point view - where "Maintenance Project" is the term for Proposals and Activity Plans.
When adding a filter on the client in Project Center the filter works as most people would expect - it is doing a text comparison. If I want to just see Maintenance Projects I can set a custom filter where the Field Name Project Type contains the word Maintenance.
And then we see:-
But if we want a view that users can choose that automatically has this filter then we probably want to define it on the server. Setting exactly the same filter in the view definition for Summary in Manage Views gives this:-
Why is nothing showing up? The reason is that server side filters are not working quite the same as on the client - and are doing their comparisons against an enumeration of the Project Type defined as a number. Maintenance Projects have a Project Type of 4 - so with a filter set like this - where Project Type equals 4
we get what we want on the client with no client side filters set.
Enumerations for various of these kind of things can be found in the SDK. This different behavior makes more sense when you look at different language versions. If you have created a PWA site in French for instance you can set a server side filter for Maintenance Project without needing to know the French term. Remember on the server you can filter on a field that is not displayed - so it isn't as silly as it sounds...
I know in this case it wouldn't make much difference - maintenance is maintenance - but other translations may be more challenging (at least to me). This is a site provisioned in Korean, and viewed with IE set to French - just so you can see we are still looking at the same field.
I hope this helps you understand why there are differences in the filters - and how to use them.
Blogging in Microsoft does allow a great deal of freedom to the blogger, but the final test before hitting "publish" is always to think "What would this story look like on the front page of the New York Times, or Wall Street Journal". I think the worst case interpretation of this blog might give a headline like "Steve Ballmer got it wrong" says Brian Smith. But hopefully it wouldn't come to that (he didn't get it wrong anyway) - and it probably wouldn't be the front page, and probably wouldn't even make the Valley View (my local paper) - so here goes...
At the time of the project conference the work on SP1 was going well - still not complete, but homing in on the final few fixes. So we knew we would soon have a release date we could stick to - but we weren't quite there. Steve Ballmer did a great keynote on the future of project and work management - along with some demonstrations of what is coming in the next release - and then to Q & A.
The inevitable question was the date of SP1! Steve was really gunning for the customers here and pushing Mike Angiulo for a date. At that point we were still on track for December, but with the holiday period approaching even a small issue could push this into the new year - so Mike gave Q1 as a safe release date. Steve's interpretation of Q1 was "March at the latest". So the date was now March! That's how it happened - and I know because, to quote Max Boyce, "I was there". And nobody really got it wrong.
You all now know we delivered SP1 on 12/11. Plenty of customers are happy to see it earlier than expected - so what compels me to write this? There are now various interpretations of this "early" delivery - all the way from "Microsoft has cut testing to ship SP1 early" through to "Microsoft doesn't know what it is doing if they can cut 3 months from the plan" (with the added irony that comes with such a statement when one is on the Project support team ). This certainly does an injustice to the many people who have worked very hard on this release both in the different product groups and the support teams. I'm not looking for mutual back-patting here - I know there are still some bugs we didn't fix with SP1 - but SP1 is the first step. Now we move on to the rollup patch of hotfixes we have addressed since SP1 was locked down - then to SP2.
This posting was different from my usual technical stuff but I think the better understanding customers and partners have of how we work - and sometimes how decisions are made - is of mutual benefit. Let me know what you think. And in case this does get brought to SteveB's attention - I love this company too (and my job):).
And before you ask - this blog wasn't based on a bet to mention Max Boyce and Steve Ballmer in the same paragraph, but that has to be a first.
The December 2007 update to the SDK is now available
Project Developer Portal: http://msdn2.microsoft.com/en-us/office/aa905469.aspx
SDK Online: Project 2007 SDK in MSDN online library: http://msdn2.microsoft.com/en-us/library/ms512767.aspx
SDK Download: http://www.microsoft.com/downloads/details.aspx?FamilyId=2672F6F9-7028-4B30-99A2-18CB1EED1ABE&displaylang=en
New and updated topics since July:
· ERP Connector Solution Starter (20 topics)
· How to: Customize E-Mail for Project Server Notifications – this is an update of the project_programmability blog post.
· Project XML Data Interchange Schema Reference includes the following topics:
· New XML Elements
· Custom Field Data in XML
· Saving and Opening Projects in XML Format
· How to: Use XSLT Transformations with Project XML Data Interchange Files
· Project Data Interchange Elements includes reference topics for all of the schema sections, XML structure, and XML elements.
PSI Managed Code Reference:
· Additional descriptions of row properties in the following datasets in the Project Server Interface (PSI): LookupTableDataSet, LookupTableMultiLangDataSet, AlertSubscriptionInfoDataSet, ReminderSubscriptionInfoDataSet, QueueStatusDataSet, QueueStatusRequestDataSet, StatusingSettingsDataSet.
· Additional descriptions for event receiver methods.
· ERP Connector: Complete sample source code and test application for the ERP Connector.
· XML Schema: Revised XML data interchange schema, mspdi_pj12.xsd.Note: The Project XML schema is also now published on http://schemas.microsoft.com/project/2007.
· ProjTool: Now includes a dialog box for backup and restore of selected projects.
· SP1 library assembly: Redistribution license for the SP1 build of the Microsoft.Office.Project.Server.Library.dll assembly, and 32- and 64-bit copies of the assembly.Note: The MSIL code of the assembly is the same in both cases, so the file size is the same. However, we provide both builds to be consistent with the Project 2007 SKUs and because there may be differences in the future.