MicroStrategy ONE

Create an ODBC DSN-less Connection

You can create an ODBC DSN-less connection for Google BigQuery. However, MicroStrategy recommends using JDBC connection. To set up JDBC-less connection, see 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.

  • 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 (Driver) connector from the data source list.

  5. Select the Select Tables 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 (Driver).

      On Mac, choose Google BigQuery.

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

    • Enter a catalog and data source name.

      The specified catalog will also be your billing project.

  8. To add additional parameters, click Show connection string and select the Edit connection string checkbox.

  9. In the Connection String field, locate the desired parameter.

  10. If the parameter is present, change its value.

  11. If the parameter is not present, append the parameter to the end of the connection string. When appending new parameters to the connection string, separate them from the previous parameter with a semicolon. For example, DefaultStringColumnLength=4000;EnableHTAPI=1. The following are additional parameters:

    • EnableHTAPI enables the high throughput API or faster data transfer. For example, EnableHTAPI=1;.

    • AdditionalProjects allows loading datasets from multiple projects. The value for this parameter is a comma separated list of additional projects. The final list of datasets contains all datasets from the default project and the additional projects listed as a value of this parameter. For example, AdditionalProjects=MicroStrategyProject1,MicroStrategyProject2,MicroStrategyProject3;.

  12. Click Save.

  13. Complete the required fields for setting OAuth parameters.

    • From the Authentication Type drop-down, choose Google.

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

  14. Click OK. The Google BigQuery ODBC DSN-less data source is created.

  15. Click the Google BigQuery ODBC DSN-less data source. The Google sign-in page appears.

  16. Log into your Google account.

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

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

  19. Click Allow.

The following section applies to Windows only.

  1. Generate an OAuth refresh token on 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 (Driver) 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 (Driver).

    • 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. See Generate a Refresh Token to Access Google BigQuery on Windows for more information.

  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 
    Driver={MicroStrategy Google BigQuery ODBC Driver Client};RefreshToken=OAUTH_REFRESH_TOKEN;ClientID=977385342095.apps.googleusercontent.com;ClientSecret=wbER7576mc_1YOII0dGk7jEE;Catalog=PROJECT_ID;SQLDialect=1;OAuthMechanism=1;DefaultStringColumnLength=4000;EnableHTAPI=0
  12. Copy the modified connection string and paste it in the Connection String field.

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

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