Connect to Google BigQuery in Workstation

Starting in MicroStrategy 2021 Update 8, you can connect to Google BigQuery using Workstation.

In Workstation, you can use both Google BigQuery JDBC or ODBC connections with OAuth or Token authentication mode. MicroStrategy recommends using a Google BigQuery JDBC connection since JDBC performs significantly better than the ODBC driver. In addition, JDBC uses one billing project, but allows clients to access all Google BigQuery projects to which the user has access. Before following this procedure, make sure you have done one of the following:

• For Token authentication, ensure you have the client ID, client secret, and a valid refresh token. See one of the following topics to generate the refresh token:

  • Generate a Refresh Token to Access Google BigQuery on Linux

  • Generate a Refresh Token to Access Google BigQuery on MacOS

  • Generate a Refresh Token to Access Google BigQuery on Windows

• For OAuth authentication, you must create an IAM object with Google IdP in Enterprise Security.

Make sure that your user or service account has at least the following Google BigQuery roles assigned at the project level.

• BigQuery Data Owner

• BigQuery Job User

• BigQuery User

It may be possible to assign these roles on the dataset or table level. However, MicroStrategy has not tested these other scenarios. To learn more about granting roles to an user, see Manage access to projects, folders, and organizations in the Google IAM documentation.

See KB485784 to troubleshoot any connection issues.

1. Open the Workstation window.

2. In the Navigation pane, click , next to Data Sources.

3. Click Google BigQuery.

   

4. In Default Database Connection, choose Add New Database Connection.

   

5. Name your database connection and select either the JDBC or ODBC driver type. Keep in mind that MicroStrategy recommends JDBC. Enter the Google Billing Project.

   

6. In Authentication Mode, choose Token or OAuth.

   1. For token authentication, enter the Simba client ID (977385342095.apps.googleusercontent.com), Simba’s client secret (wbER7576mc_1YOII0dGk7jEE), and the refresh token you previously generated.

      

   2. For OAuth authentication, select the IAM object created using the Workstation Enterprise Security.

      

7. Click Save.

8. Name the data source, associate the data source with the applicable Projects, and click Save.

   

Related Topics

KB484010: Technical Considerations: MicroStrategy and BigQuery