Power BI and Microsoft Project – Getting the Data!
By: Collin Quiring
As seen on MPUG (Microsoft Project Users Group) at this link: https://www.mpug.com/articles/power-bi-got-you-puzzled/
Using Microsoft Project Online or Project Server is commonplace for many Project Managers and report writers alike. Of course, there are reports online which provide project or portfolio views. There are also useful reports in Project Professional, showing a single project. However, these are difficult to use when trying to combine other data sources or when doing heavy customization. It seems that every organization wants to see their data displayed in a specific, unique way. Furthermore, it is important for a Project Manager to understand the progress and details from their project, even if they are required to understand costing or accounting that might not be in Project Online.
This is where Power BI comes in! When used correctly, it is the report writer’s best friend. With Power BI, information from other data sources can be combined. If accounting or costing numbers are maintained in another system, putting that information together with project details in a single report is incredibly valuable and time-saving. No longer is the report writer having to write one report for their Project Online data and then a totally separate report from a separate data source. Power BI lets them combine the data together to give very powerful information about the Project and other meaningful attributes from other systems. In fact, we can create visual representations of our reports that simply cannot be done using other tools.
In my upcoming webinar on the topic, I plan to show one or two of the standard Power BI visual reports for Project, however, the presentation is going to primarily focus on the steps to take BEFORE the visuals. We will cover HOW we get to the relevant information.
- What is the string that we use in Power BI to actually get the data from our Project Online database?
- What are the tables that are available to us?
- How do those tables link together?
- How do we link other information sources into this data?
The presentation will also briefly cover how to clean some of that data up so that it makes more sense for our needs—particularly with regard to duration and time elements.
One of the first challenges that every report writer is faced with is to get to the original data to make a report. The second challenge is to understand how that data’s tables relate to each other and how to produce a meaningful report. And then, the writer needs to understand the formatting of the data and how they may want to modify it to meet their business needs. This means that the writer can modify that data and put it together in a way that makes sense to them, and, even more importantly, to the folks that are going to be viewing those reports. In some cases, there are canned reports pre-built for this, and report writers can occasionally backwards engineer parts and pieces, but that doesn’t always work out either. Microsoft has a couple of those pre-built reports available for users and report writers alike because Microsoft understands that not everybody knows where to get the project data or how to link it together.
As good as these reports are, inevitably each unique organization will want to modify them in some way. If the report writer understands the method of obtaining the Project Online data and how to manipulate that dataset to best match their needs, they can provide extremely helpful visual reports for users and management alike. Linking Project Online data to their own data sources, they can build even more holistic reports.
I look forward to sharing more, so join me for my webinar, How to get and see Project Information in Power BI. It’s eligible for 1 PMI® PDU in the Technical category of the Talent Triangle.