Completion Skyline template

Download the template for Excel here.

Edit 22-august-2018 : updated for PowerBI & Excel, you can select either precom or com, or any combination ( Phase, SP etc),  the DAX measure was done by Owen Auger 

Edit 14-may-2018 :  PowerBI now support more complex conditional formatting, the post is updated with PowerBI templates.  

PowerBI report here

PBIX here

Skyline charts are very popular in the precommissioning and commissioning phase, as in one page, you can see the remaining subsystems to be done, and how much ITRS to be completed, here is an example.

skyline

Excel does not support this chart natively, even power bi,( you can vote for this idea) , some people manually draw this chart in Excel, which is really time consuming or using complex vlookup formula, in this blog post i will share with you a template i built using powerquery and powerpivot, as you would expect that chart is generated with a simple refresh.

what data source you need 

for convenience, the data source are included in the same file, i used a dummy data for obvious reason.

Export from your completion system :completion_system_export

Export from your Planning Software :p6_export

A Master Table for Dates:mst_date

a master table is included as you needed to aggregate the date, there is no point in showing a skyline per day, here i am using week, but you can change the formula to be per month.

refresh

go to Powerquery, show pane, and refresh

refresh 

some consideration 

  • Although I personally use P6, any Planning software is fine, basically we need only 1 forecast date by subsystem per phase,  if the subsystem is spread in multiple PCWBS, then use the Maximum date
  • P6 here is used only for the forecast date, the actual data ( ITR and completion date) are from the completion system ,  in some cases, i overwrite P6 forecast date if it is earlier then the completion subsystem cut off date, if P6 does not provide a date for a particular subsystem,  I use the latest date from the equivalent system.
  • in the slicer Phase, select either Precom or Commissioning, if  you select both, th skyline will not work properly.  
Advertisement

Introduction to Modern Excel

Download the .pbix file, Click  here to open on the web.

Untitled

Modern Excel is a term  first coined to distinguish between Excel 2007, and Excel 2010  that saw  the introduction of PowerPivot and PowerQuery,  Excel has become a first class enterprise Business intelligence solution, Millions of row can be loaded, using PowerQuery , data can be linked using PowerPivot interactive, reports are easy to generate using PivotCharts and Cube Formula, and your audience will have more confidence in your reports.

Basically excel become a personal datawarehouse that store and analyze millions of records from multiple data sources.

A sample dashboard In Modern Excel

A “traditional” excel dashboard has no real distinction between the data source and the report itself, everything is mixed together with the results generated by a mixtures of vlookup and sumproduct, and maybe some VBA code,  if the boss ask for a different report, you have to start from scratch, but most importantly, it is error prone, if you move one cell, you may change the results without noticing, and there is no obvious way to audit the results.

The Dashboard was first built in Excel using PowerPivot and Powerquery then imported into PowerBI desktop then  published to the web.

I could have simply distribute the excel file with the reports  but I wanted to emphasis it is not simply another Excel spreadsheet with zillion of cryptic formulas that no one can understand except the author but a proper database solution based on columns not cells.

 

How it works

Anyone who used MS Access will find modern Excel very familiar, so let see how the steps needed to generate the sample  dashboard, it is only a very high level description, not a detailed tutorial.

Data Source: most of SQL Servers, excel, text files, web, ms access, basically any data source you may think of

Data Preparation: PowerQuery is a Powerful ETL Tool for cleansing data and prepare it to be loaded in the data Model, for example if your export the spread of hours from P6, the data will be spread horizontally, in a couple of clicks  you can unpivot the data and make it as a table

Data Model: is where the relationship between the table are defined, here is a screenshot from the sample dashboard

DataModel

Dax:  the language to query the data model and define your measures, it is not that different from sql queries

Report and Charts: Pivot table, Pivot chart and cube formula that read from the data model

Here is an example of a pivot table that query from the data model, notice how are all the tables are visible, a classical pivot table will show only one table.

Pivot

Powerpivot vs PowerBI

It can be a little confusing when you hear all those terms but basically The core technology in Microsoft Business intelligence stack is in an in-memory analytic engine called Vertipaq, the same technology is present in different products intendent to service different market segment

  • PowerBI : the cloud offer from Microsoft to provide business intelligence for enterprise, it mainly compete with the like of tableau and Qlik, PowerBI come in two editions free and professional.
  • SQL Server Analysis Services : On premise Enterprise offer come with MS SQL Server
  •  PowerPivot : in Excel 2010  and up for self-service Business Intelligence

Wrap Up

Self service business intelligence is changing how to deal with data, microsoft has manage to bring a powerful technology to the end users,  previously a word like datawarehouse was exclusively a heavy IT investment, now with PowerPivot and PowerQuery you can do it by yourself.

 

%d bloggers like this: