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

Some content in this post is perhaps superseded by PowerBI Progress & Schedule Dashboard.

Getting the right structure for what a PowerBI dashboard can do in relationship to construction projects is not a 1 sized fit all approach, so really this post is meant to just focus on some of the data elements, which again have been perhaps better presented in the new link above

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

Using Or Conditions between Slicers to filter Primavera Schedule using DAX

The Interactive report is published here,  you can download the PBIX here.

In PowerBI or Tableau or any BI solution by defaults when you have multiple slicers, the filter conditions is always, AND, in this particular case, the user want to filter a schedule based on a start date  OR the end date OR the activity status, basically how to reproduce the following Primavera Filter using DAX

Primavera

 

 

Using the normal slicers will not work as it will simply show the activities where all the conditions met, instead we will use some DAX techniques (disconnected slicers and filter using the result of a measure) to get a new behavior.

 

Let’s build a simple data mode.

1-The main table is task: it contains Activity ID, Activity Status, Start, Finish

2- add a date Table, Start_Date, don’t link it to any table, use it in a slicer , the table is generated using Powerquery,

3- add another Date Table, Mstdate, don’t link it either, use it in a slicer

model

 

4- add Activity status Table, don’t link it, , use it in a slicer

5- add this measure

Filter_measure = CALCULATE(COUNTROWS(task),

FILTER(task, ([Start] <= LASTDATE(Start_Date[Date])

&& [Start]>= FIRSTDATE(Start_Date[Date]))

||

([finish] <= LASTDATE(Finish_Date[Date])

&& [finish]>= FIRSTDATE(Finish_Date[Date]))

||

IF(NOT(ISFILTERED(‘Activity status'[Activity status])),BLANK(),’task'[Activity status]IN ALLSELECTED(‘Activity Status'[Activity Status]))))

 

Add this measure to the matrix visual as a filter only, and make it not equal null, and voila

result

Load polygons geometry into PowerBI data model

Starting a new project that involve plotting a relatively big map ( 3000 polygons), it should be straightforward, as I have done it before, but a limitation in my prefered PowerBI custom visuals had an interesting result.

1-  synoptic panel: in my view it is the best visual to show custom map, ( floor plan, general layout etc), unfortunately not this time, my new map has 3000 polygons, when I tried to plot some attributes, I get the equivalent of Windows screen of death for PowerBI, too many values !!!

1

That’s bad, I filled a bug report, the author was kind enough to reply that it will be fixed in a next release, (something to do with incremental loading) fine let’s try other options

2- Shape map : (it did not even render the polygons correctly, and to be honest the visual did not receive any update since the first time I use it, 2 years ago)

3- Mapbox: showstopper, to load your own shape file and keep it private, you need a business plan.

I am stuck, I can’t plot a 3000 polygons map in PowerBI, let’s try Rstats.

4-Leaflet: is very versatile mapping engine, right now I use it in other projects (outside of PowerBI) and it plot 60K points and nearly 500 polygons in sub 2 seconds, but there is a problem, Leaflet output is HTML which is not supported under PowerBI, I never manage to make decent screenshot, something to do with the zoom.

5-ggplot2 + SF : quick google and got this code

library(sf)

library(tidyverse)

map_shp <- read_sf(‘C:/Users/mimoune.djouallah/test.geojson’) map =dataset %>%

left_join(map_shp, ., by = c(‘id’ = ‘id’)) %>%

ggplot() +

geom_sf(aes(fill = type),lwd = 0)

map

2

Great, I have my map now ( this is only a portion, I can show the whole layout as it is proprietary), there is only a little problem, code take 25 seconds to render, it is very annoying, especially as in PowerBI you touch anything, and all the visuals render again.

my first thought maybe the join between PowerBI dataset ( dataframe) and the geojson is slow !!! the beauty of R integration in PowerBI is: you click on R icon, a new window open with an empty dataset, then you add the columns you want to analyse, yes that simple no ETL, no messing with data, you have to the mighty data model.

Loading Geometry data into PowerBI data model ?

 

After reading the SF documentation, I learned all I need is a dataframe  with a column that store geometry data, too easy just use QGIS , save as csv and make sure to select geometry, WKT

Capture

Now I loaded the csv file to the data model and join it with the attribute

3

Now here is the new code, notice, we don’t load any external file, all data is from the data model

library(sf)

library(tidyverse)

map_file= st_as_sf(dataset, wkt = “geometry”)

map = ggplot(map_file) +

geom_sf(aes(fill = type),lwd = 0)

map

basically, you need just to tell SF which column represent the geometry , unfortunately it is still slow maybe now it is 24 seconds

Another round of googling and I find the issue ggplot2 is very slow to render, no problem, this is R after all, there are a least a couple dozen of packages that do the same thing 🙂

6-Pure SF

 Turn out SF is not only to manage Geometric dataframe but it can plot too,( who would thought that)

library(sf)

map = plot(st_as_sf(dataset, wkt =geometry”)[“type”])

map

 render in less than 2 seconds, happy dance  

 

Bonus point, Cross-filing geometry

 My purpose to load geometry was just to speed ggplot2, but hold on, the geometry is in the data model, not only the attribute change ( install quantities, category etc), I can even filter polygons on the fly !!!, this is quite amazing, I can load all the layers and filter out any shape as I want.

4

HTML map using R, Part 1

I always tried to learn R, but I never succeed, I did not like it, after enjoying the GUI of PowerQuery, using R was like a self-inflicted torture. But obviously I was wrong as usual, R is not only for data cleaning.

in a PowerBI usergroup , I heard someone talking about R, and I made a mistake of telling him what R can do that PowerBI can not do ?, all I can say is I wish if I did not ask him this question, anyway, in defending R, he said something about generating HTML file and boom, that’s exactly what I was looking for, a file that anyone can read and it has interactivity and it works offline.

my problem is, I build very nice interactive map visualization, but for large distribution, I just print it to pdf, which obviously fail the purpose, not everyone has access to PowerBI service, and for external parties ( client, subcontractor), you can’t reasonably expect them to subscribe to PowerBI service just to see a map :), using Excel 2016 was an option but unfortunately some functionalities are still missing (can’t show a label) and honestly the integration of 3D map with Excel looks like it was a second thought by the developers.

The good news is, R has an excellent integration of PowerBI, so instead of recreating the wheel, just use R for the stuff that can not be done natively in PowerBI.

I hope to publish multiple blogs to document my workflow.

In my real case scenario which I can not publish for obvious reason, we need to view the status (Completed, or not) of nearly 50,000 Piles, show all the piles will clutter the view, instead, I found by accident that a library called Leaflet (the Engine used by MapBox), has the ability of showing layer only when you zoom.

I will use a dummy data, (literally a foundation on a mountain in a New Zealand as an example),

Capture

the data source is an excel file with the pile location and topojson file to show the foundations location all generated using the Excellent QGIS.
the R code is

library(readxl)
library(leaflet)
library(htmlwidgets)
#read Excel Data
data <- read_excel("pile.xlsx", col_types = c("text","text", "numeric", "numeric"))
#read json
map_data <- geojsonio::geojson_read("foundation.json",what = "sp")
######################################
map <-leaflet(map_data) %>%
addTiles(urlTemplate = "https://mts1.google.com/vt/lyrs=s&hl=en&src=app&x={x}&y={y}&z={z}&s=G", attribution = 'Google') %>%
addPolygons()%>%
addCircleMarkers(lng = data$x, lat = data$y,radius = 3)
map
saveWidget(map,file="foundation.html",selfcontained=TRUE,title = "foundation")

the result is a self contained html, at the moment, it is show only 2 foundations and some piles, in the next blog post, we will add zoom, and how to filter layers, and show labels and stuff.

the result is here, and the source data is located in this github repository.