Create a Data Studio Data Source

Owned by Leopold Strauch
Last updated: Dec 31, 2021 by Siamak Haschemi (Deactivated)
4 min read

In this tutorial we will now show how to create a data source in Google Data Studio for ready-to-use views provided in our public DWH dataset i19s-insights:insights or by using of custom query.

As a first step, navigate in Google Data Studio to the “Data sources”. Here you can create a new data source.

Choose BigQuery as Connector

Now you have multiple options to point to the data you want to use in the data source. We will describe two of them:

  • Point directly to the provided views

  • Use custom queries

Use provided ready-to-use data views directly

This would be the option you probably use in the most cases. We provide several ready-to-use data views in the public dataset i19s-insights:insights where facts are already joined with the dimension data and can be used directly in your reports. If you decide to do something more (i.E. combining of the data from different views in one source) check out the section Use custom query of this document.

After you have chosen BigQuery as Connector as shown above, next steps are:

  • Click on SHARED PROJECTS

  • Choose the billing project

  • Type i19s-insights as shared project name

  • Choose insights dataset

  • Click on the view you want to have in the data source

  • Click on CONNECT to see the list of fields and settings like credentials, freshness etc.

Done. You can use the source in your Data Studio reports.

Most of views are partitioned by date and require filter by a date column. You have to use data range according to the partitioning key in your report.

Use custom query (advanced)

In the article https://ingenioustechnologies.atlassian.net/l/c/rda0qBTW you have learned how to create a custom query. Now we will show how to use this custom query to create a data source in the Google Data Studio.

Parametrize the Custom Query

Google Data Studio supports the concept of parametrized custom queries. Parametrized queries have the advantage that they are more efficient than general queries, since only the data that is really necessary is queried. In an SQL query, placeholders are set which are then filled with values at runtime. A parameter could be a date field, for example. If the user changes a date filter in a report, the correct values are inserted into the query when the query is run.

In the article https://ingenioustechnologies.atlassian.net/l/c/ySn8pKYb , you have learned how to query a fact table and filter it by partitioning key. At this point you will insert the parameters.

From:

SELECT * FROM `i19s-insights.insights.finance_conversion` WHERE DATE(trackedAt) between '2020-01-01' and '2020-01-01' LIMIT 100;

To:

SELECT * FROM `i19s-insights.insights.finance_conversion` WHERE DATE(trackedAt) between parse_date('%Y%m%d', @DS_START_DATE) and parse_date('%Y%m%d', @DS_END_DATE);

The WHERE part of the SQL query is filtered by the field "trackedAt". This is the partitioning key. This value must lie between @DS_START_DATE and @DS_END_DATE. These two parameters are placeholders used by Google Data Studio. But also other analysis software uses exactly these placeholders for time periods. Furthermore, it is important that the LIMIT statement is removed.

You select the BigQuery Connector from the Google Connectors. In the next step, select "Custom Query" on the left-hand side and choose the billing project. This is the project over which the costs for the queries are billed. The Custom SQL Query is entered in the field on the right side. As you can see, the query contains the period placeholders. Now you only have to select "Enable date parameters". Then you have to click on "Connect" in the upper right corner.

More information can be found in the official Google Data Studio documentation.
https://support.google.com/datastudio/answer/6370296

The next step is to configure the fields that the SQL statement queries to work with Google Data Studio. In most cases you can work with the automatic configuration. This configuration can also be adjusted later. You can directly create a report with the data source by clicking the "Create Report" button in the upper right area.

More information can be found in the official Google Data Studio documentation.
https://support.google.com/datastudio/answer/6402048