5 tips for building a better dashboard with Google Data Studio and BigQuery
… or 5 tricks to ease the pain and do things better
If you are in data analytics this article is for you.
Google Data Studio is f̶u̶l̶l̶ ̶o̶f̶ ̶b̶u̶g̶s̶ a free tool to build custom reports and visualise your data (which Google claims is beautiful). My data is not so beautiful. I would say building dashboards with it is a struggle. And this post is about how to build you reports quicker with no sudden surprises like the one you can see at the top.
Yup :) This was my first BigQuery invoice. I’ve managed to claim it back but it still soars…
So if you want to do things more efficiently with no sudden surprises just have a look at these tips below.
1. Use better partitioning
Do it first. Define partitions just before you load your data into BigQuery. It saves time and money later.
See that magic number of £3002.72 ? Yeah, that happens when you do a SELECT * FROM big table. Don’t do it. Use partitioning.
So why not just partition by timestamp when loading data into BigQuery? This approach simplifies things when you run custom queries from Google Data Studio with date params.
The trick is you can set partition column in load jobs:
Create a function and that will work beautifully like in this demo:
2. Stop using filters in Google Data Studio
Filter custom datasets using Google spreadsheets instead.
That’s my favourite one. Built-in Data Studio filters might do a trick for two or three things to filter out but for something bigger you can use spreadsheets.
Even though this type of tables (based on Google sheets) is slow this trick saves time. Just copy data to a new table if you need a better performance and then do a JOIN to filter your dataset.
INNER JOIN `your-project.reference-dataset.filter` f ON f.id = c.productId
It takes seconds to create such table:
3. Load data in source JSON format
That’s a complete hack. I love it and I brought it from Snowflake .
Parsing timestamps, etc. with JSON_PARSE function. It’s all simple now. You just load data you have into one field in source format (which is JSON).
Example of your schema.yaml file to load data into BigQuery:
Then you just create a view. Simple, like so:
4. Pivot tables in Google Data Studio
PIVOT is not supported in BigQuery SQL and it’s a big miss. Of course you can do something like in this stackoverflow post.
Just use pivot tables in Google Data Studio. It’s really cool and makes things with dashboard prototyping a lot faster.
5. Be careful with cache
In BigQuery if you make the same requests multiple times the result is cached and is charged only for the first time.
This won’t necessary work for your Google Data Studio report :)
The thing is that when you connect data to your report you should really consider the type of your data source. BigQuery, Firebase, Google Analytics, AdWords, for example, are dynamic ones which means that your report will update the data in your reports automatically every time you refresh or load the page.
Data Studio makes different requests to build every single chart in your report and does not use cached results.
New Data Studio BI engine might be a worthwhile looking at too.
Thanks for reading!
- Data studio community links: https://support.google.com/datastudio/threads?hl=en&thread_filter=(pinned:true)
2. Connect to BigQuery Google Docs: https://support.google.com/datastudio/answer/6370296?hl=en&ref_topic=7332343
3. How to Pivot table in Big Query: https://stackoverflow.com/questions/26272514/how-to-pivot-table-in-big-query
4. Types of data sources: https://support.google.com/datastudio/answer/6268208?hl=en
5. BI engine documentation: https://cloud.google.com/bi-engine/docs/
6. Data Studio cache: https://support.google.com/datastudio/answer/7020039
7. GDS Data freshness: https://support.google.com/datastudio/thread/25196948