Create a PowerBI Resource Analysis Dashboard – by Darrin Kinney

Feel free to provide comments directly to my LinkedIn Post that references this article and contact me directly – Darrin Kinney

Analyzing  resources in P6 is a common responsibility of all planners. However, ultimately the data typically stays in the realm of the planner, and never properly given to the specific project managers and project engineers who actually need to execute the work. Thus, there exist a huge opportunity in the digital world to extract data from P6, and present it to the masses. Planners are penultimate professionals in this. We live for this. We take the resource assignment data from P6 and work our magic in excel creating a suite of reports and graphs (s-curves).

However, all the excel work is customized. The ability to quickly drill into the data from the data table or the graph is just too difficult for management. The need to create custom graphs, takes time. Instead, we can create something quite amazing using PowerBI.

Using PowerBI we can create a simply easy to use dashboard that provide nearly unlimited flexibility to display both schedule and resource information to any user on a project. The below is a walk through to create a simple view with a simple schedule. However, I have run this through a schedule with 5,000 activities and multiple resources including both manhour resources and quantity resources. It is situations where you have data overload that PowerBI shines. Get the data out from the planner, and into the hands of the project management group.

Step 1 – Get resource assignment data

Using the resource assignment tab in P6, remove all the grouping and just display everything. You will want to ensure some key fields are available: Start Data, End Data, Resource Data, Resource ID and Type, Activity Name and ID, plus some WBS and grouping data (ex contract).

RA_2_P6_resources

Step 2 – Copy-Paste Resource Assignment Data into Excel

A simply copy-paste from P6 into excel will suffice. It is really that easy. Sometimes the data from P6 may not contain the right descriptions, so this is an opportunity to use some vlookups (or Index(Match()). The data also needs to be presented in a table. Select All and use CNTL-T to convert to a table.

RA_3_excel_table

Step 3 – Unpivot Data using Power Query

This step can be done directly inside PowerBI Desktop, or in perhaps a more flexible excel environment. I prefer to do as much data handling in excel to reduce the complications once inside PowerBI; however, many options exist.

Inside Excel (ensure you have power query add in), select the columns with the dates, and click “UnPinvot Columns”. Close and Exit.

After running the unpivot, you will see each data column turned into a row. This is an easier data format for use in databases and a trick universal not just for powerBI, but anytime you might be dealing with databases.

RA_5_powerq

Step 4 – Import Into PowerBI

PowerBI has very quick and easy import routines. This post is not meant to be a specific click by click guide. There are easy import routines you can find to import excel files. Again, as indicated above, just make sure your data is converted into a table. You can directly create a datasource using something similar to the below

=Excel.Workbook(File.Contents(“C:\Users\Name\Downloads\P6_resource_Assignments_HP.xlsx”), null, true)

Step 5 – Create your PowerBI Elements : Gantt, Graph, and Slicers

GANTT

The PowerBI file uses a custom visual you can download for free from the marketplace

GANTT by MAQ Software

The parameters you will want to use are roughly as noted below

GRAPH

The PowerBI file use AREA CHART. This is an easy to use built in graph feature of PowerBI. You can use potential extension of line graphs to show a % Complete curve. However, from a base usage, the area chart provides the easiest visual.

A nice trick in the overall dashboard is to align the data range of the GANTT with the GRAPH. I have found some limitations to this exist, but in this example, it worked quite nicely.

 

SLICER

The real power of this visual is through the use of slicers. Slicers allow for immediate filtering based on a variety of selections. This visuals uses a range of filters for Contract, Facility, SubFacility and ResourceID. Depending on the structure and data you extracted from the P6 resource assignment, your options here are unlimited.

 

Conclusion

This example just scratches the surface of what is possible. I found the above to be immediately useful to our project team in clearly visualizing the resources required over time. There is some manual effort to keep the source excel updated after changed to P6 – this is not a live linked datasource. There are numerous possible development extension people can use to customize this.

The biggest critique to the dashboard is the lack of visibility into the Baseline dates and plan curves. For this, we can only hope someone builds a more robust custom visual to accommodate this. In the end, happy planning.

RA_1_Final

Advertisements

Float Erosion report between two XER

I just added a new report in the Xer reader to show the float Erosion Report between two XER, I am using the “Harbour Pointe Assisted Living Center” from Primavera P6 default installation as example.

1- Download the pbix file here

2- Save a two XER in this folder C:\XER

2

3- Refresh PowerBI Desktop

3

4- Review the report, and export for Excel for sharing/Further analysis.

4

5- if you have PowerBI service in your company then publish it there, so other users can interact with the reports.

let me know if you want to see other reports.

 

 

Rewriting Xer Reader using PowerBI

4 years ago, we had to customize the xer parser for a client, that work end up as Xer Reader, turn out the file was very popular, but unfortunately, as it was written in VBA, users get all kind of different errors, and I was not able to provide a proper support.

now I am trying to rewrite the same  Excel Macro but now using PowerBI, the file is hosted in github

in order to open a pbix file, you need PowerBI Desktop, it is a free download

I think it will be an interesting experience, some stuff are much easier using VBA, and there is no equivalent in PowerBI, I will try to document the progress.

in the main time here is my progress so far

stay tuned

Integrated Project Control system using PowerBI

One of the most popular discussion in planning forum is how to have an integrated project control system, every practitioner has a different opinion how it must be done, and of course you get a lot of marketing from people trying to sells their systems

In this blog, we share high level description of a data warehouse built using PowerBI desktop.

Data warehouse are not widespread in the construction industry, because the reporting specifications are different from project to project, and every client has a different systems and tools, and trying to have a standard system works only if you are the main contractor but if you are a subcontractor you have to adopt the client system.

Another reason is; it required a specialised IT skills, we are just business users not programmers, we do understand data very well, but not necessarily having the skills and tools to manage it, The good news is, with the rise of self-service Business intelligence, we have exactly that, Powerful data management tools yet accessible (assuming you want to learn something else than Excel).

So at high level this is how it works.

 Project Controls Data Warehouse

  • The Data warehouse was built using PowerBI desktop, I know it should be called sematic model, (for me data model, data warehouse are fundamentally the same thing), initially it was using Excel PowerPivot but it did not scale well with the increase of the volume of data.
  • As the data is not always in the format we want, PowerQuery is very handy in this case, as virtually it can transform any source of data, example lookup the subsystem using the tag field, trying to do that if you have 8 Million rows using Excel or Access is not feasible.
  • We maintain Master tables to integrate all the different source of data (tags, WBS, subsystems etc)
  • Every week, we get new Export from the source systems (Cobra, proprietary database systems etc), we load the new data and keep the historical records, it took 15 minute to refresh, which is quite impressive, Cobra alone is a folder 60 Excel file, and nearly 2 Giga in size.
  • Usually you publish your reports into PowerBI.com service to end users once your refresh your data model, in our case we can’t use the cloud for privacy reason, instead we use Excel as a reporting tool that pull the data from PowerBI desktop, the advantage of this approach is that we have different reports for different users, Skyline, Gantt chart, Client reports (in their required format), management reports etc.

Some thoughts.

  • As you can see Primavera P6 is used only as a forecasting tool and to calculate the critical path, the earned value calculation is done in the data model, personally I think P6 should not be used as the centre of your project control system, I remember the first time I start learning Primavera P3 ( a long time ago:), we kept asking the trainer how it is possible to track the spent hours at the activity level, the answer is we don’t, actually deciding at which level you track you spent hours it is the most important decision to make when you start a new project.
  • the basic idea here is in order to have an integrated project controls system is stop trying to have one, data will be always in silo, don’t try to change other department how they manage their specific data, it will not work and they will not listen to you anyway, So instead of trying to have one system to rule them all, just use the existing systems and build a data warehouse for reporting and Integration with P6.

Mapping Primavera P6

If you work for the main contractor or doing an EPC contract, then there is already a well-established project controls systems and procedures applied enterprise wide, all the projects use the same approach and methodology, all you have to do is just learn it and fellow the procedures.

Now when you work for a subcontractor it become tricky, although there are already systems and procedure in place, you need to fellow the main contractor specifications.

In this blog, I will share a generic approach that is agnostic to the client’s tools, it is not a step by step tutorial as every client has a different systems and terminology, but I hope you can adapted it to your particular case.

First you can implement the mapping system in any database systems you are familiar with, you can use MS access, SQL server or any open source database,

I will use Cobra as the progress measurement systems and Primavera as the planning tool, this is only an example, any other combination will works, as we exchange data using Excel and we use activity codes as a reference,  all the planning tools support some kind of activity codes.

Why do we need mapping in the first place?

Updating a schedule is a time consuming task!! And it is prone to errors and personal judgement of the planners involved,  generally  the update process involve two steps.

  • Updating the facts ( Actual Start, Actual Finish, Earned Hours , remaining duration)
  • Review the update and analyse the results (critical Path, Forecast Curve) and check for any discrepancies, and thinking of mitigation plans if there are slippage.

If step 1 is done manually then obviously the planner will have less time for step 2, which I think is the most important Task.

I think a better approach is to make step 1 fully automated and let the planners do step 2 which cannot be automated (unless computer become self-aware).

 

How to do the mapping then.

  • Manual, most used approach, the planner open the progress spreadsheet and manually copy the hours and the new status, either by filtering or a pivot, or on ad hoc basis (nice word to say guess work).

 

  • Primavera activity ID is manually assigned to cobra, Cobra then will make an export with the new status grouped by activity id, the disadvantage of this approach is, it is hard to maintain when the take-off change which is natural fact of life in construction projects and there is no transparency why the mapping was done in such way, still it is way much better than the first one.

 

  • Dynamic mapping: instead of manually assigning activity id in cobra, we define a new primary key in P6 that based on fixed rules will automatically generated link to Cobra.

How this dynamic mapping works:

Capture

We don’t use Primavera activity id, instead we define a new natural key, let’s call it P6_PK, this key define the exact scope of work of one activity, in this example we have two type of activities:

We can use this P6_PK as the activity id, but if you have already a baseline, you can’t touch the activity id and sometimes the default length of the activity is too short.

Let’s define some terms:

Activity Level 3: P6_PK  is  Area|P6 price code

Activity Level 4: P6_PK  is  Group|P6 price code

– Group: a group of tags that have common characteristic: by subsystems, major package, cables grouped by destination, or origin or whatever the schedule is grouped by. Or simply how you think the work will be done.

– P6 price code: the MTO has a very detailed breakdown for the price, obviously we can’t use them otherwise we end up with a massive schedule, instead we group the code to a manageable level.

In P6 (or MS Project or whatever you are using) define new codes, Area and P6 price code.

Create two tables.

P6 price code: define how you group your MTO Price code to P6 price code

Group:    define how you want to group your tag.

The Database will look up the group from the group table and P6 price code from your P6 Price code, then generate P6_Pk from that then we get the activity id.

Obviously I just outlined the fundamental concept, in real project, it is much complex, P6 price code may have different level of details in the same schedule, and some tags may have different groups based on the type of activities, we may keep excavation at the level 3 but pouring concrete for foundations at level 4.  (Hint you can new dimensions to your dictionary tables)

Please notice I use Level 3/4 just for the purpose of mapping, it is not to reflect the level of the schedule which is an open question. (Two planners will tell you different thing)

 

The output of the system:

  • The new status of the activities. ( import back to P6)
  • The new scope that are not mapped.( update the new groups and price code eventually)
  • The activities that used be mapped but now lost the scope ( statue them as completed, as there is no more scope)

The time to update P6 was significantly reduced (by order of magnitude), the system was implemented using PowerPivot/Power Query as filtering and manipulating huge list of data is more convenient in Excel.

 

The challenge

The biggest challenge was how to combined all the weekly export from cobra in Excel and combined them in one table and add other data from other systems to get a format required for the mapping, if you are an SQL ninja it is doable but if you are a planner what you do, there was no easy answer before, but now we have it, Build a data-warehouse in PowerBI, but that’s a blog for another day.

How to connect to Primavera SQLite database

if you have a standalone installation of Primavera using SQLite, and you want to see the data stored there, either for reporting or just by curiosity to study the schema, it is pretty easy to do that using Powerquery.

unfortunately there is no native connectors to SQLite but you can use ODBC, please fellow this tutorial how to download and install the drivers ( sorry you need admin right, that’s why you should be always nice with the IT).

we don’t use standalone installation at work, I just download one installation from Oracle site in my home computer, the installation is valid for 1 month, once it is installed i copied the SQLite file generated by Primavera with all the template Projects.

the connection string is like this

Source = Odbc.DataSource(“driver={SQLite3 ODBC Driver};database=C:\sqlite\PPMDBSQLite.db;longnames=0;timeout=1000;notxn=0;dsn=SQLite Datasource”, [HierarchicalNavigation=true])

use the same password and username used for installation

don’t query directly the file, but use a copy, there is a risk that you corrupt your file.

Capture

keep in mind the tables in Primavera are highly normalized, and the spread data are not stored ( for example forecast labor units per day/task), so depending on your reporting requirements it can be a non trivial task.

anyway My PowerBI template now use either xer or sqlite to generate reports.

Using PowerQuery to read Primavera Xer file

You can download the file here  or view the reports online

The last time I tried parsing an Xer file, I was using VBA and it was a horrible experience, anyway that was three years ago, The problem was not VBA per se, but supporting the different combination of Excel and Windows, I still receive sometimes emails from users complaining that Xer reader do not work for them, after a while I concluded it is not worth the effort.

Yesterday for no reason, I got this idea why not trying Powerquery , in 10 minutes I had a working prototype without writing a single line of code!!!  even better it reads in bulk, copy a bunch of xer in the same folders, and there are loaded, only conditions they needs to have the same revisions( don’t mix P6 rev 7 with rev 8), as different revisions of P6 introduce some minor changes in the Xer schema, ( they add or remove some columns)

It is only a proof of concepts, I only loaded some tables, (activities and activities code), the data is loaded in the Data Model, and you need at least Excel 2013 with the free PowerQuery Installed.

1

Make sure you write the path to where your Xer is stored

2

You need to refresh the Queries here

6-6

Here is a pivot with some measures, now you can group only by Project and Activity codes and obviously by activity id.

6-3

I test it with two Xer that contains 47 projects