In projects, information is everything. Microsoft Project is the number one project management system based on popularity. It is a powerful, all-inclusive system with robust scheduling, resource management, and extensive time-phased data. These features make it a popular choice for many companies building their project and program management capabilities.
In addition to these features, Project also provides a load of data that makes it a perfect candidate for Power BI data analysis. Power BI is a powerful analytics tool with deep capabilities to bring your organization’s data to life and provide insight into your operations. One of the great capabilities of this tool is its ability to easily load data from a myriad of sources and merge them all seamlessly.
With over 120+ connectors, Power BI has you covered with most data connections you will encounter, both native connectors and cloud data sources alike, including connections into Microsoft Project Data.
Combining Project data with Power BI
Imagine being able to see your projects and portfolio data come to life with charts, graphs, and other visuals based on current project data from your PMO. Combining Project data with Power BI gives you the ability to tell a story to both management and teams alike. Power BI provides graphical information to unlock insights to help manage projects, portfolios, and resources.
This brings us to a question we get asked a lot at Advisicon. How do I load Microsoft Project data into Power BI?
Getting started
Let’s start with the data connector that Microsoft Project uses to expose its data for use: the OData feed. This OData feed is the type of connection Power BI requires to connect to projects. OData is short for Open Data Protocol. OData is the best way to create and consume data APIs.
The OData feed is used by both an on-premise Microsoft installation of Project and a cloud-based installation. The OData feed allows the user to mine the data from the projects via predefined out-of-the-box tables and fields that make up the Microsoft project tool and Enterprise Custom Fields that may have been created specifically for your installation.
Connect to your OData source
The following steps will guide you to connect to your OData source successfully.
Step one
Within Power BI, select Get Data from the Ribbon, then select OData Feed.
Step two
Enter your site URL and add “_api/ProjectData”. This will be needed to connect to any PWA pages within Project Online.
Example:
https://<tenantname>.sharepoint.com/sites/pwa/_api/Projectdata.
Click OK.
You will be prompted to authenticate your account.
Generally, when authenticating your connection to projects, you will use your organizational Microsoft 365 account. However, there are instances when you can sign in using windows authentication when working inside a remote/VPN connection or within your organization’s network.
Once authenticated, Power BI will give you the option to navigate to the tables you need within projects.
Step three
Select the tables you need and load the tables.
Step four
Click on transform data, and you will be able to see your data.
Reporting options
The process is very straightforward, and when you first pull up the data, you will see all the tables available. This is where the real fun begins because you have just opened up the possibilities available for your reporting.
The tables you choose will populate the fields you can use to start your data analysis. Generally, we will load the Advisicon standard Power BI reports pack for most organizations we work with. This starting point provides a set of reports that an organization can use for their entire portfolio as well as project and resource level views. This usually opens up the imaginations of the report consumers, and they are soon looking to add to and modify the views to fit their organization’s needs and hot buttons.
Since Power BI is such a powerful tool, you can add tables and fields that need to be reported on or remove ones that are not required to allow faster and more streamlined loads. An important piece to understand with the project data is the interrelationships of the tables and the data modeling that connects the tables. Having a report pack to use helps with this because the connections are already modeled.
Unlock your organizational insights
As an organization matures in its project management methodologies, the project data driving it becomes key to making effective management decisions. If used correctly, Power BI often becomes the lens that shows the story and highlights opportunities.
The project OData feed is a great start, and data from other areas of an organization can be aggregated with this as needed. Often, an organization will want to see their cost data from a separate financial system brought into these same reports, and Power BI can do so. Other data as well can be added to enhance this based on an organization’s needs.
At Advisicon, we see all sorts of data from different sources being brought together and merged with Project data. This gives a complete picture of the inner workings of organizations using the Power BI platform.
If you want to check out some key Power BI reports that support Project Online, watch this webinar:
Begin utilizing your Project data
Now that you have your project data loaded into Power BI take the opportunity to play around with the reports and visuals you can create. I like to start simple with a goal to create a visual to answer a specific question. For example, “which projects are managed by which project managers?” Then I start playing around with how to make that happen. Once that has been conquered, move to the next question. This will give you a feel for the data and how the fields and tables relate.
Let us know your experiences with the OData feed and connecting your data to Power BI. Or, if you need help customizing your report packs, you can contact us here. We love helping people with their project management journey and the technologies driving organizations.
