How to import tables from multiple Google BigQuery namespaces/projects into Warehouse Catalog using MicroStrategy Workstation

Symptom

When creating a Google BigQuery connection and data source, it can be difficult to retrieve the table
s in the Warehouse Catalog. Error messages like the following are common:

Query failed to execute. Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [37000:70: on HSTMT] [Strategy][BigQuery] (70) Invalid query: Invalid project ID 'xxx-xxx.xxxx'. Project IDs must contain 6-63 lowercase letters, digits, or dashes. Some project IDs also include domain name separated by a colon. IDs must start with a letter and may not end with a dash.Query failed to execute. Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [37000:70: on HSTMT] [Strategy][BigQuery] (70) Invalid query: Syntax error: Expected end of input but got "-" at [1:547]Query failed to execute. Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [S1000:31750: on HSTMT] [Strategy][BigQuery] (31750) Dataset is not found. Not found: Dataset xxxx-xxxx:xxxx-xxxx was not found in location US

Cause

As per KB484010 , Strategy supports BigQuery multiple projects connect. However, according to Product Help: Use Separate Billing and Data Projects : “the JDBC driver is faster and natively supports listing across multiple projects. The Google BigQuery ODBC driver does not natively support multiple projects”. For this and other reasons, Strategy suggests using a JDBC connection over an ODBC one for BigQuery.
In addition to using a JDBC connection, it is necessary to know exactly to set it up in order for it to work.
 

Action

To properly create a GBQ source connection and import tables from different data projects:

1. In Workstation, go to “Data Sources” under “Administration” and click the plus (+) sign:

1. Select “Google Big Query” (in 2021 Update 9 and newer), or “Google Big Query (JDBC)” (in previous versions).

1. Fill the required fields. “Projects” at the bottom refers to MSTR projects, not GBQ ones.

1. Use an existing GBQ connection or create a new one if needed.
   
1. To create a new connection, click “New…” and fill the fields in the new window. Select one of the three auth methods and write the appropriate GBQ billing project.

1. After the data source is created, go to the Warehouse Catalog by opening “Schema” under “Modeling”. Right-click your project and select “Edit Schema”:

1. In the catalog, find your connection, right-click, and select “Edit Catalog Options”. Use the following values to fill the options.

1. Catalog Read Mode: Data source SQL
2. Retrieve namespaces available in data source: (Leave blank)
3. Warehouse catalog table and column list retrieval SQL statement:

SELECT DISTINCT table_schema AS name_space, table_name AS tab_name, column_name AS col_name, data_type, CASE WHEN data_type = 'BOOL' THEN 1 WHEN data_type IN ('DATE', 'DATETIME', 'FLOAT64', 'INT64', 'TIME', 'TIMESTAMP') THEN 8 WHEN data_type = 'NUMERIC' THEN 16 ELSE -1 END AS data_len, CASE WHEN data_type = 'FLOAT64' THEN 53 WHEN data_type = 'INT64' THEN 64 WHEN data_type = 'NUMERIC' THEN 38 ELSE null END AS data_prec, CASE WHEN data_type = 'INT64' THEN 0 WHEN data_type = 'NUMERIC' THEN 9 ELSE null END AS data_scale FROM #DATABASE_NAME#.INFORMATION_SCHEMA.COLUMNS​​​​​​​

1. Replace #DATABASE_NAME# with your qualified dataset name. In GBQ, that is a data project and a dataset that’s inside of it.
   e.g.: FROM data-project.dataset_name.INFORMATION_SCHEMA.COLUMNS

1. Connect to the source and import the required tables from the namespace used in the retrieval SQL statement (6.d)
2. Right-click the table and select the “Change name prefix” option. Use the same namespace used in the retrieval SQL.
3. Repeat steps 6 to 8 to add tables from different projects as needed. The read SQL statement is just for importing tables into projects. The table prefix is used for report execution. So, changing the retrieval statement won’t affect the tables that are already added to the catalog.

NOTE: You may also be able to add tables from different namespaces without having to change the retrieval SQL statement. But do set the proper prefixes for them.