Kelpler.gl is a Geospatial data analysis
by Uber !!!, in this short blog we show
how to build a map with a time attribute and then showing a time playback slider,
it is very and straightforward process, all you need is to format the data as a
list.
This data was randomized, as an example I show the main step to build a solar farm, piling, tracker, module then Electrical works, but obviously you can try it with road , pipeline projects, any project really that have geographic data
Please note the data is rendered using your local computer, so it is totally safe
The map is here,
keep reading if you want to understand how it was built
I attached a sample dataset here;
the field are self-explanatory.
Just be careful with Excel for some reason, when you open
the csv file, it changes the date format, make sure the format is like this
And because in a medium sized solar farm, the total pile
will be around 50,000 Piles, if you want just to show 4 main stages, the total
number of rows will be 50,000 * 4 = 200,000 row, PowerBI will not support that
( Tableau shine in this scenario),
The documentation is very clear, please read it to experiment with your own data
Add data
If your dataset has this fields names
X_lat, X_lng, Kepler.gl will automatically recognised it as a point layer
2-Define the legend
We select colour based on the field status ( select the palette
of colour)
3- Select time field
The filed should be recognized as time otherwise, it will
not work.
4- Add satellite as new map Style
For some reason, it is not there by default, but it is
trivial to add
mapbox://styles/mapbox/satellite-streets-v9
5-And voila
You can play with the slider, either moving the end date
or a window, up to you
6-Now you have multiple options for sharing
You need to have a mapbox
account, it is virtual free to medium usage !!!
For this example, I will share it using Dropbox, but you can export to HTML and securely share it either per email or hosted in sharepoint
This is one of the best piece of software
that I had the privilege to use, nothing to install, free , simple and straight
to the point !!
I have been asked to produce a simple construction report, we need to show the last 4 weeks of actual progress data and 6 weeks of forecast and to make thing a little bit complex the average installation since the start of the project, nothing special three measures, average to date, install per week and forecast per week
Obviously, it is trivial to be done in Excel using named sets,
if you don’t know what’s named set and cube formula is, you are missing the
most powerful reporting paradigm in Excel, a good introduction is here,
and there are plenty of resources here.
Unfortunately named set is not supported yet in PowerBI, you
can vote here,
Just for demonstration purpose, if you try to add those three
measures to a matrix visual, PowerBI just repeat them for every time period, obviously
that’s not good at all, the actual
installation make sense only in the past and the forecast has to be in the future,
there is no option to hide a measure if there is no value in a column and even
if it was possible we need to show the average installation independently of
the time period, anyway this the report when you add the three measures
and because I already learned a new trick on how to dynamically add measures to a matrix visual in PowerBI, I was tempted to try and see if it works in this scenario.
So, let’s see how it can be done using the disconnected table
Create a disconnected table with two columns
Order and status
Add a calculated column,
As the cut-off date change at least three times a week, the week number change accordingly, we can’t simply hard code the dates, instead let’s add a new calculated column, which will just lookup the week date from a master calendar table based on the order, when the order is -4 it will return “average to date”, I added a dummy 0.5 order just to add an empty space between actual and forecast ( cosmetic is important)
Primavera has a very comprehensible security mechanism, based on OBS and EPS and user access, it is very mature and rigorous, but once you have a direct connection to the database’s backend all this security disappear, the connection will give you access to everything in the database.
This blog post is not a comprehensible security introduction to Primavera reporting, but rather a gentle introduction to RLS in PowerBI and how you can leverage it in the simplest form possible, and a reminder why security is a very important consideration, specially when you deal with a portfolio and multiple division in the same company.
if you don’t want to read the whole blog, I think all I want to say is
Only Primavera Admin or IT should have access to Primavera Database connection.
There are multiple solution to implement Projects level access, particularly if you are dealing with multi divisions portfolio
What is row level security?
Is the ability to access a table but read only some rows,
for example you want to read the table TASK, which show all the activities for
all the projects, obviously you need your user to view only the activities that
belong to the projects assign to them.
The Problem
Primavera administrator want to create a companywide reporting
system based only on Primavera Database, RLS is implemented in PowerBI everyone
see only the data that belongs to him, happy days, planning manager from
division X like the reports but wants to use other data sources too, not only
Primavera but Ecosys, cobra , progress measurement system etc, still he needs
to see only his projects, Planner Y don’t care about PowerBI and wants the raw
data to do his own stuff using some obscure VBA Excel, PowerBI as of this
writing can not connects to multiple datasets from other reports, and you can’t
mix live connection with import from other source.
Solutions
Whatever you do , you need to have only one connection to
Primavera Database, don’t give multiple users access to the production
database, that’s bad ( probably your DBA, will not do it anyway), I know it is
still read only, but it is bad practise, if your write an expensive query
against a reporting server, it is annoying, but slowing Primavera server will get you
angry planners from everywhere ( we are very famous for being grumpy)
Reporting Database
Ideal solution, just spin a cheap SQL instance on azure and make sure it is on the same region as your PowerBI ( no egress fees), implements RLS there for the people who want to author reports ( the viewer access will be done in PowerBI),any IT can easily create a small pipeline to copy data from Primavera Database ( doesn’t matter, Oracle or SQL server) and you get your data fresh every morning or whatever schedule you want. ( bonus point, no PowerBI gateway between PowerBI and SQLServer as both are in azure)
PowerBI Dataflows
If SqlServer is
not an option , PowerBI dataflows seems like a perfect solution here, you
connect once to the Primavera databased and you can share the results with other
user, PowerBI will be hitting the
Dataflows instead of the production server, perfect, yes, unfortunately,
Dataflows results are just CSV, no RLS, either you read the whole TASK table or
not.
Analyse in Excel
Personally, I think it is the most Powerful feature of
PowerBI, when you have access to a report in PowerBI, when you click analyse in
Excel you get access to all the data behind the model, not only that you can
create a table that fetch the data and bingo, it does honnor RLS, you see only
your stuff, so the workflow will be something like this, for how to use analyse
in Excel, Please read this excellent blog by Chris Webb
Let’s see how to use RLS in PowerBIRLS is very vast subjects, and has many different implementations and nuance, in real life you need to have something like a hierarchy security like Primavera OBS, and you need to read this excellent series of blogs from Reza, let’s say I want to grant view access to a couple of Projects to two users (Viewer @ projectscontrols and test@ projectscontrols ), those users are not necessary planners they don’t have access to Primavera
I just used Project user defined field and typed the full address,
you need the email address as it is the format that PowerBI understand, the
good thing about UDF, you can put multiple values separated by a comma, I
pulled the following tables from P6 Database (UDFTYPE , UDFVALUE) and just
using PowerQuery to split and unpivot, and I go this little tables.
Now I have the Project ID and the username of the users who
can access the projects.
Edit the relationship between access table and Project
tables
Manage role
Basically, table access get filtered by PowerBI, then it
will filter table PROJECT, that will filter table TASK
Publish to PowerBI service to the owner account but not to user Viewer as he should only have access to view and build ( you need build to use the dataset in other reports), RLS works only with viewer role,
Now let’s see what the user viewer will see
Yes, only 7 projects are visible to that user, let’s try analysing
in Excel,
Once I select table access, only the viewer gets selected
Now let’s query only the table TASK ( yes DAX is a query language
too)
Voila we have the TABLE Task filtered, with only 7 Projects
Now you can add more tables and load those tables into another PowerBI datasets and do your own enhanced reports.
Primavera P6 calculated a lot of rich metrics for a
schedule, we all know the basics, Start Date and Finish date, but there are all
kinds of other values ( Cost, Labor hours, not Labor , Float Duration etc)
The challenge is, different people want to see different
measures, and once you publish your
reports, the viewer can not change the visual, Microsoft is working on this feature,
but it will take another couple of months to be released.
The trick was suggested by Kasper
de Jonge and in a random chat with @DingbatData@_Ivan_Bond , they use it
already to solve similar issues, actually it is very easy
Anyway the
purpose to reproduce something like this from Primavera
Create a new table that contains all the measures
Instead of copy and past all the measures, I just used DAX studio
connected to my PowerBI desktop to generate a list of all the measures, read this
link
The Category is to just to make it easy to select which measure to select, the index is to keep the same sort in the slicer, I don’t want to show actual Finish before Actual Start.
2- Create a Master Measure that check if the value is selected.
Using Switch and SelectedValue give the result, I had only to add some condition
to format the results of Date to be show as date not Number, you can vote on
this idea
Here is a snapshot for the first 4 measures ( currently I have 29 in the
models)
3- Add the column Masure_Value to the column in the Matrix and Seletced_value in the values Area.
4-Voila
5-Bonus
I am using bookmarks to toggle the select Columns Button, I think it is
freaking cool, hopefully in the short
term PowerBI add more features so we can build not only dashboard but real application
interface.
Small Data And self service 