Take Advantage of Temporary Tables in Google BigQuery

Starting in MicroStrategy ONE Update 11, MicroStrategy supports temporary tables for Google BigQuery.

Temporary tables help improve query performance and cost optimization. Splitting up a complex query allows for materializing intermediate results in variables or temporary tables. You can then use these intermediate results in other parts of the query. It is increasingly useful when these results are needed in more than one place of the query. You are not charged for storage of temporary tables.

1. Create a Temp Table Pattern and Temp Implicit Create Pattern specified for Google BigQuery and make sure they have been included in your live metadata update.

   Sample SQL:

   Copy

   CREATE TEMP TABLE TE8B9BINQMD000(
         `S_SUPPKEY`    INT64,
         `WJXBFS1`    FLOAT64,
         `WJXBFS2`    INT64)

2. MicroStrategy recommends using temporary tables along with the following VLDB settings:

   • Intermediate Table Type – True Temp Table

   • Table Creation Type – Explicit

3. You must use "EnableSession=1" in your ODBC/JDBC connection properties to support temporary tables.