Rendering 1.2 Million points in PowerBI using Icon Map

TL;DR, the report is here , pbix here , download Icon Map here

I blog about it really before here, but in a colorful discussion on twitter after they closed this bug report, (Max rows in PowerBI is 30 K), I recalled a bug report about R ggplot, when the authors suggested to use multi points instead of individual points to speed the plotting of the map !!! all I had to do is to check if icon map support it and it did !!!.

WKT format for point is POINT (x,y), all you need to do is to concatenate multiple rows of point in MULTIPOINT format MULTIPOINT ((x y),(x1 y1), etc).

now we need to deal with three thing:

1-The maximum row numbers returned to a visual is 30K.

2-The maximum length of a column in PowerQuery is 32766

3-The Maximum length of DAX function is 2.1 Million

so a calculated column is a no go, I am using this DAX measure to concatenate the text from (x y) to a Multipoint format, again using Chris blog, I got this measure

WKT = var concat =CONCATENATEX(values(openstreetmap[point]),openstreetmap[point],",") var wkt ="MULTIPOINT ("&concat&")"return if (concat =BLANK(),BLANK(),wkt)

using this table that contains all the items tagged as amenity in Openstreet Map, the table contain 17 Million records, got it from BigQuery dataset

remember you don’t want to group all points in 1 row for two reason.

1- Concatenax Max is 2.1 Million

2-it is better still to group by common attribute, at you can color code for example by country or category or both if you want, in this case, I added a third factor in MULTIPOINTS, just a number that change very 30k rows to make sure I will not end up with a multipoints > 2.1 million, Initially It was 100K, but I notice icon map become extremely slow

now in icon Map, you need to assign three fields.

Category : multipoints

Icon URL/WKT/SVG: the measure wkt, this extremely clever and flexible, as it is a measure, that will render using the filter context of category, you may want to be creative and implement drill using different level of details, as the geometry is calculated on the fly.

Circle/line/WKT/Geojson Outline Color : a color in hex format, in my case, coded by country, ( at work for a different use case, I use a measure instead to show change of status per time)

because, the data set is relative big, I use this option in PowerBI

Just to be clear, this only proof of concept, rendering a big dataset will be slow and will eat all your memory, and probably you will get errors in shared workspace, or if you are in a premium workspace, probably you will end up in a trouble, but it is cool, personnaly, I use it to render a 58K points and it is very smoth.

anyway here is the result filtering the tag place of worship 1.1 Million, I tried parking which is 3.2 Million but my laptop crashed !!!, I know it is subjective, but that looks very beautiful for me.

here tag : School and University color by Country

edit : got a nice feedback from Reddit user data_Crucher, just to improve the performance I materialized the results using a calculated table, the drawback is you increase the size of the model, but I guess it is worth it, and I changed the decimal precision for the lat and long to 4 digits just to reduce the size, the pbix is around 600 MB.

again for production scenarios, I think around 100K points should be doable.

How to build Compact layout Pivot Table in Google Data Studio

TL,DR : the report is here. and I appreciate a vote on this bug report

First, don’t be excited, it is a silly workaround, and introduce it is own problem, but if you are like me and need to deliver a nice-looking pivot table in Google Data Studio, it may be worth the hassle.

The Problem.

Show the spent and budget by Campaign and country, the spent is at the country level, the budget at the country level, here is a sample data set.

The Solution, First try

Probably you are saying, it is too easy, why you are writing a blog about it, GDS support pivot Table !!, let’s see the result

We have three Problems already (1 bug, 1 limitation and 1 by design)

Bug:            you can return not return a null in the metric spent

By design:  GDS does not understand hierarchy, country null is all good.

Limitation: The Famous Excel compact View is not supported

Here is the deal, contrary to what you may read in the internet, Pivot table is the most used viz in reporting ( ok, maybe second after table) and users will want their pivot table to look exactly like their beloved Excel, my own experience, if you show a user a map for example and he ask for a feature which is not possible, you can say, I can’t do it and people will tolerate that, but their Excel looking Pivot table, zero tolerance, if you can’t reproduce it, they will either think :

  1. Your BI is not good
  2. You don’t know the tool

The Solution, SQL!!.

Write a SQL that return a column that show the campaign and country in the same field, using union

Assuming your data is on google sheet.

  1. Link Google Sheet to an External table in BigQuery

2-Write the Query

Connect to that table using a custom Query

SELECT project,sum(budget) as budget,sum(spent) as spent FROM `test-187010.work.factraw`

group by 1

union all

SELECT Concat(“\U0001f680”,country), budget, spent FROM `test-187010.work.factraw` where country IS NOT NULL

3-BI engine does not support External table.

Every time you open the report, GDS studio will issue a new query which cost 10 mb minimum !!!, even if the data is 1 kb ( it is a big data thing after all),  to avoid that we extract the data

4-Profit 🙂

 We use conditional formatting to highlight the row campaign.

needless to say !!!! you should not use it unless you have to, cross filtering will be a mess , Hopefully GDS will improve pivot table formatting in the near future.

Is Google Data Studio Ready for Complex Business Reports ?

 I love Google data Studio, I am using it for this project nemtracker.github.io and it is perfect for this use case.


For other Dashboard at Work, I am using PowerBI, for no reason, I tried a new experiment, can GDS be used instead, it is an academic exercise only.
the use case is a typical Business Reports, a lot of different small datasets with different granularity , something like budget vs items sold etc.


I am just recording the pain points in GDS , the good thing, most of them are under development, and some of them overlap, for example if we have Parameters controls, probably, there will be less need for blending ( which is very limited at the moment).


it is not a critique, GDS has some killer features, I particular like custom visuals as there are no limit the number of data plotted which is a pain in other Software.


the assumption is all the data-sources is already loaded and cleaned and ready to be analysed in BigQuery.


TL,DR : the pain points are at the Calculation level , Obviously if all you data is at the same granularity, then everything is easy
my conclusion, nearly there !!!, but will revise when Parameter controls are supported.

Instead of Writing a full blog, I thought showing a report is a more practical approach

Integrated EPCM Management – Engineering Progress

How is an engineering deliverable list a bit of a misnomer? Find out of a web page with editable fields can streamline the tracking of progress on list items.

I am a firm believer in the use of a standard project website portal from which the project team at large can quickly access key data and metrics about the project. This is not meant to be confused with a project dashboard, or PowerBI visualization. This is simple html file linked to a database with key flat-file information. Using smart JavaScript code, it is also possible to EDIT some of the key information.

With everything, before we even begin, we want to focus on what core digital strategies we are trying to tackle. Again, the project website is just a tactical approach, underlying it are the more relevant strategic goals we want to operate under.

Digital Strategy – Make Information easy to access

Digital Strategy – Agile Construction Management

Digital Strategy – Allow people to EDIT key data

The features discussed in this blog can be seen showcased in the following video. This is not a pipe dream. This is a functional application.

The video can be viewed in a separate window at https://www.youtube.com/watch?v=0XNA9xJS2yY

The Data

The key data sources involved with engineering progress will be:

  • Engineering Deliverable List
  • Manhours per WBS

Your engineering deliverable list is a bit of a misnomer. A lot of engineering and design tasks are not specifically related to a “deliverable.” In my view of the world, while you will have specific deliverables and need to track them, your progress list should also include everything else you are doing too – up to a point where perhaps the level of detail is too small.

For analysis, productivity factors for engineering are critical. As such, this post would not be complete without a discussion and visibility into hours and productivity factors.

The Menu

Our menu follows many of the key functions of project controls. However this specific post will dive into the ENGINEERING section only.

If we expand our menu, we see various views into our data: by project, discipline, contracts, schedule IDs and a unique view for controls engineering

Detail Views / EDIT MODE

Perhaps before we look at our summary and drill downs, the main control screen we would use on a day to day basis would be the Deliverables by WBS and Discipline

In the below screen, we can see all out progress items, our budget hours, progress% as well as the mapping to P6 ID (as all progress items should be mapped to into your schedule)

This view into deliverables by itself is not specifically unique. Where the magic happens is when we enter EDIT MODE.

Inside our EDIT MODE, we can directly update the progress %, and update our mapping to schedule ID. These features in my mind are your killer features that distinguish this from any other app. The shear ease in updating items in this way is a breath of fresh air, not to mention the live feedback and visibility throughout your team.

JIRA

Each combination of WBS and Discipline can be mapped to a JIRA task. Obviously we know our WBS can be considered an EPIC. The power in using a tool such as JIRA is that we can now track more detailed L4/5 tasks using JIRA Subtasks. These are completely flexible to allow the user to manage their own tasks.

Additionally, the ability to embed commentary and status is a brilliant way to distribute and communicate key status and blockers if any.

 

Summary Pages

Now that we have seen how we will actually interact with a project website, we can now showcase the summary level reporting. Specifics of how you report can often times be better captured using an analytics platform like PowerBI; however, more often then not, simple summaries and metrics are what drive our business.

In the summary by project, we can easily see key metrics per project. Each project would have a link to allow us to drill into the details for each project

In the above, we are now looking at the individual functional delivery areas (disciplines) for our project. Again we can see key budgets, spent hours, progress and productivity measures too. By clicking each discipline, we further dive into our project by now looking into the detailed WBS elements for that Discipline

It is in this view that we can see detailed metrics per WBS/Discipline pair.

Understanding our Spent Hours from time sheets is a critical management function. As such, the SAP actuals here is a link that takes us to a screen where we can view the detailed weekly time sheet data to see who (and when) has book to this element.

Conclusion

In summary we started with some key strategic thinking and built out a tool that ticks a lot of boxes in the EPCM construction world.

More often then not, I am confronted with technology that “shows me” but doesn’t allow me to interact, edit, or collaborate. Everyone wants to solve the “one source of truth” however, information changes and is updated by people. That is the missing link in a lot of our data analytics. Look into the work processes that generate information in the first place. Look at what people need to better capture their raw information into a data format and platform that others can now use.

This approach to put management of engineering items to plain sight, with ease of access is just one approach. There are 100s of ways to approach this, but if you stick to core strategic ideals, you can’t go wrong.