I have blogged already how to drill down to another page using Parameter, today, I find another approach, it is extremely easy, and a bit embarrassed why I have not thought about before !!!
I will be using administrative division of Algeria as an example, see the report here, please notice the pattern is not only for maps, personally I use drill down to another page extensively with finance reports, for example drilling down by Project, vendor, user etc
1- Create a map at the region Level
2- Add a new empty tab
3- Make the Map at the report level
4-make sure apply filter is on
5- in the second tab , hide the map either using send to back or add a big rectangle
6- add a new map at the city level
7- add a new button to navigate between page 1 and 2
basically when you create a report level map, the selection from page 1 get synchronized to page 2 , then the hidden map in page 2 will filter the map at the city level.
TL;DR : a sample dataset with x,y,z,red,green,blue and a custom Viz in Google Data Studio Using Deck.GL point Cloud, see example here
I added a new dataset , so you can test it yourself, you can either load it using BigQuery or use the load file connector in Data studio.
section explain how we got the data, if you are only interested in testing the visual go to section 2.
1-How to get the Data
for some reason it it is extremely painful to get a dataset with x,y,z,r,g,b
luckily a couple of days ago, I was in twitter and saw this tweet by Michael Sumner
it turn out extracting coordinated and elevation is extremely easy using R, all you need is the center location and the dimension of the area you are interested in, and R ceramic will extract x,y,z automatically in a nice dataframe, then I took that data and uploaded it to BigQuery using the package bigrquery then plot using a custom Viz I built using Deck.gl ( see the linked report)
BigQuery recently introduced two new GIS functions, ST_CONVEXHULL and ST_DUMP
Read the announcement here , when I saw the announcement I already thought about this use case.
Although showing map in BI software has improved dramatically in the last couple of years, still unless you use Tableau, there is always a hard limit how much data you can show in a map, even if you can show more, it is better to reduce the volume of data just for performance sake, users are so spoiled those day that they complain when their report does not show up in less than 2 second.
Although the example used here is very specified, I am sure it can be extended to other uses cases.
Let’s say you want to show a lot of points with one colour coded attribute, in a lot of cases, the end user wants only to see the distribution of the attribute not the individual points, see here
That’s a lot of points ( my real case is 58 Thousand)
Convexhull to the rescue
Convexhull is very handy the input will be group of points and the output will be a closed polygons, I use it a lot in QGIS, but the killer feature here, because it is SQL and the attribute are dynamic, (in my use case they changed daily), you can write a Query that dynamically generate new geometries, either polygons or linestring or even keep the original points if they can’t be grouped.
Now the trick is we group by status and existing grouping, for example in this dataset.
Check if in one area all the status is the same using count distinct, if in one area it is the same attribute, it will generate a polygons.
if one area has multiple status and hence multiple colours then fine, we jump to the row level and generate line strings.
If one line string has multiple colors then we jump to points.
I built this SQL View with the help of Mikhail Berlyant, the source data is here, replace “xxxxx.SolarFarm ” with your table.
source AS (
ST_GEOGFROMTEXT(CONCAT( "POINT (",x," ", y,")")) AS POINT,
COUNT(DISTINCT status) OVER (PARTITION BY ROW) AS multiple_status,
COUNT(DISTINCT status) OVER (PARTITION BY area) AS multiple_status_area
tt AS (
id, pole_nr,color,area, ROW,status, POINT,
WHEN multiple_status_area=1 THEN area
WHEN multiple_status=1 THEN row
CAST (id AS string)
ff AS ( SELECT newgroup, ST_ASTEXT(ST_CONVEXHULL(ST_UNION_AGG(POINT))) AS WKT
xx AS (
tt.newgroup = ff.newgroup)
and here is the result side by side with the original data from 3528 rows to 283 rows, that’s a big improvement,
as of July 2020, Google Data Studio does not support Geometry, and the total number of points is limited to 10K, you can use other custom Visual but currently tiles are blocked.
if you are using PowerBI to view the data, you need to use the excellent Icon Map as it support WKT geometry
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.