MicroStrategy ONE

Preserve All Lookup Table Elements

The Preserve All Lookup Table Elements setting 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). You need to show all the stores in the report, even those stores that do not have sales. Instead of relying on the stores in the sales fact table, you must ensure that all the stores from the lookup table are included. The SQL Engine must use a left outer join from the lookup table to the fact table.

The report can include multiple attributes. To keep all of them, the Analytical Engine must use a Cartesian join between the attributes' lookup tables before doing a left outer join to the fact table.

The options for this property are:

  • Preserve common elements of lookup and final pass result table

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

  • Preserve lookup table elements joined to final pass result table based on fact keys

    If the fact table level is not the same as the report level, this option first uses a left outer join to keep all the attribute elements at the fact table level. Next, it aggregates to the report level. The advantage of this approach is the same pass of SQL performs the left outer join and aggregation. The disadvantage is that the Cartesian join with the lookup tables is at a much lower level and can result in a very large joined table.

  • Preserve lookup table elements joined to final pass result table based on template attributes without filter

    If the fact table level is not the same as the report level, this option first aggregates to the report level, then uses a left outer join to take in all the attribute elements. This is the reverse of the approach described above. It needs an additional pass, but the cross join table is usually smaller.

  • Preserve lookup table elements joined to final pass result table based on template attributes with filter

    This option is similar to the previous option, the difference being that the report filter is applied in the final pass.

  • Use default inherited value

    The various "Preserve lookup table elements" options are described in more detail in Example: Different Levels for Fact Table and Report.

    When you use any of the "Preserve lookup table elements" options, it is assumed you want to keep all the elements of the attributes in their lookup tables. However, you may want the setting to affect only some of the attributes on a template. For example, a report contains Store, Month, and the Sum(Sales) metric. You want to show all the store names, even if they do not have sales, but not necessarily all the months in the LOOKUP_MONTH table. You can individually select attributes on the report that need to preserve elements with the Attribute Join Type setting in Report Data Options. For more information, see Selecting an Attribute Join Type.

Example: Different Levels for Fact Table and Report

The fact table level is not always the same as the report level. For example, a report contains Store, Month, and the Sum(Sales) metric, but the fact table is at the level of Store, Day, and Item. You can choose any of the "Preserve lookup table elements" options to accomplish this, as described below.

Preserve lookup table elements joined to final pass result table based on fact table keys

First, a left outer join keeps all the attribute elements at the Store, Day, and Item level. Then the results are aggregated to the Store and Month level. Two SQL passes are run:

  • Pass 1: LOOKUP_STORE cross join with LOOKUP_DAY cross join LOOKUP_ITEM produces TempTable1
  • Pass 2: TempTable1 left outer join with Fact_Table on (Store, Day, Item)

The advantage is that the left outer join and aggregation is completed in the same pass (Pass 2). The disadvantage is that the Cartesian join with the lookup tables is performed at a much lower level (Pass 1) so the result of the Cartesian joined table (TempTable1) can be very large.

Preserve lookup table elements joined to final pass result table based on template attributes without filter

The aggregation is completed first, then a left outer join brings in all the attribute elements. Three SQL passes are run:

  • Pass 1: Aggregates the Fact_Table to TempTable1 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 with LOOKUP_MONTH produces TempTable2
  • Pass 3: TempTable2 left outer join with TempTable1 on (Store, Month)

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

Preserve lookup table elements joined to final pass result table based on template attributes with filter

This option is similar to the previous option. The only difference is that the report filter is applied in the final pass (Pass 3). For example, a report contains Store, Month, the Sum(Sales) metric, and a filter of Year = 2022. You want to display every store in every month in 2022, regardless of whether sales occurred. However, you do not want to show months from any other years. This setting resolves the issue.