Temp Table Creation

Intermediate Table Type

The ability to generate multi-pass SQL is a key feature allowing for the efficient execution of complex business queries. They are temporary in nature and hold intermediate results. They are created, populated and dropped by MicroStrategy. MicroStrategy implements a list of strategies to generate intermediate tables the choice of which can have a significant impact on query performance.

MicroStrategy's SQL engine is capable of generating different SQL syntax in order to provide optimal support for any given RBDMS. The following sections discuss the default choices for PostgreSQL and potential alternatives.

Temporary Table

One way to implement multi-pass SQL is to execute each pass (for example, each query block) in a separate table. When the Intermediate Table Type VLDB property is set to True Temporary Table, each pass results in a local temporary table. The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other sessions. Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped. This is the default setting for PostgreSQL.

SQL statement example:

CREATE UNLOGGED TABLE TZZZT7U1FSP000( 
"C_CUSTKEY" INTEGER,  
"WJXBFS1" DOUBLE PRECISION) 
 insert into TZZZT7U1FSP000  
select "a11"."O_CUSTKEY" AS "C_CUSTKEY", 
sum("a11"."O_TOTALPRICE") AS "WJXBFS1" 
from "ORDERS" "a11" 
where "a11"."O_CUSTKEY" <=  5 
group by "a11"."O_CUSTKEY" 
CREATE UNLOGGED TABLE TVC80884ZSP001( 
"C_CUSTKEY" INTEGER,  
"WJXBFS1" DOUBLE PRECISION) 
 insert into TVC80884ZSP001  
select "a11"."C_CUSTKEY" AS "C_CUSTKEY", 
"a11"."C_ACCTBAL" AS "WJXBFS1" 
from "CUSTOMER" "a11" 
where "a11"."C_CUSTKEY" <=  5 
 select "pa11"."C_CUSTKEY" AS "C_CUSTKEY", 
"a13"."C_NAME" AS "C_NAME", 
"pa11"."WJXBFS1" AS "WJXBFS1", 
"pa12"."WJXBFS1" AS "WJXBFS2" 
from TZZZT7U1FSP000                 "pa11" 
join          TVC80884ZSP001 "pa12" 
  on  ("pa11"."C_CUSTKEY" = "pa12"."C_CUSTKEY") 
join          "CUSTOMER" "a13" 
  on  ("pa11"."C_CUSTKEY" = "a13"."C_CUSTKEY") 
drop table TZZZT7U1FSP000 
drop table TVC80884ZSP001

Derived Tables

Derived table syntax is another option for the intermediate table type. Rather than implementing each SQL pass in a separate table, the derived table syntax allows the SQL engine to issue additional passes as query blocks in the FROM clause. Instead of issuing multiple SQL passes that create intermediate tables, the SQL engine generates a single large pass of SQL. This allows queries to run faster since there are no CREATE TABLE or DROP TABLE statements to catalog, no corresponding locks on the system tables, and no logging of records inserted into a physical table. 

You may want to experiment with this option to determine if it is beneficial in your specific environment. 

SQL statement example:

select "pa11"."C_CUSTKEY" AS "C_CUSTKEY", 
"a13"."C_NAME" AS "C_NAME", 
"pa11"."WJXBFS1" AS "WJXBFS1", 
"pa12"."WJXBFS1" AS "WJXBFS2" 
from (select "a11"."O_CUSTKEY" AS "C_CUSTKEY", 
sum("a11"."O_TOTALPRICE") AS "WJXBFS1" 
from         "ORDERS" "a11" 
where      "a11"."O_CUSTKEY" <=  5 
group by "a11"."O_CUSTKEY" 
)               "pa11" 
join          (select "a11"."C_CUSTKEY" AS "C_CUSTKEY", 
"a11"."C_ACCTBAL" AS "WJXBFS1" 
from         "CUSTOMER" "a11" 
where      "a11"."C_CUSTKEY" <=  5 
)               "pa12" 
  on  ("pa11"."C_CUSTKEY" = "pa12"."C_CUSTKEY") 
join          "CUSTOMER" "a13" 
  on  ("pa11"."C_CUSTKEY" = "a13"."C_CUSTKEY")

Note that not all reports are able to use derived tables. There are two primary scenarios in which temporary tables must be used instead of derived tables:

• When a report uses a function supported in the MicroStrategy analytical engine that is not supported in PostgreSQL. For example, many of the functions in the financial and statistical function packages.  If these functions are used in intermediate calculations, the MicroStrategy analytical engine performs calculations and then inserts records back into the RDBMS for further processing. Inserting records back into PostgreSQL requires a temporary table.

• When a report uses the MicroStrategy partitioning feature.  When using partitioning, the SQL engine executes a portion of the query to determine which partitions to use. The results are then used to construct the rest of the query. Since the full structure of the query is not known prior to execution, the SQL engine must use temporary tables to execute the query in multiple steps.

These situations do not cover 100% of the cases in which temporary tables must be used.  The rest of the cases are relatively obscure combinations of VLDB settings, such as certain combinations of sub query type plus outer join settings on metrics plus non-aggregable metrics.

If the intermediate table type is set to derived tables, then the fallback table type should be set as true temporary table, which is the default value or permanent table. If the value is set as fail report then the report fails with error.  For each report, the SQL engine follows an "all or nothing" policy in determining whether to use derived table syntax. If the entire report cannot be resolved in a single statement with derived tables, the SQL engine automatically reverts to the fallback table type syntax and does not use derived table syntax for the report.

Common Table Expressions

Rather than implementing each pass in a separate table, common table expression syntax allows the SQL engine to issue additional passes as query blocks in the WITH clause. The WITH clause defines a name for a query block that can be referenced as a table name in any FROM clause of the full SELECT statement that follows. Common table expressions provide a way to perform many operations within a single SQL statement.  As with derived table syntax, there are no CREATE TABLE or DROP TABLE statements to catalog, corresponding locks on the system tables, or logging of records inserted into a physical table.

You may want to experiment with this option to determine if it is beneficial in your specific environment.

with  gopa1 as 
 (select "a11"."O_CUSTKEY" AS "C_CUSTKEY", 
sum("a11"."O_TOTALPRICE") AS "WJXBFS1" 
from         "ORDERS" "a11" 
where      "a11"."O_CUSTKEY" <=  5 
group by "a11"."O_CUSTKEY" 
),  
 gopa2 as 
 (select "a11"."C_CUSTKEY" AS "C_CUSTKEY", 
"a11"."C_ACCTBAL" AS "WJXBFS1" 
from         "CUSTOMER" "a11" 
where      "a11"."C_CUSTKEY" <=  5 
)select     "pa11"."C_CUSTKEY" AS "C_CUSTKEY", 
"a13"."C_NAME" AS "C_NAME", 
"pa11"."WJXBFS1" AS "WJXBFS1", 
"pa12"."WJXBFS1" AS "WJXBFS2" 
from gopa1 "pa11" 
join          gopa2 "pa12" 
  on  ("pa11"."C_CUSTKEY" = "pa12"."C_CUSTKEY") 
join          "CUSTOMER" "a13" 
  on  ("pa11"."C_CUSTKEY" = "a13"."C_CUSTKEY")

Temporary View

Temporary view is also another option for the intermediate table type. In PostgreSQL, a view is a virtual table based on the result set of a SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

SQL functions and WHERE and JOIN statements can be added to a view and present the data as if the data were coming from one single table.

In MicroStrategy, a view can be created if temporary view has been chosen as the intermediate table type. At the end of the SQL statement, those views that are created are dropped. Here is an example when temporary view is enabled.

create view TPU6E4K05SP000 ("C_CUSTKEY", "WJXBFS1") as
select "a11"."O_CUSTKEY" AS "C_CUSTKEY",
sum("a11"."O_TOTALPRICE") AS "WJXBFS1"
from "ORDERS" "a11"
where "a11"."O_CUSTKEY" <=  5
group by "a11"."O_CUSTKEY"
create view TLIIQ4U85SP001 ("C_CUSTKEY", "WJXBFS1") as
select "a11"."C_CUSTKEY" AS "C_CUSTKEY",
"a11"."C_ACCTBAL" AS "WJXBFS1"
from "CUSTOMER" "a11"
where "a11"."C_CUSTKEY" <=  5
select "pa11"."C_CUSTKEY" AS "C_CUSTKEY",
"a13"."C_NAME" AS "C_NAME",
"pa11"."WJXBFS1" AS "WJXBFS1",
"pa12"."WJXBFS1" AS "WJXBFS2"
from TPU6E4K05SP000                 "pa11"
join          TLIIQ4U85SP001 "pa12"
on  ("pa11"."C_CUSTKEY" = "pa12"."C_CUSTKEY")
join          "CUSTOMER" "a13"
on  ("pa11"."C_CUSTKEY" = "a13"."C_CUSTKEY")
drop view TPU6E4K05SP000
drop view TLIIQ4U85SP001

Index Creation on Intermediate Tables

Most intermediate tables are either joined on the attribute columns back to fact tables (for example, metric qualification) or serve as temporary storage for metrics that are joined with other intermediate tables to produce a final result.

MicroStrategy doesn't create primary or secondary indexes on intermediate tables by default. However, creating an index on the attribute columns may improve performance, since those columns will usually be involved in a join later in the report SQL.

If you are using temporary tables as the intermediate table type and have large intermediate tables, consider setting the intermediate table index VLDB setting to create an index on the attribute columns of each intermediate table. This setting can also be used to create indexes on the temporary or permanent tables created as part of the fallback table types for derived table syntax reports and common table expression.