Three years to finish a Dashboard

in 2017, at my previous job, we were using PowerBI Desktop as our reporting solution, but there was a big limitation, we couldn’t use the service, so sharing the reports was either in Excel or pdf.

I remember I did try different solutions (Rstudio, Qlik, SSRS), they were great Products, but you need some kind of server to share the reports. At that time all I wanted is a simple web app where people can click on a slicer and get a fancy charts.

At that time Google made their reporting solution free, I was really excited about Data Studio, a free product, extremely easy to share but unfortunately a bit slow and lacked some basic functionality, I still managed to build something but it was not really good

It is all history now, moved to another job, we have PowerBI service ( and Tableau), but still for some reason, I felt like a missed opportunity, what if Data Studio became a good enough to be used as a free report tool.

If I remember correctly 2017 and 2018, there was no major progress but then they released custom viz, which basically means you can port any javacript library relatively easily , I managed to build a custom viz see example here

and in sept 2019, BI Engine showed up !!

It was really a big Deal, BI Engine is an analytics in-memory Database , and it is fast and they gave away 1 GB for free, it means you can connect your data from BigQuery and pay nothing ( with a fair limit of course), this made this report possible

In May 2020, they finally released Google Map Integration , although with a limit of 10K points, it was not useful for my use cases ( Solar farm needs a lot of point around 40k to 60K)

That was great and all, but still I couldn’t write complex measures easily ( or maybe did not know how), but something changed in August 2020

At last we have Proper support for parameter, that changed everything, now you can write any complex business logic using SQL in BigQuery and visualize the results using Google Data Studio, and you can do a lot of fancy stuff see those examples

Still there was still a major bug, Pivot table in Data Studio show 0 for null values needless to say, it is extremely annoying although you can build workaround, it was a hack and not sustainable.

That was fixed last week

So yes, it took me three years to finish this report, BI Engine + Parameter + Custom Viz and a bug fix in the Pivot Table to make this report possible

I added a workflow explanation in the report, but basically create a reporting dataset as large flat fact table and show the results in BigQuery with further control by SQL Parameter, if the native visual are not satisfying, you can show pretty much anything using Vega-lite custom viz.

One aspect was impossible to do without Parameter is the dynamic grouping of dates, in the time series, the weekend update dynamically based on the cut off selected.

Please don’t get me wrong, there is still a lot of work to be done, but the foundation of the product is already there, I can see clearly the vision of the product team, hopefully they keep investing but faster this time ( Parameter Action, support for BigQuery geography field, analytics functions, Tiles for Custom Viz ……)

Take away:

– If you need near real time reports

– You want a reporting solution and don’t have a decent budget.

– Used Data Studio in 2017 and dismiss it.

I have a good news for you, BigQuery/Data Studio is a viable option now, and you get 1 TB free for BigQuery and 1 GB compressed in -memory for BI Engine, that’s a lot of free resources, and there is no catch, you can share securely with anyone, again totally free.

Although I am a PowerBI developer and I love it, I think it is very healthy for the industry to have more choices, 2021 will be exciting !!!

NASA Apollo Cost Tracker

Quick how to guide on building my NASA cost tracker.

To follow up a recent video showcasing the NASA Apollo Costs, I wanted to illustrate how easy it is to use PowerBI to generate quick program of works dashboard. If you have several projects following a pipeline of work, some features here might spur some discussions or thoughts on what is possible.

The Data

I have sourced data from a google drive folder

NASA COSTS

However, like most data you find, the format is not suited to analytics. So a little manipulation was in order. Firstly, I had to create a WBS structure.  Typically, information we find is buried under headers, however for databases, we need to turn group headings into a column data field.

We can see I have inserted a 3 layer WBS structure, plus a company name field. This will allow me the flexibility to add subsequent data to this file from perhaps multiple companies, not just NASA. Again, when you build flexible data structures, the way you can use the structure is much more powerful

I know that I also want more contextual information displayed on the dashboard beyond simple data. Specifically, I want a description blurb to be viewable on a tool tip, along with a picture. Additionally, I want to display the leading contractor as well. Therefore, I added a few columns to the excel file. When you import the data into PowerBI, the URL needs to be set as a special format of “Image URL”. Took for some time to find that setting: its under “data category” on the column tools tab.

At some point, I will hopefully build out this dataset to include subsequent NASA budgets, and also publish this data through an API that everyone can access. However, there are limitation to what I can do and what I want to do typically far outstrips my abilities.

The Dashboard

Importing the data is quite straight forward, we do need our usual “unpivot” trick to convert the year information (which is contained inside columns) into row based data. However once that is done, lets look the various parts of the dashboard.

Before I jump into the various aspects of the dashboard, what really gives a dashboard a little polish is the use of a background image. Here is my go to ground image. Just a little playing around with Paint can produce something very valuable to your end product.

The dashboard utilizes 3 slicers. Each has a slightly different formatting. I definitely recommend playing around with the formatting of your slicers

The TREEMAP is where I have put a little extra bit of attention

What pops out here is the tooltip. I have created a separate page just for this tooltip. I am by no means an expert in designing tooltip, but know the power of inserting extra dimensions of data that again allows your dashboard to pop. This specific tooltip includes the blurb, an image URL and the main contractors. This information would be too dense for the overall dashboard and perhaps not dense enough for its own dash, therefore a tooltip is a perfect medium between.

The final element of the dashboard is the line graph and histogram. I still find creating line graphs difficult and in this case I had to add a measure to my data. I think there is a much easier way to achieve rolling sum data, but in my case, the below measure works easy enough for me.

CTD_line = CALCULATE(SUM(NASA_Budgets[Value]),filter(ALLSELECTED(NASA_Budgets),NASA_Budgets[Year]<=MAX(NASA_Budgets[Year])))

And with that, we have our completed dashboard

Extensions

There is a lot I can do with this framework now. We have a cost file that is quite generic and a dashboard that is also generic. We can in theory use this to outline any type of project pipeline. Although this dashboard is looking in the past, we can also have a rolling wave where we can see past spend on specific projects and what our future pipeline of work looks like. I love seeing project pipelines and following my NASA theme for the moment, here is a great view of what the NASA project pipeline looked like in 1973

PowerBI Progress & Schedule Dashboard – By Darrin Kinney

I have recently been developing a series of videos that highlight the key features utilized in a progress and schedule dashboard. The videos showcase the capabilities of PowerBI dashboards in the Project Controls space. I have not seen dashboards effectively used in this way and want to share the valuable knowledge.

 

I have recently been developing a series of videos that highlight the key features utilized in a progress and schedule dashboard. The videos showcase the capabilities of PowerBI dashboards in the Project Controls space. I have not seen dashboards effectively used in this way and want to share the valuable knowledge.

This series is not meant to be a step by step guide. There are subtleties about this demo that may cause difficulties in the production environment. I would simply recommend you share this with your development team and discuss the pros and cons of your approach. Oftentimes, a more straight forward approach is more valuable when compared to endless development polishing an inferior product.

 

Part 1: The Showcase

This video gives an insight into the key capabilities of the dashboard. Having the ability to seamlessly review schedule activities, and how these contribute to the overall progress and forecast, is invaluable.

The ability to quickly dive into your schedule, without having to deal with the confines and limitations of your actual scheduling tool are also key features.

 

Part 2: The Excel Feeder Sheets

This highlights a simple Excel feeder sheet. Too often the time phased data that our schedules produce are not easily accessible in a digital format. I have built an excel file around a typical structure that project controls deals with. This structure will lend itself nicely to the steps that follow in converting the into a database format.

 

Part 3: PowerBI PowerQuery

Here we import the data from the Excel feeder sheets into the PowerBI platform. The use of PowerQuery is so embedded with the way PowerBI works. The steps you need to follow here are the similar to the steps you would need to follow in inserting the progress and schedule data into any formal data structure. The way we think about data is sometimes not compatible with the format that databases need. This is specifically around the need to “unpivot” time phased data.

 

Part 4:  PowerBI Measures and Dax

With all the data now structured and available to PowerBI, we need to now dive into the use of DAX to create Measures. A perfect example in the use of measures is in the generation of progress curves.

What might seem line a straight forward approach to drawing simple progress curves, is in fact (within the realms of PowerBI) not that simple. However, if you follow a logic approach and know what calculations are needed, the world is your oyster.

 

Part 5: Integration of JIRA and Agile Methods

In the (as of now) final installment of this series, I showcase a way in which we can integrate our PowerBI dashboard with a JIRA project. This approach is completely different from what you might expect. I don’t want to put a PowerBI dashboard ontop of my JIRA task list. I want to put a JIRA task list on top of my schedule.

The purpose of the dashboard is to extract the SCHEDULE data from the scheduling tool. When variations to prior forecasts occur, or where further detail is needed, we are often constrained because pictures, running commentary and discussion about each activity is not something that resides in our schedule. However, we can use JIRA to easily capture those elements and use our PowerBI dashboard and a linking tool to integrate everything together.

 

The Future: ???

There are still a lot of features and extensions that I have yet to formally discuss. The next steps are likely going to be a showcase of a SQL Server backend for this data. There is a lot of information that is missed in the way this dashboard imports data (specifically past budgets). Therefore visibility into changes is restricted.

Another interesting feature is the use of saw tooth graphs when budget changes occur. I have a clear vision for how this is possible, but perhaps not the development know how. I will definitely be passing my ideas on to a few more tech savvy than I, and will hopefully have something to say about that in the near future.

In general, the way in which dashboards and data are embedded into our work processes, is a field ripe for growth. It is also an endeavor that can greatly increase the visibility into project controls data and can also bring teams together using integrated tools like JIRA. As such, the future is bright and where we should always have half an eye looking.

 

Digital Strategy in Construction – The Videos

Within the project controls world – I have found the strategic approaches to digital strategy to perhaps be a bit lacking. They key is that nothing exists in isolation. You need a fully comprehensive approach Gaining awareness of what is out there, gaining clear understanding of the current capabilities of your staff and in general, being at the forefront of our technological world is what the future will bring

Specifically in the world of project controls, I have found the strategic approaches to digital strategy to perhaps be a bit lacking.  This was the key impetus for me to put together the presentation last year. For me, digital strategy is not about implementing PRISM or EcoSYS. It isn’t even about upskilling your staff. There are more holistic views that can enable users to operate smarter. Obviously, this goes hand and hand with systems and education. They key is that nothing exists in isolation. You need a fully comprehensive approach that touches everyone on your projects.

The genesis of the ideas were several posts on LinkedIn and a Blog Post on Digital Strategy

I have posted each of these separately, but nice to have them all together in once concise post that I can reference in the future. There is a similar post on Agile in Construction

Digital Strategy – Dealing with Excel Hell

Excel Hell is where we all live and the area of our business that has seldom been touched by digital strategies. Perhaps times we start to think what we can do about it and move to the next step

Digital Strategy – Enter Data Once

The next step is Entering Data Once. This term is tossed around a lot, but the way we view and deal with this is a dogs breakfast. I don’t necessarily understand all the possible solutions, but perhaps the framing of the problem and discussions about what we can do about will stimulate some discussions.

Once we have data stored digitally, we can move onto the next step: Be Visual

Something as simple as a daily report – can be insanely valuable when translated into a digital environment. Enter information once and allow the entire project access to it. Digital forms – using tools such as OpenText allows for your creativity to be the limiting factor in entering data once and using it everywhere. Tools such as NINTEX can also so easily be tailored to fit unique custom needs in your existing sharepoint platforms.

Digital Strategy – Be Visual

The future is all about digital dashboards. If you are not in this space now, you will be in the future. Now that we have our key data stored in a digital format, we can start to move our reporting into the 21st century: BE VISUAL!

Whats in the Future?

I think the sophistication of many of the commercial software packages can in some regards leave my beliefs redundant. Companies like Sablono, Procure and JIRA not to mention a myriad of other providers are implementing many of the concepts I try to follow related to digital strategy. While some of the tools are known in the construction world, I urge people to push the concepts of MASHUP. If you can use something from another field in a creative way inside construction – that is where innovation and disruption come from.

Gaining awareness of what is out there, gaining clear understanding of the current capabilities of your staff and in general, being at the forefront of our technological world is what the future will bring

Thus, perhaps the most important strategy I can recommend

Digital Strategy – Follow all the latest Trends and know all the software capabilities

This is perhaps at the core of my beliefs. Unless you at the forefront, vision alone is not sufficient.

Digital Strategy – IT by itself does not solve your problems – its how you use IT