Link MicroStrategy Jobs with Google BigQuery Jobs Using Query Labels

To help organize your Google BigQuery resources, you can add labels to your datasets, tables, and views. After labeling your resources, you can search for them based on label values.

Linking MicroStrategy jobs with Google BigQuery jobs using query labels allows better tracking of long-running queries, performance analysis and optimization by grouping queries with the same purpose, environment, department, and so on. This allows you to optimize your Google BigQuery costs.

1. Add a Report Pre-Statement VLDB setting in the database instance or at the report level.

   Copy

   SET @@query_label = lower("Project:!p,GUID:!r,Job:!j") 

   The SET command only works in Google BigQuery when using a JDBC driver and enabling session mode. Here is an example of a JDBC connection string:

   Copy

   JDBC;DRIVER={com.microstrategy.googlebigquery.jdbc42.Driver};URL={jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=your-project;OAuthType=0;OAuthServiceAcctEmail=your-serviceaccount;OAuthPvtKeyPath=your-privatekey;Timeout=6000;Location=your-location;EnableHighThroughputAPI=0;HighThroughputMinTableSize=100000000;EnableSession=1;useQueryCache=1};

2. Check that the Google BigQuery job is linked with MicroStrategy labels.

   Copy

   SELECT 
     creation_time, 
     job_id, 
     parent_job_id, 
     query_labels, 
     query, 
     total_bytes_billed 
     FROM `YourProject.region-us.INFORMATION_SCHEMA.JOBS_BY_USER` 
      left join UNNEST(labels) AS query_labels 
   WHERE 
     query_labels IS NOT NULL