In this article, I want to zoom in on a Power BI aspect that is very useful for any organization with a large amount of data sources.
The Case
Consider a situation where you run multiple projects using Project Online. You already know that there’s a great connection between Project Online and Power BI. If you aren’t sure of that fact, you can read up on the basics by referring multiple articles on MPUG and/or my blog.
Yes, the basics of using Power BI are valuable, but there is a lot more to it than has been covered in the links above. You can, for instance, create connections between sources.
Let’s assume your organization has a financial datasource (SAP or another financial application). In that system, you store detailed budgetary and financial transactions for the projects that you run on Project Online.
There are good reasons to keep the financial system and the Project Online project management system separate. One of these is that Project Online doesn’t support advanced financials out of the box. Another reason could be data consistency. An organization might store more (not project related) financials in the financial system if it wants all monetary values to be located in that single source.
Perhaps you have the initial budget and some other high level estimates, but the bottom line is that you don’t have detailed financial information stored in Project Online. That’s fine, but also means that you can’t do full reporting on those projects based on only the Project Online dataset. This is where Power BI comes to the rescue!
The Linking Pin
Combining data sources is possible, as long as you have a value that is unique for each project/task/resource on both sources. Let’s look at them one at a time.
1. For Projects, two such values are the GUID (Global Unique Identifier) and the Project ID.
The GUID is truly unique, but also useless in any normal human communication. Just consider this conversation:
Person 1: I heard you were working on a new project. What is it’s GUID?
Person 2: Well, daf85b74-cf89-e811-80dc-00155d08491f.
Person 1: Very useful, thanks 😐.
Now consider using the Project ID. This is the automatically generated number for a new project that can be useful in a human conversation:
Person 1: I hear you are working on a new project. What is the Project ID?
Person 2: It is Proj-007.
Person 1: Thanks 😊
2. For tasks, the unique value is the WBS number. This code is also used on the SAP system making it easier to link the two systems together. You can find out more about WBS and what it is by referring to several MPUG articles or visiting my blog again.
3. For resources, the easiest unique identifiers would be employee numbers. This isn’t a default value in Project Online, but you can create a custom field for this with ease. Or you could use “Code,” which is an out of the box field.
The downside with this is that external resources don’t have employee numbers, but perhaps they do have a number in the system that you can use as a substitute.
Upside is that these numbers are almost always present in any financial system. So, you know that there’s a counterpart number easy to link to.
Combining Sources
Having a unique identifier on one end isn’t enough for Power BI. You need to have that same unique value on the same level of the project on the other side.
Let’s take a look at our example of SAP again. We are going for the Project ID to be the unique ID for any project. It makes sense to make the Project ID from Project Online the primary key and not the same number from SAP side. This is because Project Online is the source of projects and the Project ID should come from that source.
On the SAP side, we can create a custom attribute, which we will use to fill in the Project ID. Any time we create a SAP entity associated with a project, we want to have that attribute filled in (as long as there’s a Project Online counterpart).
Alternatively, it could be that a project exists on the SAP system first, and then it might be wise to have the SAP number as the Project ID. It is really up to you to decide which system is leading here. The key thing to keep in mind is that the value needs to be unique on both systems.
Now that this field is available in SAP we can extract it, together with the financial information we need, using a CSV or Excel files. Storing this file anywhere on SharePoint makes it easy to extract it in Power BI using the “SharePoint Folder” option, as shown below.
The folder option also allows for trend analysis. If you save the financial data at the same location each time, and with the same naming conventions, there’s good chance you can harness the data later. I would advice creating a double copy of the most recent file (one with a date in the name, and one called “Latest”).
This way you can focus on the file name “Latest” to extract the financial data.
The SharePoint folder input has a “Binary” value in the first column. If you hit the two arrows pointing downwards, you’ll be able to extract the underlying file. Such as our Excel file seen below.
In my example, the financial data was stored in a table ensuring that the column headers are correct.
Now we will need to combine the data with Project Online. Grab the data by using the OData string: <your PWA url>/_Api/ProjectData/Projects.
After filtering out some data, here’s what we get:
Please note here that the Project ID is actually called ProjectIdentifier on OData. Another thing to notice is that the Identifier on Project Online is a Text value, but on the Excel file it’s in number format. Let’s change the data type on the Excel version to make sure there’s no trouble there.
After clicking close and apply, you’re ready to link the data. Navigate to the relationships page. On this page, link the Project ID with the ProjectIdentifier as shown.
Make it a 1-1 cardinality with cross filter direction set to “Both.”
With a final touch, you can change the formatting of the financials.
Now when a report is created, you’ll be able to see the financials for Projects 100007, 8, and 0:
Note that the fields “ProjectName” and “ProjectPercentCompleted” come from Project Online (1) and the fields “Budget”, “Estimate at completion,” and “Actuals” come from the financial system (2).
Best Practice
As we close, I want to give one last piece of guidance here. Make sure that you are comparing apples and apples! It’s easy to get lost in the syntax and setup of the different systems. On Project Online, a project might be something completely different from that of a project according to the SAP standpoint. Make sure you have the correct data prepared before creating any reports.
Thank you for reading another article! Keep reading, keep learning!
