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.

A simple query may look like this:

/* Get YTD Actual Cost */
Select
	p.[ProjectName]
       ,p.[ProjectOwnerName]
       ,p.[ProjectStartDate]             
       /*
        System-specific data requested for output, such as          
        ..Baseline date or cost data
        ..Agregated Task level data
       */             
       ,sum(AssignmentActualCost) as [Project YTD Spend]
      
from   MSP_EpmProject_UserView p (nolock)
                          
       inner join MSP_EpmTask_UserView t (nolock)
       on     t.ProjectUID=p.ProjectUID
                    
       inner join MSP_EpmAssignmentByDay_UserView a (nolock)
       on     a.ProjectUID=p.ProjectUID
       and    t.TaskUID=a.TaskUID
             
                     /* Beginning of current year */  
where  a.TimeByDay >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
and    p.ProjectCostCenter = = @ProjectCC
                            
group by
        p.[ProjectName]
       ,p.[ProjectOwnerName]
       ,p.[ProjectStartDate]

Solution

In some systems, the above query might take way too long to process and eat up valuable processing power. To speed things up, I’m going to:

• fracture the query into 2 parts
• pre-aggregate the time-phased assignment data into a temp table
• join the temp table and with the project and task data to produce the output
• drop the temp tables

My new, quicker running query will now look something like this:

/* Stage YTD Actual Cost */
Select p.ProjectUID
       ,sum(AssignmentActualCost) as [Project YTD Spend]
into   #TempOutput1 
from   MSP_EpmProject_UserView p (nolock)

                   /* You could move this criteria to the Where clause */
       inner join (select   ProjectUID, TaskUID 
                     from   MSP_EpmTask_UserView (nolock)
                     where  TaskIsMilestone = 0
                     and    TaskIsActive=1) t
       on t.ProjectUID=p.ProjectUID
                    
       inner join MSP_EpmAssignmentByDay_UserView a (nolock)
       on     a.ProjectUID=p.ProjectUID
       and    t.TaskUID=a.TaskUID
             
where  a.AssignmentActualCost!=0
                      /* Beginning of current year */
and    a.TimeByDay >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
and    p.ProjectCostCenter = @ProjectCC                                      

group by
       p.ProjectUID

/* Output Data */
select p.ProjectUID
       ,p.ProjectName
       ,p.ProjectOwnerName
       ,p.ProjectStartDate                            
       ,p.ProjectBaseline0StartDate            
       ,p.ProjectActualStartDate             
       ,p.ProjectBaseline0FinishDate
       ,p.ProjectActualFinishDate
       ,p.ProjectFinishDate                   
       ,z2.[Project YTD Spend]                

from   MSP_EpmProject_UserView p (nolock)
             
       inner join (select   *
                     from   MSP_EpmTask_UserView (nolock)
                     where  TaskIsMilestone = 0
                     and    TaskIsActive=1) t
       on t.ProjectUID=p.ProjectUID     
                                 
       left outer join #TempOutput1 z2
       on p.ProjectUID=z2.ProjectUID
             
where  p.[Project Settlement Cost Center] = @ProjectCC

/* Clean up */
drop table #TempOutput1

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.

Please feel free to leave comments below or check out our other blogs on Microsoft Project, Project Server, SharePoint and Project Management Methodology.

Follow us at @EPMAinc, linkedin, or facebook