MicroStrategy ONE

Preserve All Final Pass Result Elements

Preserve All Final Pass Result Elements is an advanced VLDB property that is hidden by default. For an introduction to this property, see Preserving Data Using Outer Joins. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The following Preserve All Final Pass Result Elements VLDB property settings determine how to outer join the final result, as well as the lookup and relationship tables:

  • If you choose the default Preserve common elements of final pass result table and lookup/relationship table option, the SQL Engine generates an equi-join. Therefore, you only see elements that are common to both tables.
  • If you choose the Preserve all final result pass elements option, the SQL Engine generates an outer join, and your report contains all of the elements that are in the final result set. When this setting is turned ON, outer joins are generated for any joins from the fact table to the lookup table, as well as to any relationship tables. This is because it is hard to distinguish which table is used as a lookup table and which table is used as a relationship table, the two roles one table often plays. For example, LOOKUP_DAY serves as both a lookup table for the Day attribute, as well as a relationship table for Day and Month.

    This setting should not be used in standard data warehouses, where the lookup tables are properly maintained and all elements in the fact table have entries in the respective lookup table. It should be used only when a certain attribute in the fact table contains more (unique) attribute elements than its corresponding lookup table. For example, in the example above, the Fact Table contains sales for five different stores, but the Store Table contains only four stores. This should not happen in a standard data warehouse because the lookup table, by definition, should contain all the attribute elements. However, this could happen if the fact tables are updated more often than the lookup tables.

  • If you choose the Preserve all elements of final pass result table with respect to lookup table but not relationship table option, the SQL Engine generates an inner join on all passes except the final pass; on the final pass it generates an outer join.
  • If you choose the Do not listen to per report level setting, preserve elements of final pass according to the setting at attribute level. If this choice is selected at attribute level, it will be treated as preserve common elements (that is, choice 1) option at the database instance, report, or template level, the setting for this VLDB property is used at the attribute level. This value should not be selected at the attribute level. If you select this setting at the attribute level, the VLDB property is set to the Preserve common elements of final pass result table and lookup table option.

    This setting is useful if you have only a few attributes that require different join types. For example, if among the attributes in a report only one needs to preserve elements from the final pass table, you can set the VLDB property to Preserve all final pass result elements setting for that one attribute. You can then set the report to the Do not listen setting for the VLDB property. When the report is run, only the attribute set differently causes an outer join in SQL. All other attribute lookup tables will be joined using an equal join, which leads to better SQL performance.

Levels at Which You Can Set This

Database instance, report, template, and attribute

Examples

The first two examples below are based on the Preserve All Final Pass Result Elements example above. The third example, for the Preserve all elements of final pass result table with respect to lookup table but not relationship table option, is a separate example designed to reflect the increased complexity of that option's behavior.

Example: Preserve common elements of final pass result table and lookup/relationship table

A report has Store and Dollar Sales on the template.

The "Preserve common elements of final pass result table and lookup table" option returns the following results using the SQL below.

Store Dollar Sales

East

5000

Central

8000

South

12000

 
select a11.Store_id Store_id,
 max(a12.Store) Store,
 sum(a11.DollarSls) WJXBFS1
from Fact a11
 join Store a12
 on (a11.Store_id = a12.Store_id)
group by a11.Store_id

Example: Preserve all final result pass elements

A report has Store and Dollar Sales on the template.

The "Preserve all final result pass elements" option returns the following results using the SQL below. Notice that the data for Store_IDs 4 and 5 are now shown.

Store Dollar Sales

East

5000

Central

8000

South

12000

 

3000

 

1500

 
select a11.Store_id Store_id,
 max(a12.Store) Store,
 sum(a11.DollarSls) WJXBFS1
from Fact a11
 left outer join Store a12
 on (a11.Store_id = a12.Store_id)
group by a11.Store_id

Example: Preserve all elements of final pass result table with respect to lookup table but not to relationship table

A report has Country, Metric 1, and Metric 2 on the template. The following fact tables exist for each metric:

CALLCENTER_ID Fact 1

1

1000

2

2000

1

1000

2

2000

3

1000

4

1000

 

EMPLOYEE_ID Fact 2

1

5000

2

6000

1

5000

2

6000

3

5000

4

5000

5

1000

The SQL Engine performs three passes. In the first pass, the SQL Engine calculates metric 1. The SQL Engine inner joins the "Fact Table (Metric 1)" table above with the call center lookup table "LU_CALL_CTR" below:

CALLCENTER_ID COUNTRY_ID

1

1

2

1

3

2

to create the following metric 1 temporary table, grouped by country, using the SQL that follows:

COUNTRY_ID Metric 1

1

6000

2

1000

 
create table ZZSP00 nologging as
select a12.COUNTRY_ID COUNTRY_ID,
 sum((a11.QTY_SOLD * a11.DISCOUNT))
WJXBFS1
from ORDER_DETAIL a11, 
 LU_CALL_CTR a12
where a11.CALL_CTR_ID = a12.CALL_CTR_ID
group by a12.COUNTRY_ID

In the second pass, metric 2 is calculated. The SQL Engine inner joins the "Fact Table (Metric 2)" table above with the employee lookup table "LU_EMPLOYEE" below:

EMPLOYEE_ID COUNTRY_ID

1

1

2

2

3

2

To create the following metric 2 temporary table, grouped by country, using the SQL that follows:

COUNTRY_ID Metric 2

1

10000

2

17000

 
create table ZZSP01 nologging as
select a12.COUNTRY_ID COUNTRY_ID,
 sum(a11.FREIGHT) WJXBFS1
from ORDER_FACT a11, 
 LU_EMPLOYEE a12
where a11.EMP_ID = a12.EMP_ID
group by a12.COUNTRY_ID

In the third pass, the SQL Engine uses the following country lookup table, "LU_COUNTRY":

COUNTRY_ID COUNTRY_DESC

1

United States

3

Europe

The SQL Engine left outer joins the METRIC1_TEMPTABLE above and the LU_COUNTRY table. The SQL Engine then left outer joins the METRIC2_TEMPTABLE above and the LU_COUNTRY table. Finally, the SQL Engine inner joins the results of the third pass to produce the final results.

The "Preserve all elements of final pass result table with respect to lookup table but not to relationship table" option returns the following results using the SQL below.

COUNTRY_ID COUNTRY_DESC Metric 1 Metric 2

1

United States

6000

10000

2

 

1000

17000

 
select pa1.COUNTRY_ID COUNTRY_ID,
 a11.COUNTRY_NAME COUNTRY_NAME,
 pa1.WJXBFS1 WJXBFS1,
 pa2.WJXBFS1 WJXBFS2
from ZZSP00 pa1, 
 ZZSP01 pa2, 
 LU_COUNTRY a11
where pa1.COUNTRY_ID = pa2.COUNTRY_ID and 
 pa1.COUNTRY_ID = a11.COUNTRY_ID (+)