How Can I Speed up my Project Costs Reports?

If you’re a Microsoft Project Server report developer, you’ve no doubt come across the issue of long running reports that look to output time-phased cost data. Things are just fine until you go and add the costs to the query; then your 3 second query turns into a minute and a half. Sure the OLAP cubes are quicker, but what if your system doesn’t build them or the business insists on real-time data? By structuring your query a little differently and making use of temp tables, I’m going to show you how I’ve overcome this issue.

What’s the deal?

If you can’t utilize the OLAP cubes, your time-phased cost data lives in the AssignmentByDay table, along with a LOT of records. Think about it – records for every project, every task, every assignment, every day included in that assignment’s duration… we’re talking several thousands of table records.

So, if I wanted to include a Year-to-Date Actual Cost field in a project report, in the SQL query I’m going to have to join my project data to task data and then task data to this wealth of assignment data and only select records from the assignment data that meet my year-to-date criteria. If your system has long running schedules with hundreds of tasks with multiple assignees, your queries are going to work hard to produce data from such a deep dive into the AssignmentByDay table.

Keep in mind that the above example is stripped down so as not to show any sensitive, actual client data elements. I realize that in this stripped down version the approach of moving the heavy lifting for time-phased cost data to a temp table may make less sense but when your queries are crafted to accommodate business needs, they can get pretty complicated – and that’s when any processing time savings is greatly appreciated.

Good luck!

Interested in how EPMA can help your schedules? Contact us today at 1.888.444.EPMA or enroll for one of our training classes.