How to plot Digital Elevation Model in Data Studio.

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)

here is a script I used

library(raster)
library(ceramic)
library(bigrquery)
bq_auth("XXXXXXXX.json")
Sys.setenv(MAPBOX_API_KEY = "DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD")
cc <- ceramic::cc_location(cbind(14.428778,40.822973), buffer = c(2000, 2000), zoom = 15)
el <- ceramic::cc_elevation(cc)
el1 <- resample(el, cc, method = "bilinear")

df1 <- as.data.frame(cc,xy=TRUE)
df2 <- as.data.frame(el1,xy=TRUE)
df <- merge(x = df1, y = df2, by = c("x", "y"), all.x = TRUE)

df <-transform(df, lng=x/100000,lat=y/100000,red=layer.1,blue=layer.2,green=layer.3)
df <- df[c("lng", "lat","layer","red","blue","green")]
job <-  insert_upload_job("PROJECT_ID",
                "GIS",
                "VOLCANO",
                df,
                create_disposition = "CREATE_IF_NEEDED",
                write_disposition = "WRITE_TRUNCATE")
wait_for(job)

2-Plot the Data using Point Cloud Viz

the Custom Viz address is

or you can just copy the report and use your own data

all fields are required except tooltips, by default it will show coordinates

I used Mount Tahat as an example, it is a highest Moutain in the south of Algeria, extremely beautiful area

Data Studio limit the number of rows passed to a custom visual to 1 Million, here I made sure it is less than 750K as it is the maximum that can be downloaded from the visual

3-The end Results

Mount Uluru in Australia

Volcano Vesuvius in Italy

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

Custom SQL in Google Data Studio

in the last 12 months, Google Data Studio has added many new interesting new features, specially the integration with BigQuery BI engine, and custom SQL Queries.

Obviously, I am a huge PowerBI fan, and I think it is the best thing that happen to analytics since Excel, but if you want to share a secure report without requiring a license for every user, Data Studio is becoming a valid option.

I have already blogged about building a near real time dashboard using Bigquery and Data Studio , but in this quick blog, I will try to show case that using SQL one can create a more complex business logic reports.

I am using a typical dataset we have in our industry, a lot of facts tables, with different granularity, the facts tables don’t all update at the same time, planned values changes only when there is a program revision, actual changes every day.

Instead of writing the steps here, please view the report that include the how to and the results.

The approach is pretty simple, all modern BI software works more or less the same way( at least PowerBI & Qlik, Tableau is coming soon), you load data to different tables then you model the data by creating relationships between the tables, then you create measures, when you click on a filter for example, or when you add dimension to a chart, the software generate a SQL query to the data source based on the existing relationship defined in the data model, it is really amazing , even without knowing any SQL coding you can do very complicated analysis.

Data Studio is no different to other tools, the Data Modeling is called Blending, it link all the tables together using left join, which is a big limitation as if some values exist in one table and not in others, you will miss data.

The idea is let’s bypass the modeling layer and write some SQL code, and to make it dynamic let’s use parameters, it is not an ideal solution for an average Business users ( we don’t particularly like code) but it is a workaround, till Data Studio improve it’s offering.