Strategy ONE

Switch Catalog Retrieval from JDBC/ODBC to SQL Query Mode

SQL catalog retrieval may be faster and more versatile (for example you can filter resulting set) than JDBC/ODBC mode. However, keep in mind that Google BigQuery charges for every executed SQL query and therefore; SQL catalog retrieval may increase your Google bill.

To use SQL retrieval with Google BigQuery, the user must have the BigQuery Metadata Viewer role. To learn more about granting roles to a user, see Manage access to projects, folders, and organizations in the Google Identity and Access Management (IAM) help.

  • Web
  • Workstation

  1. If you are using MicroStrategy 2021 Update 7 or older, use the following topics to enable SQL catalog retrieval first:

  2. Create a Google BigQuery data source using one of the following procedures:

  3. Log into Strategy Web.

  4. Navigate to the Home page.

  5. Click Create > Add External Data.

  6. Search for and select the Google BigQuery (Driver) for ODBC or Google BigQuery (JDBC) driver.

  7. Choose Select Tables and click Next.

  8. Hover over the data source, click the arrow, and choose Edit Catalog Options.

  9. Select Data source SQL to switch from ODBC mode to SQL catalog retrieval. If you want to adjust the SQL queries, unselect Use default value to modify them.

  10. Use the following templates to replace the SQL queries, based on your version, along with your Namespace and Caching selections. Make sure to use your Google project ID instead of 'project-id'

    Current only namespaces and Table Names only caching:

    • Retrieve namespaces available in data source

      Copy 
      SELECT DISTINCT
        table_catalog || '.' || table_schema AS name_space
      FROM `project-id`.`region-us`.INFORMATION_SCHEMA.TABLES
      ORDER BY 1

    • SQL statement to retrieve tables available in the data source

      Copy 
      SELECT DISTINCT
        table_catalog || '.' || table_schema AS name_space,
        table_name AS tab_name
      FROM `project-id`.`region-us`.INFORMATION_SCHEMA.TABLES
      ORDER BY 1, 2

    • SQL statement to retrieve columns for the selected tables

      Copy 
      SELECT DISTINCT
        table_catalog || '.' || 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 `project-id`.`region-us`.INFORMATION_SCHEMA.COLUMNS
        WHERE table_name IN (#TABLE_LIST#)
        ORDER BY 1, 2, 3

    Current only namespaces and Table and column names caching:

    • Retrieve namespaces available in data source

      Copy 
      SELECT DISTINCT
        table_catalog || '.' || table_schema AS name_space
      FROM `project-id`.`region-us`.INFORMATION_SCHEMA.TABLES
      ORDER BY 1

    • SQL statement to retrieve columns for the selected tables

      Copy 
      SELECT DISTINCT
        table_catalog || '.' || 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 `project-id`.`region-us`.INFORMATION_SCHEMA.COLUMNS
        ORDER BY 1, 2, 3

    All namespaces and Table names only caching:

    • Retrieve namespaces available in data source
      Do not change this input box.

    • SQL statement to retrieve tables available in the data source

      Copy 
      SELECT DISTINCT
        table_catalog || '.' || table_schema AS name_space,
        table_name AS tab_name
      FROM `project-id`.`region-us`.INFORMATION_SCHEMA.TABLES
      ORDER BY 1, 2

    • SQL statement to retrieve columns for the selected tables

      Copy 
      SELECT DISTINCT
        table_catalog || '.' || 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 `project-id`.`region-us`.INFORMATION_SCHEMA.COLUMNS
        WHERE table_name IN (#TABLE_LIST#)
        ORDER BY 1, 2, 3

    All namespaces and Table and column names caching:

    • Retrieve namespaces available in data source
      Do not change this input box.

    • SQL statement to retrieve columns for the selected tables

      Copy 
      SELECT DISTINCT
        table_catalog || '.' || 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 `project-id`.`region-us`.INFORMATION_SCHEMA.COLUMNS
        ORDER BY 1, 2, 3

    Current only namespaces and Table Names only caching:

    • Retrieve namespaces available in data source

      Copy 
      SELECT DISTINCT
        table_schema AS name_space
      FROM `project-id`.`region-us`.INFORMATION_SCHEMA.TABLES
      ORDER BY 1

    • SQL statement to retrieve tables available in the data source.

      Copy 
      SELECT DISTINCT
        table_schema AS name_space,
        table_name AS tab_name
      FROM `project-id`.`region-us`.INFORMATION_SCHEMA.TABLES
      ORDER BY 1, 2

    • SQL statement to retrieve columns for the selected tables

      Copy 
      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 `project-id`.`region-us`.INFORMATION_SCHEMA.COLUMNS
        WHERE table_name IN (#TABLE_LIST#)
        ORDER BY 1, 2, 3

    Current only namespaces and Table and column names caching.

    • Retrieve namespaces available in data source

      Copy 
      SELECT DISTINCT
        table_schema AS name_space
      FROM `project-id`.`region-us`.INFORMATION_SCHEMA.TABLES
      ORDER BY 1

    • SQL statement to retrieve columns for the selected tables

      Copy 
      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 `project-id`.`region-us`.INFORMATION_SCHEMA.COLUMNS
        ORDER BY 1, 2, 3

    All namespaces and Table names only caching:

    • Retrieve namespaces available in data source
      Do not change this input box.

    • SQL statement to retrieve tables available in the data source

      Copy 
      SELECT DISTINCT
        table_schema AS name_space,
        table_name AS tab_name
      FROM `project-id`.`region-us`.INFORMATION_SCHEMA.TABLES
      ORDER BY 1, 2

    • SQL statement to retrieve columns for the selected tables

      Copy 
      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 `project-id`.`region-us`.INFORMATION_SCHEMA.COLUMNS
        WHERE table_name IN (#TABLE_LIST#)
        ORDER BY 1, 2, 3

    All namespaces and Table and column names caching:

    • Retrieve namespaces available in data source
      Do not change this input box.

    • SQL statement to retrieve columns for the selected tables

      Copy 
      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 `project-id`.`region-us`.INFORMATION_SCHEMA.COLUMNS
        ORDER BY 1, 2, 3

    If you select All in Namespaces, both queries change the '#?SCHEMA_NAME?#' placeholder to 'region-us'.

  11. The 'region-us' placeholder is actually a project, which holds catalog information about any selected dataset. If you have datasets in any other region, make sure to adjust the placeholder to the correct region. To find all Google regions, see Dataset locations in the Google BigQuery documentation.

  12. Click OK.

  1. Connect to Google BigQuery in Workstation.

  2. In the Navigation pane, click Schemas.

  3. Right-click the project you want to edit and choose Edit Schema.

  4. In the left pane, right-click the data source you created in step 1 and choose Edit Catalog Options.

  5. Select Data source SQL.

  6. Use the following templates to replace the SQL query, based on your version. Make sure to use your Google project ID instead of 'project-id'

    MicroStrategy 2021 Update 7 (JDBC)
    MicroStrategy 2021 Update 6 and older (JDBC/ODBC)

    Copy 
    SELECT DISTINCT
      table_catalog || '.' || 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 `project-id`.`region-us`.INFORMATION_SCHEMA.COLUMNS
      ORDER BY 1, 2, 3

    MicroStrategy 2021 Update 7 (ODBC)

    Copy 
    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 `project-id`.`region-us`.INFORMATION_SCHEMA.COLUMNS
      ORDER BY 1, 2, 3

  7. The 'region-us' placeholder is actually a project, which holds catalog information about any selected dataset. If you have datasets in any other region, make sure to adjust the placeholder to the correct region. To find all Google regions, see Dataset locations in the Google BigQuery documentation.

  8. Click OK.

Related Topics

KB484010: Technical Considerations: Strategy and BigQuery