Preserve All Lookup Table Elements

For an introduction to this property, see Preserving Data Using Outer Joins.

The Preserve All Lookup Table Elements VLDB property is used to show all attribute elements that exist in the lookup table, even though there is no corresponding fact in the result set. For example, your report contains Store and Sum(Sales), and it is possible that a store does not have any sales at all. However, you want to show all the store names in the final report, even those stores that do not have sales. To do that, you must not rely on the stores in the sales fact table. Instead, you must make sure that all the stores from the lookup table are included in the final report. The SQL Engine needs to do a left outer join from the lookup table to the fact table.

It is possible that there are multiple attributes on the template. To keep all the attribute elements, Analytical Engine needs to do a Cartesian Join between involved attributes' lookup tables before doing a left outer join to the fact table.

In MicroStrategy 7.1, this property was known as Final Pass Result Table Outer Join to Lookup Table.

Preserve Common Elements of Lookup and Final Pass Result Table (Default).

The Analytical Engine does a normal (equal) join to the lookup table.

Preserve Lookup Table Elements Joined to Final Pass Result Table Based on Fact Table Keys.

Sometimes the fact table level is not the same as the report or template level. For example, a report contains Store, Month, Sum(Sales) metric, but the fact table is at the level of Store, Day, and Item. There are two ways to keep all the store and month elements:

Do a left outer join first to keep all attribute elements at the Store, Day, and Item level, then aggregate to the Store and Month level.
Do aggregation first, then do a left outer join to bring in all attribute elements.
This option is for the first approach. In the example given previously, it makes two SQL passes:
Pass 1: LOOKUP_STORE cross join LOOKUP_DAY cross join LOOKUP_ITEM èTT1
Pass 2: TT1 left outer join Fact_Table on (store, day, item)

The advantage of this approach is that you can do a left outer join and aggregation in the same pass (pass 2). The disadvantage is that because you do a Cartesian join with the lookup tables at a much lower level (pass 1), the result of the Cartesian joined table (TT1) can be very large.

Preserve Lookup Table Elements Joined to Final Pass Result Table Based on Template Attributes Without Filter.

This option corresponds to the second approach described above. Still using the same example, it makes three SQL passes:

Pass 1: aggregate the Fact_Table to TT1 at Store and Month. This is actually the final pass of a normal report without turning on this setting.
Pass 2: LOOKUP_STORE cross join LOOKUP_MONTH èTT2
Pass 3: TT2 left outer join TT1 on (store, month)

This approach needs one more pass than the previous option, but the cross join table (TT2) is usually smaller.

Preserve Lookup Table Elements Joined to Final Pass Result Table Based on Template Attributes with Filter.

This option is similar to Option 3. The only difference is that the report filter is applied in the final pass (Pass 3). For example, a report contains Store, Month, and Sum(Sales) with a filter of Year = 2002. You want to display every store in every month in 2002, regardless of whether there are sales. However, you do not want to show any months from other years (only the 12 months in year 2002). Option 4 resolves this issue.

When this VLDB setting is turned ON (Option 2, 3, or 4), it is assumed that you want to keep ALL elements of the attributes in their lookup tables. However, sometimes you want such a setting to affect only some of the attributes on a template. For a report containing Store, Month, Sum(Sales), you may want to show all the store names, even though they have no sales, but not necessarily all the months in the LOOKUP_MONTH table. In 7i, you can individually select attributes on the template that need to preserve elements. This can be done from the Data menu, selecting Report Data Option, and then choosing Attribute Join Type. Notice that the 4 options shown on the upper right are the same as those in the VLDB dialog box (internally they are read from the same location). In the lower-right part, you see individual attributes. By default, all attributes are set to Outer, which means that every attribute participates with the Preserve All Lookup Tables Elements property. You still need to turn on this property to make it take effect, which can be done using either this dialog box or the VLDB dialog box.

Levels at Which You Can Set This

Database instance, report, and template

Example

The Preserve common elements of lookup and final pass result table option simply generates a direct join between the fact table and the lookup table. The results and SQL are as follows.

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

The "Preserve lookup table elements joined to final pass result table based on fact keys" option creates a temp table that is a Cartesian join of all lookup table key columns. Then the fact table is outer joined to the temp table. This preserves all lookup table elements. The results and SQL are as below:

Store	Dollar Sales
East	5000
Central	8000
South	12000
North

select distinct a11.Year Year

into #ZZOL00

from Fact a11

select pa1.Year Year,

 a11.Store_id Store_id

into #ZZOL01

from #ZZOL00 pa1

 cross join Store a11

select pa2.Store_id Store_id,

 max(a12.Store) Store,

 sum(a11.DollarSls) WJXBFS1

from #ZZOL01 pa2

 left outer join Fact a11

 on (pa2.Store_id = a11.Store_id and

 pa2.Year = a11.Year)

 join Store a12

 on (pa2.Store_id = a12.Store_id)

group by pa2.Store_id

drop table #ZZOL00

drop table #ZZOL01

The "Preserve lookup table elements joined to final pass result table based on template attributes without filter" option preserves the lookup table elements by left outer joining to the final pass of SQL and only joins on attributes that are on the template. For this example and the next, the filter of "Store not equal to Central" is added. The results and SQL are as follows:

Store	Dollar Sales
East	5000
Central
South	12000
North

select a11.Store_id Store_id,

 sum(a11.DollarSls) WJXBFS1

into #ZZT5X00003UOL000

from Fact a11

where a11.Store_id not in (2)

group by a11.Store_id

select a11.Store_id Store_id,

 a11.Store Store,

 pa1.WJXBFS1 WJXBFS1

from Store a11

 left outer join #ZZT5X00003UOL000 pa1

 on (a11.Store_id = pa1.Store_id)

drop table #ZZT5X00003UOL000

The "Preserve lookup table elements joined to final pass result table based on template attributes with filter" option is the newest option and is the same as above, but you get the filter in the final pass. The results and SQL are as follows:

Store	Dollar Sales
East	5000
South	12000
North

select a11.Store_id Store_id,

 sum(a11.DollarSls) WJXBFS1

into #ZZT5X00003XOL000

from Fact a11

where a11.Store_id not in (2)

group by a11.Store_id

select a11.Store_id Store_id,

 a11.Store Store,

 pa1.WJXBFS1 WJXBFS1

from Store a11

 left outer join #ZZT5X00003XOL000 pa1

 on (a11.Store_id = pa1.Store_id)

where a11.Store_id not in (2)

drop table #ZZT5X00003XOL000