Use metadata to drive Microsoft Project reporting logic

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.

1. Create a metadata value lookup table
2. Create a task custom field (you may need more than one eventually, but start simple)
3. 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.