Category: Project Online

The need to extract Microsoft Project Task level data in an efficient manner is growing as many Project Server and Project Online clients are creating Power BI models over this data. Unfortunately, many did not account for this BI need when creating their project template structures. This leads to Project template designs that make it difficult or impossible to extract usable data from the Project Server/Online data store.

Microsoft Project Task names should not drive meaning outside of the project team

One common issue is making the task names in your project template meaningful to needs outside of the project team. You might have standard task names for Finance or for the PMO for example.

If you have told your PMs that they cannot rename or add tasks to their plans, you have this issue. You have encoded information into the structure of the project plan. The issue is that this way of encoding makes it very difficult to extract data easily using tools like SSRS and Power BI.

We’ve seen this before, when Content Management Systems were new

This was a common problem early on in file systems and SharePoint implementations in the 90s and 00s. A few of you may remember when we had to adhere to these arcane file naming conventions so that we could find the “right” file.

For example, you had to name your meeting notes document using a naming convention like the following. Client X – Meeting Notes – 20010405 – Online.doc. If you accidentally added a space or misspelled something, everything broke.

Metadata, a better approach

With the advent of search, we were able to separate the data from the metadata. This encoding of metadata into the file name data structure went by the wayside. Instead, we now use metadata to describe the file by tagging it with consistent keywords. Search uses the tags to locate the appropriate content. We also do this today for nearly all Internet related content in hopes that Google and Bing will find it.

If we reimagine Project Business Intelligence as a specialized form of search, you see that the metadata approach works to ensure the right information can be found without encoding data into the project plan structure. There are many benefits to using this approach.

Example: Phase 1 tasks encoding before

For example, today I might have the following situation, where the phase information is encoded into the structure.

Example: Phase 1 tasks encoding after

The metadata approach would yield the following structure instead.

Metadata benefits

The biggest benefit is agility. If your business needs change, you can your data tagging strategy quickly without requiring restructuring all of the projects. You can roll out a new tagging strategy and the PMs can re-tag their plans in less than a day.

Another benefit is consistency. Using Phase and TaskID, I can extract the Phase 1 tasks consistently from across multiple projects. This also has the side effect of reducing the PMO’s auditing effots.

You can better serve the collaboration needs of the project team while still meeting the demands of external parties. Project plans are simply the notes of the latest state of the conversation between members of the project team. It is intended for servicing their communication and collaboration needs. The PM is now free to structure the plan to serve the needs of their project team. They simply have to tag the tasks accordingly, which is a minimal effort. These tags can be used to denote external data elements such as billable milestones, phase end dates, etc.

Lastly, the plan structure makes better sense to the team and is easier for them to maintain. Top level tasks become the things that they are delivering instead of some abstract process step. The task roll-up provides the health of and progress toward a specific deliverable.

How do I implement project metadata in Microsoft Project?

It requires three steps in Project Server/Online.

Create a metadata value lookup table

Create a task custom field (you may need more than one eventually, but start simple)

Add this metadata field to your Gantt views for the PM to see and use

Note: Don’t use multi-value selection for this need as this creates complexities in the BI solution.

Below is an example of a lookup table created to support this metadata use. One use of it was to support a visualization of all implementation milestones for the next month across the portfolio. The query looked for all milestones with a Reporting Purpose equal to “Milestone.Implementation” to extract the appropriate milestones.

To create a task custom field and lookup table, please refer to this link for the details. Note, you can use the same approach in Microsoft Project desktop using Outline codes.

Metadata Lookup Table

The Reporting Purposes lookup table supports two levels of values. This enables multiple classes of tags, such as milestones and phases. This exercise focuses on the Milestone.Implementation value.

Metadata Custom Field

Create the Reporting Purpose task custom field and attach it to the Reporting Purposes lookup table. Specify that Only allow codes with no subordinate values is selected. This prevents the user from selecting Milestones without selecting a more specific purpose.

I hope you find this article useful. Please post questions and comments below.

Are you having tired of waiting for long refresh times with Power BI? Perhaps, you are getting timeouts because you are retrieving too much data. There’s three easy ways to avoid these issues.

WHY IS MY POWER BI DATA REFRESH SO SLOW?

Many Power BI models refresh slowly as the models are not structured to take advantage of query folding.

Query folding is a process by which specific Power BI transformations are executed by the data source instead of Power BI. Allowing those actions to occur at the source means less data has to be sent to Power BI. Less data means faster data refresh times and smaller report models. Note, not all data sources support query folding, but oData for Project Online and SQL Server for Project Server do.

A sample of these foldable actions include

Column or row filtering

Group by and aggregation

Joins

Numeric calculations

For example, you need to find out the amount of work scheduled by week for the quarter. You are querying the time phased data from Project Online. If you aren’t careful, you may be retrieving hundreds of thousands of records. Query folding will make a huge difference in speed and the number of records retrieved. If you have the records filtered by Project Online before retrieving them, you may only receive a few thousand records instead.

ISSUE #1: NOT REFERENCING THE DATA SOURCE PROPERLY

This issue occurs primarily using oData sources, such as Project Online, Office 36 and other web based sources. Query folding breaks if you don’t properly reference the oData feed.

In order for query folding to work properly, the transformations that have folding support need to be the first things executed. If a non-folding transformation is added, no subsequent transformation will be folded. If you don’t reference the oData feed properly, the step to navigate to the oData feed isn’t foldable, therefore blocking all subsequent transformations from being folded.

ISSUE #2: DOING DYNAMIC DATE SELECTION IMPROPERLY

This issue causes more headaches with Project’s time phased data than any other action. In many cases, you are looking to create a range of dates for Power BI to query. If you use the Between filter in the Power BI Query Editor, there’s not option for Today’s date +/- a number of days. If you use many of the other Date/Time filters, like Is Latest, you still seem to pull back a lot of data.

Solving this particular issue requires getting familiar with the M query language so that you can understand how Power BI Desktop performs specific actions.

For example, let’s look at the Is Latest date selection. By default, your dataset is unsorted so Power BI creates a list of the values in the selected column and does a Max value. While this is correct, this could result in a lot of records being retrieved to perform a Max and get one record. The M code over a time phased table looks like this:

To get much better performance, make the following two changes. First, sort the dataset in a descending manner on the column which you are using to decide latest. In this example, that’s TimeByDay. Sorting is a foldable action so the data source will do the work.

Next, change List.Max to List.First. Since the latest date is the first record in the sorted dataset, a lot less data is required to get the answer. So, my statement is now = Table.SelectRows(#”Filtered Rows”, let latest = List.First(#”Filtered Rows”[TimeByDay]) in each [TimeByDay] = latest)

In testing, the original way required over a 1 Mb of data to be retrieved to answer the question. The new way only retrieves 127 Kb.

ISSUE #3: URLS ARE TOO LONG

This issue comes into play when working with Project Online and SharePoint based data sources. Project, in particular, has a lot of long field names. SharePoint has a limit of 2048 characters for a URL. If you are manually selecting a lot of field names, you can accidentally go past the 2048 character limit.

What this means is that Power BI receives a URL that can’t be acted upon. The default Power BI behavior is to then simply retrieve the feed without modifiers. If this happens on a source with many rows, this could significantly impact performance.

In this case, you would break up your dataset into two or more to fit within the limitation and then merge them back together in Power BI.

WANT TO KNOW MORE?

Join us for a free Power BI performance clinic on March 22! This session will do a deep dive into these issues as well as show you how to determine when folding is happening (and not.) Go here to register!

Growing up, it always seemed like a pipe dream to be able to ask questions of the computer and have her answer. Microsoft has got us the closest to this dream with Cortana and Power BI Integration. You can now summon answers from your Power BI dashboards, with the power of your voice.

Attached is a very short demo and hopefully you can see this is just the beginning. I’m working on a more extensive demo and will be presenting this functionality next week at SharePointFest Chicago.

You can use the free PowerBI.com license with Microsoft Project Online to do your basic reporting. There are caveats but I’ll show you ways that you can use the free license effectively.

What is Power BI?

Microsoft Power BI is a collection of online and client tools that enable organizations to visualize and explore data, share insights and allow consumption of these insights from multiple platforms.

PowerBI.com is the home site for the service and serves as the portal to see your dashboards. Power BI supports three primary entities:

Dashboards – A tile based way of showing you important information in a glance-able and easy to use fashion. Dashboards also support natural language querying, where the viewer can type in a question and have Power BI build a new visualization automatically.

Reports – Are similar to dashboards in that they are tile based. However, reports are more interactive in that clicking on a bar in a chart filters everything on the report. A given Power BI project can have up to 10 reports.

Datasets – These are sets of data that have been transformed into business-usable formats.

Power BI is available on all mobile platforms via mobile applications in the platform app stores.

It’s Free?

Yes! Awesome isn’t it! When Microsoft last updated the Power BI pricing plans, they came out with a free license tier. To get your license, go to http://PowerBI.com and login with your Office 365 account. Note, you can sign up with other accounts but if you want to use this license with Project Online, you have to sign up with the account with which you access Project.

Now What?

You need to download and install Power BI Desktop to create and publish your BI content. Think of Power BI desktop as “Word for Reporting”. You put it all together in Desktop but this isn’t where you would typically consume your BI content.

The Approach

Assumptions

For this approach to work, three things have to be true

Your report consumer wants to consume the data but desires accessibility over the ability to customize the views

The total amount of data you need to make available is less than 1G

Automatic daily refresh of data is acceptable

The second point is important as the data stored counts against both the creator of the visualizations and the consumer. The last point is the norm for most Project clients as most information is updated weekly.

Tactics

Publish the Report Pack

If you aren’t already signed up for PowerBI.com, create your free account using the same account you access Project Online.

If you haven’t already done so, download and install Power BI Desktop,

Expand the Schedule Refresh section and set up when you wish the data refresh to happen

Click Apply

Share the Report

Content Packs, which is a way of sharing report packs that allows the report consumer to customize the reports to their liking. However, creating and consuming Organizational Content Packs is a Pro only feature. You can consume the Microsoft supplied content packs with the Free license.

This is where the requirement that the end user is a simple consumer comes into play. The free license level can share their dashboards and reports with other free users. It simply makes the dashboard read only. For those tiles that support drilll-through, you can still access the reports.

To Share

Select your dashboard in the Dashboards group on the left pane

In the upper right corner of the screen, select the Share button

Enter either individual emails or preferably, AD groups with which to share the dashboard. These email addresses have to be within your organization.

Click Share

By default, everyone who is on the share list will get an email. The Report consumer will now see the shared dashboard under their dashboard section when they log into PowerBI.com. If they are using any of the mobile PowerBI clients, they will see the new shared dashboard there as well.

Please note, if you change the dashboard, the people with which it is shared will see the updates immediately.

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

In this example, the period name follows this pattern: 2015-W01. Timesheet periods are 7 days long.

Year

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.

Identify and retrieve timesheet eligible resources using the following conditions

Work resources – Resource Type is 2

Who can log into the system – ResourceNTAccount is not null

The result of this step is the potential list of resources who can submit a timesheet

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.

Join the Resources with the Resource Capacity records to determine if a person worked for a specific week

Are capable of working in the analyzed time frame – Resource Base Capacity is greater than zero.

The result of this step is the actual list of resources expected to submit a timesheet for the selected timesheet periods

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.

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.

The result of this step is the master list of all timesheets expected for the selected timesheet periods.

Join the submitted timesheets with the master timesheet list from step 3.

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.

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.

// 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),

// 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.

// 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”}),

Want More? Let us teach you Power BI.

Thank you for the great response!

I created a video last week called the most interested time sheet audit report, mainly as a tongue-in-cheek way of introducing the cool functionality of Power BI over what is normally a dry reporting topic. The response was great! So great, that I’m putting together a hybrid class to teach people how to use the new Power BI.

For those about to crunch, we salute you.

Heroic Business Intelligence attendees should be those who really like to crunch the numbers. These may be Finance people, IT, Marketing or others. They don’t have to have deep technical skills. They also don’t have to be using Project as I’m not focusing strictly on Project for this class. They should have a need to analyze data for themselves and for others.

Hybrid > Traditional Classes

I’ve always hated it when I’ve crammed my brain full of new knowledge for a few days, only to lose 80% of it by the time I got back to work. So, I’m teaching this as a hybrid class. By that, the instruction will occur online live via a webcast with an interactive chat. All sessions will be recorded so if you can’t attend at the time, you can see the recording. You will also have access to the course material and any updates for as long as they are hosted. There will also be a private forum for attendees to share thoughts and ideas.

Peer Support

I attended a class structured like this last year. I still talk to my class peers nearly everyday. The network alone was worth the price of the class. The approach also allows you more time to absorb the content and apply it in your environment. I’m also hosting an Office Hour session each week, so that you can ask more detailed questions without having to do it during class.

Join Us Today, Registration Closes Aug 20th

I see this more as a journey and less as a class. If you want to join us, click here.

This two minute video provides a high level look at the resource management capabilities within Project Online and Project Server 2013. All content was created, using out of box functionality. The demo also shows the use of Team Resources as containers for assigned, but unscheduled work.