MicroStrategy ONE
Preserve All Lookup Table Elements
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.
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.
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