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.
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.
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 interactivity 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.
Using Tumble Road’s Conversation-Centric Design (CCD) approach for designing Business Intelligence solutions, the technical design details are derived iteratively. First, we model the targeted social interactions. Three key aspects are defined in this step: 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.
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.
Key Organizational characteristics
All timesheet eligible resources turn in a weekly timesheet even when they are not currently assigned to a project as the organization uses Administrative Time Categories to track time away.
The PMO is only concerned with total time charged to a project and only the standard time category is used.
The weekly timesheet periods run from Sunday to Saturday each week.
Timesheet periods are closed after 60 days
Jason and Sophie need the timesheet data for their own uses with Finance and HR but are passive observers to the review process.
The business intelligence visualization is needed to support the PMO weekly review of timesheet data at 9AM on Mondays.
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.
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 and Implementation
Now you seen the business need and requirements. To see how this is translated into a technical design and the final implementation of this reporting, read the full guide.