/pictures/ls_highlight_filter.gif)
In this blog post, I want to summarize the new releases from the Google tools, that we use daily in datadice. Therefore I want to give an overview of the new features of BigQuery, Dataform, Looker Studio, Google Analytics and Google Tag Manager. Furthermore, I will focus on the releases that I consider to be the most important ones and I will also name some other changes that were made.
If you want to take a closer look, here you can find the Release Notes from BigQuery, Dataform, Looker Studio, Google Analytics & Google Tag Manager.
BigQuery
User-defined aggregate functions
You can already create UDFs in BigQuery. Now it is also possible to develop UDAFs (User-defined aggregate functions).
UDAFs have in general the same functionality as the UDFs:
- They can be persistent or temporary
- They can be written in SQL or JavaScript
With UDAFs, you can expand your tools of aggregation functions next to usual functions like AVG, SUM, …
An example would be:
CREATE OR REPLACE AGGREGATE FUNCTION dateset_name.DoubledQty(qty FLOAT64)RETURNS FLOAT64AS (SUM(qty * 2));SELECT dataset_name.DoubledQty(qty) AS doubled_qtyFROM (SELECT 1 AS qty UNION ALLSELECT 3 AS qty UNION ALLSELECT 5 AS qty);
The result of this query is 18. The created routines you can find in the corresponding dataset:
It is a nice new opportunity to develop custom functions. If you need the same kind of operations more often you can put it in a UDAF.
Data Canvas
An interesting feature that combines Gemini AI and drag-and-drop visualizations.
We will write about this another blog post because there is a lot to cover.
To create a data canvas you need to click here:
I will just give you some ideas what are the possibilities:
You can add some tables to your canvas to write queries for it. The query you can write yourself or use the AI prompt to describe your needs and let the AI generate the code for you. For the Gemini part you can take a look in one of our last blog posts. I think the AI approach is a good way to have a solid starting point (although I am sure that Rubens Barrichello did not finish a race 6520 times in the 10th position :))
In this example, we are joining two tables with each other to get further results.
I think the strong part of this data canvas is to have different queries on the same page. Especially in the phase where you are discovering new data or checking your modeled data, it is a nice way to do it, especially with the support from Gemini.
New BigQuery ML features
There are again some new BQ ML features available that I want to mention:
- Creating multimodal embeddings (embedding text and images) with a remote model based on Vertex AI
- Using a PCA model in a ML.GENERATE_EMBEDDING function
- Using autoencoder model in a ML.GENERATE_EMBEDDING function
- Using a matrix factorization model in a ML.GENERATE_EMBEDDING function
Access BigQuery Data in Power BI
Google still improves its cross-cloud features.
You have now the possibility to access data located in BigQuery via the Power BI App or Power BI Web.
To do this you need to:
- Create a workforce identity pool in GCP
- Register an Entra App from Microsoft
- Create a workforce identity pool provider in GCP
- Set up the correct IAM policies in GCP
A step-by-step guide to setting up this connection can be found here.Then you can query BigQuery data in Power BI.
Then you can query BigQuery data in Power BI.
Dataform
Using CMEKs for repositories
Google encrypts your data by default with encryption keys. These are managed by Google then. Now it is possible to use your own encryption keys for a Dataform repository.
The content of the repository, compiled and stored SQL queries and error details will be encrypted by these keys then. There are two key types supported:
- Cloud KMS Software keys
- Cloud Hardware Security Module keys
If you want to know how to set up these CMEKs, you can take a look here.
Looker Studio
New timeline chart
There is a new chart available in Looker Studio, called the timeline chart. A chart which is useful for some use cases, but you need to prepare the data before that you can show the data in the way you want.
Here is some example data. It shows the Formula 1 World Champions and which season they won.
And after doing some configurations in Looker Studio you can see the following:
On the X-Axis you can see the year of the season and on the Y-Axis are the different drivers.
I think one of the most important settings for this chart is the “Group by row label” option. In the chart above the option is enabled, because of this Lewis Hamilton (wtih different win seasons) appears in one line and not in three.
You can find a detailed guide about the timeline chart here.
Report creation from a Google Sheet
You can now create a Looker Studio report from your Google Sheets data.
In the Google Sheet where you want to create a report, you click on Extensions > Looker Studio > Create a report. Then you can change some settings and click on the create button.
After this, you get a report with some preconfigured charts, and the Google Sheet is already added as datasource.
This approach is just recommended if you have some Google Sheets data and you need quickly drag-and-drop opportunities to show some visualization.
If you want to create a solid report it is better to start with a blank report (or copy an existing one) and do at first the most important changes for the theme, metasettings, … before you start adding data.
Google Analytics
Import Salesforce data
Google added the functionality to import event data to your Google Analytics 4 property.
To add this data you need to go to the admin section > Data collection and modification > Data import > Create data source
You can just add offline event data via Salesforce. Before you start importing you may need to change some settings in Salesforce. You can take a look at this guide to go through it.
Imported user data for audiences
A quick one.
When you have user data imported from another system (CSV, …) this data is usable for audiences now. Additionally, if you already created audiences and (some of) the imported data meets the audience criteria these users are added automatically to the audience.
Google Tag Manager
No further release for the Google Tag Manager.
Upcoming datadice blog posts for this month
- Power BI features - Coming Soon
- BigQuery Data Canvas- Coming Soon
Further Links
This post is part of the Google Data Analytics series from datadice and explains to you every month the newest features in BigQuery, Data Studio, Google Analytics and Google Tag Manager.
Follow us on LinkedIn for insights into our daily work and important updates on BigQuery, Data Studio, and marketing analytics.
Subscribe to our YouTube channel for discussions on Data Warehouse, BigQuery, Looker Studio, and Google Tag Manager.
If you are interested in learning BigQuery from scratch, get access to our free BigQuery Course
Elevate your skills with Google Data Studio and BigQuery by enrolling in our Udemy course.
Need help setting up a modern, cost-efficient data warehouse or analytical dashboard? Email us at hello@datadice.io to schedule a call.
