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]
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.
Interested in how EPMA can help your schedules? Contact us today at 1.888.444.EPMAor 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