Downward Outer Join Option

Downward Outer Join Option is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

To understand Downward Outer Join, consider the following report that contains the attribute Store and two metrics, Sales Per Store (M1) and Inventory Per Region (M2). The attribute Region is a parent of Store. Both M1 and M2 are set to Outer Join.

Store	Sales Per Store (M1)	Inventory Per Region (M2)

Traditionally, the outer join flag is ignored, because M2 (at Region level) is higher than the report level of Store. It is difficult to preserve all of the stores for a metric at the Region level. However, you can preserve rows for a metric at a higher level than the report. Since M2 is at the region level, it is impossible to preserve all regions for M2 because the report only shows Store. To do that, a downward join pass is needed to find all stores that belong to the region in M2, so that a union is formed among all these stores with the stores in M1.

When performing a downward join, another issue arises. Even though all the stores that belong to the region in M2 can be found, these stores may not be those from which M2 is calculated. If a report filters on a subset of stores, then M2 (if it is a filtered metric) is calculated only from those stores, and aggregated to regions. When a downward join is done, either all the stores that belong to the regions in M2 are included or only those stores that belong to the regions in M2 and in the report filter. Hence, this property has three options.

Levels at Which You Can Set This

Database instance, report, and template

Example

Using the above example and applying a filter for Atlanta and Charlotte, the default Do not preserve all the rows for metrics higher than template level option returns the following results. Note that Charlotte does not appear because it has no sales data in the fact table; the outer join is ignored. The outer join flag on metrics higher than template level is ignored.

Store	Sales Per Store (M1)	Inventory Per Region (M2)
Atlanta	100	300

Using Preserve all the rows for metrics higher than template level without report filter returns the results shown below. Now Charlotte appears because the outer join is used, and it has an inventory, but Washington appears as well because it is in the Region, and the filter is not applied.

Store	Sales Per Store (M1)	Inventory Per Region (M2)
Atlanta	100	300
Charlotte		300
Washington		300

Using Preserve all the rows for metrics higher than template level with report filter produces the following results. Washington is filtered out but Charlotte still appears because of the outer join.

Store	Sales Per Store (M1)	Inventory Per Region (M2)
Atlanta	100	300
Charlotte		300

For backward compatibility, the default is to ignore the outer join flag for metrics higher than template level. This is the SQL Engine behavior for MicroStrategy 6.x or lower, as well as for MicroStrategy 7.0 and 7.1.