Visualizing Project Time

Project Online supports collaborative work management across your organization. It provides the ability to manage multiple project schedules and allows project team members to enter the time spent on assigned tasks via their timesheet. The timesheet information can then be used for many purposes.

Many Project Management Offices (PMOs) have dynamic information needs that require data interactivity to uncover the sought out answer. Power BI Desktop is the perfect tool to enable the data exploration process. In this article, we’ll focus on the PMO need to audit timesheet data, exploring who has submitted time and what types of time were submitted. Below is an example of an overall timesheet status dashboard, created in Power BI.

For those unfamiliar with Project, timesheet submission is generally a weekly process. The Project Manager publishes the latest project version. At the end of the week, project team members log how much time they spent on assigned tasks and submit their timesheet for approval. Project Managers then review and approve the timesheet submissions. Once this process is complete, status reports and other key data visualizations use this updated timesheet information to support organizational needs.

Figure 1 Weekly Timesheet Process

Success takes the whole village

Getting everyone to submit their timesheet in a timely manner is key to getting full value from time tracking. Thus, auditing timesheet submissions is an integral part of a successful time tracking implementation.

In a perfect world, everyone would submit their timesheet on time. The team member would get value from the data that supports their work. The organization would also get value from having data in a timely manner with minimal capture effort.

The village faces challenges

Many organizations face three time tracking challenges.

First, it’s difficult to get the entire organization to fill out their timesheet in a timely manner. Sometimes, you aren’t in the office on Friday so the timesheet doesn’t get submitted. Some people will simply forget to submit their time. Others may be more passive aggressive in that they view time tracking as a tax with little individual benefit.

Second, Project has no included functionality for easily auditing timesheet submission. If you want to use time tracking, you have to build your own business intelligence views to meet your analytic needs.

Lastly, reporting tools of years past focused on solving a particular business problem. They were not optimized for ad hoc querying to meet immediate and specific data needs. Below is an example of a dashboard for investigating where timesheets have gone missing.

This article will take you through the process of defining your business need, show you how to leverage a social design approach to design the solution and take you through the steps to address the need in Power BI.

Designing for Business Intelligence

Business intelligence answers the questions that support specific organizational conversations. Experience in BI Design indicates that mapping the social interactions first, will increase the chances that the designed visualizations will meet the business need.

I use Tumble Road’s Conversation-Centric Design (CCD) approach for designing Business Intelligence solutions. CCD helps you derive the technical design details by starting with the modeling of the targeted social interactions. This process will identify and define three key aspects: the audience, the conversations and the key questions. Once these aspects are defined, you can use these to define the data scenarios, individual data elements, common user terminology, and supporting processes.

Figure 2 Conversation-Centric Design Process

CCD can also be used as a scoping framework. For example, you can choose not to support a conversation and be able to track all aspects impacted by that decision. It also makes it easier to create a project Work Breakdown Structure based on Audience:Conversation:Key Questions structure, allowing the rollup of task progress to provide the health of each aspect automatically.

Requirements and Design

For this exercise, I’m limiting the design to one primary audience, the Project Management Office, and three key questions for the PMO.

Requirements

User Scenario

The previous week’s timesheet results for projects and administrative time are reviewed in the Monday morning PMO team meeting. Claire, the director, leads the meeting, where she reviews the results with Stephanie and Ian. The data is reviewed by open timesheet period. The outcome of the meeting is to follow up with the individual team managers to complete the process. Claire also uses the data to generate PMO reports to the company vice presidents. Therefore, she wants to have complete data as much as possible to generate the reports.

The organization uses administrative time categories so all timesheet eligible resources are expected to turn in a timesheet weekly even when the person is not currently assigned to a project. The PMO is only concerned with total time charged to a project. Only one time category is generally received, Actual Work Billable, but due to an intern incident earlier in the year, all Actuals will be summed together to ensure all time is captured. The timesheet periods are defined as running from Sunday to Saturday each week.

Jason and Sophie need the timesheet data for their own uses with Finance and HR but are passive observers to the review process.

Figure 3 Requirements in CCD format

Conversation

The business intelligence visualization is needed to support the PMO weekly review of timesheet data at 9AM on Mondays.

Audience

The PMO consists of five people, one director and four analysts. Claire, Stephanie and Ian are direct consumers of the data and are the primary audience of this exercise. Jason and Sophie are indirect consumers of the data. Claire, Stephanie and Ian, therefore, will be best choices for assessing whether the visualization meets their needs. Stephanie and Ian can also verify the data.

Key Questions

From the scenario above, we can derive the following key questions. NOTE: There are typically more than three but I find three is a great starting point.

• Have all timesheet eligible resources submitted their timesheet?
• Have all submitted timesheets been approved?
• Which teams have outstanding timesheets?

Technical Design

Supporting Data

The supporting data will denote three states for timesheet eligible resources. NOTE: Project refers to people as resources. Therefore, resources and project team members are interchangeable for this article.

• Have not created a timesheet
• Submitted a timesheet, but timesheet is still in process
• Timesheet was submitted and approved

The data set will be aggregated to the weekly level of granularity by resource and project. Only the last 60 days of data will be reviewed.

Data Sets

The primary data sets necessary to create the requisite visualizations are as follows.

Timesheet eligible resources are defined as work resources in Project that can log into the system, have an email address and are capable of working in the analyzed time frame.

• Identify and retrieve timesheet eligible resources using the following conditions
  • Work resources – Resource Type is 2
  • Resource is active in the system – ResourceIsActive = 1
  • Who can log into the system – ResourceNTAccount is not null
  • Are capable of working in the analyzed time frame – Resource Base Capacity is greater than zero.
• Identify and retrieve the submitted timesheets with associated project information for the last 60 days for timesheet periods that are open.
  • Timesheet Period Start Date is within the last 60 days.
• Identify all timesheet eligible resources who did not create a timesheet
  • This data will need to be derived using the other two data sets.

Visualizations

In order to answer the key questions, a timesheet status field is needed to hold the state. The following visualizations are needed to show the core data.

• Listing of each resource by timesheet period with the current timesheet status submission
• Stacked bar chart by timesheet period with a count of timesheets in each timesheet status
  • Provides a visual indication of resource level submission completeness.
• Stacked bar chart by team with a count of timesheets in each timesheet status
  • Provides a visual indication of team level submission completeness.
• Stacked bar chart by resource with a count of timesheets in each timesheet status
  • Provides a visual indication of resource level submission completeness.
• Amount of time logged per project per timesheet period
• Slicers will be required for timesheet period, team and timesheet status.

Data Elements for Visualization

This is the list of fields needed to generate the final visualizations. Other data will be needed in the process to generate the underlying data sets.

Supporting Processes

• Weekly Time tracking process

Tools

• Power BI Desktop
• PowerBI.com

Building the Data

Data Feeds

There are four OData feeds necessary to build the timesheet audit report.

Resources

The resources feed provides the data that is visible via the Resource Center in Project Web App. This feed provides access to all resource custom fields.

ResourceTimephasedDataSet

This feed provides access to the resource capacity data at a day level of granularity. The Max Units field in the Resource impacts the capacity available by day. If Max Units is set in PWA, all days will have the same capacity. Project Pro provides the additional capability to create different Max Units for specific date ranges.

TimesheetPeriods

This feed provides access to the date ranges per week for which timesheets are expected.

TimesheetLines

This feed provides the summary data as to what time was logged by the project team member.

Transformations

In order to build out the dataset for the visualization, the following transformations are required.

1. Identify and retrieve timesheet eligible resources using the following conditions
   1. Work resources – Resource Type is 2
   2. Who can log into the system – ResourceNTAccount is not null
   3. The result of this step is the potential list of resources who can submit a timesheet
   4. NOTE: Typically, you could use ResourceIsActive for steps 2 and 3. Be aware that the value changes if you turn on the new Resource Engagements feature. Feel free to change this in the code based on your individual configuration.
2. Join the Resources with the Resource Capacity records to determine if a person worked for a specific week
   1. Are capable of working in the analyzed time frame – Resource Base Capacity is greater than zero.
   2. The result of this step is the actual list of resources expected to submit a timesheet for the selected timesheet periods
3. Create a record for each timesheet eligible resource in step 2 for each timesheet period within the 60 day range. This is similar to a cross join in SQL.
   1. This is required as no timesheet record is created until the resource accesses the timesheet. Therefore, if resource never clicks the timesheet link, there’s no way to directly see that the timesheet wasn’t created.
   2. The result of this step is the master list of all timesheets expected for the selected timesheet periods.
4. Join the submitted timesheets with the master timesheet list from step 3.
   1. The result of this step is the status of all timesheet submissions and non-submissions.

Figure 4 Order in which to perform the data transformations

Power BI Transformation Code

Resources

This M code calls the Resources OData feed and filters the records for only those defined as timesheet eligible. The columns are then limited to only those necessary to support the end visualizations.

let
Source = OData.Feed(“https://YOURSERVERNAME.sharepoint.com/sites/YOURPROJECTWEBAPP/_api/ProjectData”),
Resources_table = Source{[Name=”Resources”,Signature=”table”]}[Data],
#”Filter for Work Resources who are Users” = Table.SelectRows(Resources_table, each ([ResourceType] = 2) and ([ResourceIsActive] = 1) and ([ResourceNTAccount] <> null) and ([ResourceEmailAddress] <> null)),
#”Get core fields” = Table.SelectColumns(#”Filter for Work Resources who are Users”,{“ResourceId”, “ResourceCreatedDate”, “ResourceEmailAddress”, “ResourceName”, “TeamName”, “ResourceDepartments”})
in
#”Get core fields”

ResourceTimephasedDataSet

One of the requirements was to only expect a timesheet when the resource had available capacity. This requires a look at the data in a timephased manner.

This M code calls the ResourceTimephasedDataset OData feed and performs the following transformations. Descriptive comments for the statement following are denoted with //. Also, each step has been renamed to provide clues as to its function, making the process somewhat self-documenting.

let
Source = OData.Feed(“https://YOURSERVERNAME.sharepoint.com/sites/YOURPROJECTWEBAPP/_api/ProjectData “),
ResourceTimephasedDataSet_table = Source{[Name=”ResourceTimephasedDataSet”,Signature=”table”]}[Data],

// Timephased datasets are usually huge, only get the last 60 days.
#”Filter for Last 60 Days” = Table.SelectRows(ResourceTimephasedDataSet_table, each Date.IsInPreviousNDays([TimeByDay], 60)),

//Since we only care about resources who could have worked, we want to

//examine Basecapacity for any instance greater than zero.

//Basecapacity is calculated using the resource’s MaxUnits value
#”Filter for BaseCapacity > 0″ = Table.SelectRows(#”Filter for Last 60 Days”, each [BaseCapacity] > 0),

//Need to group data by week, so insert new column for week of year
#”Inserted Week of Year” = Table.AddColumn(#”Filter for BaseCapacity > 0″, “WeekOfYear”, each Date.WeekOfYear([TimeByDay]), type number),

//Once grouped, join with Resources feed from previous

#”Merge with Resources” = Table.NestedJoin(#”Inserted Week of Year”,{“ResourceId”},Resources,{“ResourceId”},”NewColumn”,JoinKind.Inner),

// We need to expose the resource created date so that we can

// eliminate any capacity prior to the date of resource creation
#”Expand to see Resource Fields” = Table.ExpandTableColumn(#”Merge with Resources”, “NewColumn”, {“ResourceCreatedDate”}, {“ResourceCreatedDate”}),

// Create a new column that uses Value.Compare to flag the nature

// of the relationship. A value of 1 means TimeByDay > Created Date
#”Flag Valid Capacity” = Table.AddColumn(#”Expand to see Resource Fields”, “ValidCapacity”, each Value.Compare([TimeByDay],[ResourceCreatedDate])),

// Eliminate any rows which don’t have a 1, which are all of the rows

// that exist before the resource was created
#”Filter for Valid Capacity Only” = Table.SelectRows(#”Flag Valid Capacity”, each ([ValidCapacity] = 1)),

// Group the result set by week number
#”Group by ResourceID_WeekNumber” = Table.Group(#”Filter for Valid Capacity Only”, {“ResourceId”, “WeekOfYear”}, {{“Count”, each Table.RowCount(_), type number}, {“Capacity”, each List.Sum([BaseCapacity]), type number}, {“Startd”, each List.Min([TimeByDay]), type datetime}})
in
#”Group by ResourceID_WeekNumber”

Timesheet Lines

This M code retrieves the time logged against the individual projects.

let
Source = OData.Feed(“https://YOURSERVERNAME.sharepoint.com/sites/YOURPROJECTWEBAPP/_api/ProjectData”),
TimesheetLines_table = Source{[Name=”TimesheetLines”,Signature=”table”]}[Data],
#”Removed Other Columns” = Table.SelectColumns(TimesheetLines_table,{“TimesheetLineId”, “ActualOvertimeWorkBillable”, “ActualOvertimeWorkNonBillable”, “ActualWorkBillable”, “ActualWorkNonBillable”, “CreatedDate”, “PeriodEndDate”, “PeriodStartDate”, “PlannedWork”, “ProjectId”, “ProjectName”, “TimesheetApproverResourceId”, “TimesheetApproverResourceName”, “TimesheetName”, “TimesheetOwner”, “TimesheetOwnerId”, “TimesheetPeriodId”, “TimesheetPeriodName”, “TimesheetPeriodStatus”, “TimesheetPeriodStatusId”, “TimesheetStatus”, “TimesheetStatusId”}),

// Only get the last 60 days of timesheet lines
#”Filtered Rows” = Table.SelectRows(#”Removed Other Columns”, each Date.IsInPreviousNDays([PeriodStartDate], 60))
in
#”Filtered Rows”

Timesheet Periods (Renamed to Timesheet Analytics)

This becomes the primary dataset for user facing visualizations. Everything is predicated on timesheet period so it makes sense to orchestrate the rest of the data around this data.

let
Source = OData.Feed(“https://YOURSERVERNAME.sharepoint.com/sites/YOURPROJECTWEBAPP/_api/ProjectData”),
TimesheetPeriods_table = Source{[Name=”TimesheetPeriods”,Signature=”table”]}[Data],

// Filter records for last 60 days
#”Filter for last 60 days” = Table.SelectRows(TimesheetPeriods_table, each Date.IsInPreviousNDays([StartDate], 60)),

// Cross Join of Timesheet Period and Timesheet Eligible Resources

// Creates a master list of all expected timesheets
#”Added Resource for every Period” = Table.AddColumn(#”Filter for last 60 days”, “AllResources”, each #”Resources”),

// Show all relevant resource fields
#”Expand to show Resource Fields” = Table.ExpandTableColumn(#”Added Resource for every Period”, “AllResources”, {“ResourceId”, “ResourceCreatedDate”, “ResourceEmailAddress”, “ResourceName”, “TeamName”, “ResourceDepartments”}, {“ResourceId”, “ResourceCreatedDate”, “ResourceEmailAddress”, “ResourceName”, “TeamName”, “ResourceDepartments”}),

// Left join to match submitted timesheets with

// master list of expected timesheets
#”Merge with TimesheetLines” = Table.NestedJoin(#”Expand to show Resource Fields”,{“PeriodId”, “ResourceId”},TimesheetLines,{“TimesheetPeriodId”, “TimesheetOwnerId”},”NewColumn”,JoinKind.LeftOuter),

// Show the timesheet status
#”Show Timesheet Status” = Table.ExpandTableColumn(#”Merge with TimesheetLines”, “NewColumn”, {“ActualOvertimeWorkBillable”, “ActualOvertimeWorkNonBillable”, “ActualWorkBillable”, “ActualWorkNonBillable”, “PlannedWork”, “ProjectId”, “ProjectName”, “TimesheetApproverResourceId”, “TimesheetApproverResourceName”, “TimesheetStatus”}, {“ActualOvertimeWorkBillable”, “ActualOvertimeWorkNonBillable”, “ActualWorkBillable”, “ActualWorkNonBillable”, “PlannedWork”, “ProjectId”, “ProjectName”, “TimesheetApproverResourceId”, “TimesheetApproverResourceName”, “TimesheetStatus”}),

// If timesheet status is null, there’s no matching timesheet lines

// Therefore, the timesheet must not have been created
#”Replace Timesheet Status null with Not Created” = Table.ReplaceValue(#”Show Timesheet Status”,null,”Not Created”,Replacer.ReplaceValue,{“TimesheetStatus”}),

// Join with resources that were working during this time
#”Merge with ResourceTimephasedData” = Table.NestedJoin(#”Replace Timesheet Status null with Not Created”,{“ResourceId”},ResourceTimephasedDataSet,{“ResourceId”},”NewColumn”,JoinKind.Inner),

// Per requirement, combine all actuals columns into one
#”Total up all actuals” = Table.AddColumn(#”Merge with ResourceTimephasedData”, “Time”, each [ActualOvertimeWorkBillable]+[ActualOvertimeWorkNonBillable]+[ActualWorkBillable]+[ActualWorkNonBillable]),

// For missing timesheets, replace nulls with zero
#”Replace Time nulls with zeros” = Table.ReplaceValue(#”Total up all actuals”,null,0,Replacer.ReplaceValue,{“Time”}),

// Rename columns to be aligned with user terminology

#”Renamed Columns” = Table.RenameColumns(#”Replace Time nulls with zeros”,{{“Description”, “Period Status”}, {“EndDate”, “End”}, {“PeriodName”, “Period”}, {“StartDate”, “Start”}, {“ResourceEmailAddress”, “Email”}, {“ResourceName”, “Name”}, {“TeamName”, “Team”}, {“ResourceDepartments”, “Department”}, {“PlannedWork”, “Planned”}, {“ProjectName”, “Project”}, {“TimesheetApproverResourceName”, “Manager”}, {“TimesheetStatus”, “Status”}}),

// Narrow down data set to only those field required

#”Removed Other Columns” = Table.SelectColumns(#”Renamed Columns”,{“Period Status”, “End”, “Period”, “Start”, “Email”, “Name”, “Team”, “Department”, “Planned”, “Project”, “Manager”, “Status”, “Time”}),

// Change dates to only show date, not datetime

#”Changed dates to only dates” = Table.TransformColumnTypes(#”Removed Other Columns”,{{“End”, type date}, {“Start”, type date}}),

// Add an unique index that will provide additional

// functionality in Q&A

#”Added Index” = Table.AddIndexColumn(#”Changed dates to only dates”, “Index”, 1, 1),

// Rename the index to use the People term

#”Renamed Index for Q and A support” = Table.RenameColumns(#”Added Index”,{{“Index”, “People”}})

in

#”Renamed Index for Q and A support”

Want More? Let us teach you Power BI.