VLDB Settings For Temporary Tables

Temporary tables are needed by MicroStrategy to store intermediate data.  You can choose the table type and kind using the VLDB properties.

Derived Tables

This is a nested select statement, and it does not physically exist. It is created during the execution of the sql and it utilize the database memory for creating this virtual table.  This makes use of the database memory, and you will need to check the performance by testing both types.

The syntax is like select a,b from (select c,d from table)

You might have noticed that certain reports cannot be resolved by the use of subquery in derived tables. These reports include the following.

1. Creation of datamarts
2. Query partitioned tables
3. Reports with Custom Groups
4. Reports with Percentage Ranking
5. Reoprts with analytical functions supported by MicroStrategy Analytical Engine and not the Database

Common Table Expression

example SQL .

with TEMP1 as

(select a21.STORE_NBR STORE_NBR,
(sum(a21.REG_SLS_DLR) + sum(a21.PML_SLS_DLR)) TOT_SLS_STR,
sum(a21.PML_SLS_DLR) PRO_STR
from STORE_DIVISION a21
where a21.STORE_NBR = 1
group by a21.STORE_NBR),
TEMP2 as
(select a22.REGION_NBR REGION_NBR, ............

The advantage here is that you can create the temp table once and refer it multiple times.

You can change the table type using the VLDB Property ->  Tables -> Intermediate Table Type .

Implicit Table creation Vs. Explicit Table creation.

Example of Implicit table creation
create table TEMP1 as
select a21.STORE_NBR STORE_NBR,
(sum(a21.REG_SLS_DLR) + sum(a21.PML_SLS_DLR)) TOT_SLS,
sum(a21.PML_SLS_DLR) PROMO_SLS
from STORE_DIVISION a21
where a21.STORE_NBR = 1
group by a21.STORE_NBR

Example of Explicit table creation
create table TEMP1 (
STORE_NBR INTEGER,
TOT_SLS DOUBLE,
PROMO_SLS DOUBLE)
insert into TEMP1
select a21.STORE_NBR STORE_NBR,
(sum(a21.REG_SLS_DLR) + sum(a21.PML_SLS_DLR)) TOT_SLS,
sum(a21.PML_SLS_DLR) PROMO_SLS
from STORE_DIVISION a21
where a21.STORE_NBR = 1
group by a21.STORE_NBR


You need to test and find out which of these methods provides an optimal performance. Generally there is not much difference in the excution plan between these two types, (I have not lab tested this), however  according to Dr. Tim Hall   implicit cursor is kind of faster.

You can access this setting at VLDB ->  Tables -> Table Creation Type

Another setting you might be interested to check would be "Drop Temp Table Method" . Certain cases when you use global temporary tables on Oracle, the tables need to be empty before you can drop it. Otherwise oracle will throw error.