MicroStrategy ONE

Support Multiple BigQuery Billing Projects in MicroStrategy

Billing projects help with distributing the cost of the queries among certain departments. See Export Cloud Billing Data to BigQuery for more information.

This procedure assumes that you have two MicroStrategy groups created and each group has at least one user with a data import privilege. In this procedure there are two groups, Developers and Managers. The Developers group has a Developer user while the Managers group has a Manager user.

First, you create a GBQ_JDBC database instance, GBQ_JDBC_Developer_Connection and GBQ_JDBC Manager_Connection database connections. Each of these database connections use a connection string with a different billing project.

Next, use MicroStrategy connection mapping to map the Developers group to the GBQ_JDBC_Developer_Connection and the Managers group to GBQ_JDBC Manager_Connection connections. When a Developer user logs in and queries GBQ_JDBC, the billing project set up in the GBQ_JDBC_Developer_Connection connection string is used for billing. When the Manager user logs in and queries GBQ_JDBC, the billing project set up in the Manager_Connection connection string is used for billing. This procedure uses the Google BigQuery JDBC driver, but the Google BigQuery ODBC driver can be used as well.

Get started using the following procedures:

  1. Create Data Sources

  2. Map Connections

Create Data Sources

  1. Open the Workstation window.

  2. In the Navigation pane, click Data Sources.

  3. Make sure the correct environment is selected in the top left dropdown.

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

  5. Make sure the correct environment is selected and click Google BigQuery.

  6. Enter the Name and select the Projects you want to use in this data source.

  7. In Default Database Connection, click Add New Database Connection.

  8. Enter the Name, select JDBC as the Driver Type, enter the Billing Project for the first group of users (Developers) and select either Token or OAuth authentication. In this example, OAuth is used as the Authentication Mode, so you must select an Authentication Service as well. The Google IdP Authentication Service in this example was created using the Enterprise Security section of the Navigation pane.

  9. Click Save.

  10. In Default Database Connection, click Add New Database Connection.

  11. Enter the Name, select JDBC as the Driver Type, enter the Billing Project for the second group of users (Managers) and select either Token or OAuth authentication. In this example, OAuth is used as the Authentication Mode, so you must select an Authentication Service as well. The Google IdP Authentication Service in this example was created using the Enterprise Security section of the Navigation pane.

  12. Click Save.

Map Connections

  1. In the Navigation pane, click Projects.

  2. Right-click the project to which you linked the data source in this step and choose Properties.

  3. In the left pane, click Connection Mapping.

  4. Click Connection Map.

  5. In the grid, select the Data Source and Database Connection for the first group that you created in this step. Database Login is not used by Google BigQuery, Therefore, you cannot select one.

  6. Under Users/Groups, click Add.

  7. Select users and/or group of users to use for your first database connection and click Add. In this example, you want all users in the Developers group to be mapped to the first (Developer) database connection.

  8. Click Connection Map.

  9. In the grid, select the Data Source and Database Connection for the second group that you created in this step. Database Login is not used by Google BigQuery, Therefore, you cannot select one.

  10. Under Users/Groups, click Add.

  11. Select users and/or group of users to use for your second database connection and click Add. In this example, you want all users in the Managers group to be mapped to the second (Manager) database connection.

  12. Click OK.