MicroStrategy ONE

Create a JDBC DSN-less Connection

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.

  • Web
  • Workstation

  1. Log into MicroStrategy Web on Linux or Windows.

  2. Navigate to the Home page.

  3. Click Create > Add External Data.

  4. Find and select the Google BigQuery (JDBC) connector from the data source list.

    On Mac, select Google BigQuery.

  5. Choose an import option and click Next.

  6. Next to Data Sources, click New Data Source to add a new connection.

  7. Enter your data source information.

    • The DSN-less Data Sources option must be selected.

      On Mac, skip the DSN-less Data Sources verification.

    • From the Database drop-down, choose Google BigQuery (JDBC).

      On Mac, choose Google BigQuery.

    • From the Version drop-down, choose Google BigQuery.

    • Enter a data source name.

  8. Click Save.

  9. In the Set OAuth Parameters dialog, ensure Google is selected as the authentication type.

    If you do not have your client ID or secret, or need to authorize your callback URL, see the Google documentation.

  10. Log into your Google account.

  11. If the "This app isn't verified" screen appears, click Advanced.

  12. Click Go to microstrategy.com (unsafe).

  1. Generate an OAuth refresh token to access Google BigQuery on MacOS or Windows.

  2. Open MicroStrategy Workstation.

  3. In the Navigation pane, click , next to Dashboards.

  4. Click New Data.

  5. Find and select the Google BigQuery (JDBC) connector from the data source list.

  6. Select the Select Tables import option and click Next.

  7. Next to Data Sources, click New Data Source to add a new connection.

  8. Enter your data source information.

    • The DSN-less Data Sources option must be selected.

    • From the Database drop-down, choose Google BigQuery (JDBC).

    • From the Version drop-down, choose Google BigQuery.

    • Enter a catalog and data source name.

  9. Obtain your project ID, which is also your billing project, and OAuth refresh token from step 1.

  10. Click Show connection string and select the Edit connection string checkbox.

  11. Modify the following connection string template where OAUTH_REFRESH_TOKEN is your OAuth refresh token and PROJECT_ID is your project ID.

    Copy 
    JDBC;DRIVER={com.microstrategy.googlebigquery.jdbc42.Driver};URL={jdbc:bigquery://https://www.googleapis.com/bigquery/v2;ProjectId=PROJECT_ID;OAuthType=2;OAuthRefreshToken=OAUTH_REFRESH_TOKEN;OAuthClientId=977385342095.apps.googleusercontent.com;OAuthClientSecret=wbER7576mc_1YOII0dGk7jEE;Timeout=600;EnableHighThroughputAPI=1};
  12. Copy the modified connection string and paste it in the Connection String field.

  13. Click Save. The Google BigQuery (JDBC) data source is created.

  14. Click on the new data source to load the list of all namespaces.