BigQuery is Google’s serverless data warehouse in Google Cloud. Power BI can consume data from various sources including RDBMS, NoSQL, Could, Services, etc. It is also easy to get data from BigQuery in Power BI.
In this article, I am going to demonstrate how to connect to BigQuery to create visuals.
Prerequisites
Google Cloud account is required. You can register a trial account.
In BigQuery, there is a public dataset named world_bank_intl_debt in project bigquery-public-data. We are going to use table international_debt to create some visual.
The details about this table is available here: https://bigquery.cloud.google.com/table/bigquery-public-data:world_bank_intl_debt.international_debt?pli=1&tab=details
Cost of querying public data sets
Public data sets are paid by Google for storage but you need to pay for querying it.
Connect to BigQuery in Power BI
Open Power BI and create a new file.
In the Home tab and click Get Data button.
In the Database tab of****the opened window, select “Google BigQuery”.
https://api.kontext.tech/resource/4e7bbfba-f4a6-5f59-84f9-4e1c66a37283
Click Connect button to continue.
Click Sign in button to sign into your Google Could account.
https://api.kontext.tech/resource/6a5334eb-e8b1-5420-99dd-7f3b250d0634
In the opened window, click Allow button to allow Power BI Desktop to view and manage your data in Google BigQuery:
https://api.kontext.tech/resource/5ffccdd5-e186-5013-ae87-b0cb853a0b9d
Click connect button once signed in to continue.
Select the Required Data Tables
The hierarchy of BigQuery is: Project -> DataSet -> Table.
In the opened window Navigator, expand bigquery-public-data project.
https://api.kontext.tech/resource/e2350068-c193-5a33-af3a-9531b402daf8
For this tutorial, we just need international_debt table under world_bank_intl_debt dataset.
https://api.kontext.tech/resource/32acd7fe-5217-5d3f-9689-1a62fedc592b
Click Load button to load the data.
And then you can setup Connection settings. In this case, let’s choose Import which will bring a copy of the data into Power BI.
Please note you will pay for querying the data. There are 1,359,644 records in this table. You can customize the query to only retrieve sample data to reduce the cost.
https://api.kontext.tech/resource/82307d6f-ba62-555a-8275-158a0e9cc97a
Once imported, the following fields are available to use:
https://api.kontext.tech/resource/48cd1f34-7c08-5b28-a3f6-6e2d88ab90dd
Create a visual using the data imported
With the data available, we can now easily create a line chart by using field year as Axis and field value as Values.
https://api.kontext.tech/resource/3cafd164-073b-5552-a6e0-b92afd5c650b
You can create as many visuals as you can do with any other data sources.
https://api.kontext.tech/resource/64512d61-9f53-59a2-b439-7a76232c2dbe
Summary
It is very easy to consume Google BigQuery data in Power BI. You can create joins when drafting the queries or implement within Power BI.
For performance and cost consideration, you may choose to physicalise some data in BigQuery and then query the aggregated data into Power BI.