Connecting Google BigQuery in Power BI using ODBC Driver

[daniel]

Introduction

This article will guide you through connecting Power BI to the Google Query API using the ZappySys ODBC API Driver. By leveraging the ZappySys ODBC Google Query connector, you can seamlessly access and manage Google Query data for deeper analysis and reporting within Power BI. Whether you need to monitor file activity, analyze folder structures, or extract metadata, this integration provides a robust and flexible solution for handling your Google Query data.

Following the steps outlined in this guide, you will learn how to set up the connection, configure the ODBC driver, and import Google Query data into Power BI for enhanced reporting and data analysis.

Prerequisites

• ODBC PowerPack: Download and install the ZappySys ODBC PowerPack from the Customer Download Area or the trial version.
• Googlel account.

Steps

Create/Select a Project in the Google API Console

1. Navigate to Google API Console.

2. Click on the Project Dropdown at the top bar and either select an existing project or create a new one by clicking CREATE PROJECT.

   

3. Once the project is set, click ENABLE APIS AND SERVICES.

4. Enable both BigQuery Connection API and Cloud Resource Manager API by searching for them and clicking ENABLE.
   

5. Return to the main screen and click on the OAuth Consent Screen tab. Provide the necessary details and save.

6. Move to the Credentials tab.

7. Click CREATE CREDENTIALS in the top bar, choose OAuth Client ID, select Desktop App as the Application Type, and click Create to obtain your Client ID and Secret.
   

   

Create a New Driver

1. Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
   

2. Go to the User DSN or System DSN tab to access the data source for yourself or other users. Add a new System DSN for SQL Server Integration by clicking the “Add” button.
   

3. Select ZappySys API Driverfrom the driver list, then choose Google BigQuery in the connector list and press Continue.
   

   

Google Query Connector Configuration

1. Fill in the connector fields, including Client ID, Client Secret, ProjectId, DatasetId, and Scopes (separated by spaces). Then, generate the token and test the connection.
   

   

2. Go to the Preview Tab, select any table, and preview the result. Press OK to save the configuration.
   

   

Read Google Query Data in Power BI using ODBC

1. Once you open Power BI Desktop, click Get Data to retrieve data from ODBC.
   

   

2. Search for “ODBC” in the opened window to get data from the ODBC data source.
   

3. Select the Data Source created earlier, such as Google QueryDSN, and continue.
   

   

4. You may be asked to authenticate the new DSN. Select Windows Authentication along with Use my current credentials.
   

   

5. Select a table or view to import the data, then load it into Power BI.
   

   

6. Finally, use the extracted Google Query data in a Power BI report.
   

   

Import Google Query Data into Power BI using SQL Query

If you prefer to import Google Query data using an SQL query instead of selecting a table name, you can use advanced options during the import steps. After choosing the DSN, click on Advanced options to access the SQL Query editor.

Considerations

Save your credentials to avoid searching for them in your account every time.

Video tutorial

API ODBC Driver - Read/Write data from API Data Sources using predefined Connector File

Conclusion

Following these steps, you can seamlessly connect Power BI to Google Query using the ODBC API Driver, leveraging ZappySys tools for efficient data retrieval and integration. If you need assistance or have inquiries, please get in touch with our support team via chat on our website.

References

For more details, check our Google Query connector article here.