KB14540: Result rows are omitted when using full outer join support in MicroStrategy SQL Generation Engine 9.x-10.x

• Strategy

SYMPTOM:
If a report contains multiple metrics and more than one of the metrics has a join type of 'outer,' the Strategy SQL Generation Engine 9.x-10.x may need to generate a full outer join to preserve all the rows of each metric's result table.

Certain databases do not allow a full outer join to be written into a SQL statement's FROM clause using SQL-92 syntax. For these databases, Strategy implements a technique of analyzing each metric's result pass to obtain all the distinct attribute elements that are required to preserve the rows. In the final pass, the SQL Engine produces a left outer join from this attribute element table to every metric result table in turn. In general this is expected to produce the same results as a SQL-92 full outer join.

This behavior is controlled using the very large database (VLDB) property 'Full Outer Join Support,' located in the Joins category. The 'Support' setting will produce a SQL-92 full outer join provided the 'join type' VLDB property allows SQL-92 syntax. The 'No support' setting will construct the temporary table for attribute elements as described above.

It is possible for a SQL-92 full outer join to produce a result set that is missing some of the rows that appear when Full Outer Join Support is disabled. For this to happen, the following conditions must be met:

One of the metrics must have a dimensionality that is higher than report level.
That metric's dimensionality must be at the level of one of the parents of one of the template attributes.
The result set for the higher-level metric must have an incomplete set of attribute elements with respect to the report level metrics.

Consider the following schema. There are two distinct hierarchies, in which attribute A stands alone without parents or children, and attribute B is a parent of attribute C, as shown below:

A report is constructed with all three attributes and two metrics defined as follows:

M1 = Sum(Fact) with dimensionality A, B
M2 = Sum(Fact) with report level dimensionality

Individually, the metrics have the following results:

M1

Note that attribute B, the parent attribute, has only an element B=One for M1 but it has a second element, B=Two, for M2. This is one of the preconditions for this issue.

Depending on the value of the Full Outer Join Support VLDB property, the report results will vary. Note that the rows that are omitted from the second report correspond to the B=Two attribute element that is not present in M1's result set, as shown below:

Full Outer Join Support = No support

Full Outer Join Support = Support

NOTE: The report must also have the VLDB property "Downward Outer Join" enabled on the third option, "Preserve all the rows for metrics higher than template level with report filter," to obtain these results. Consult the following Strategy Knowledge Base technical note for more details on the use of Downward Outer Join.

KB11122 (KB5200-75x-0476) - What is the "Downward Outer Join" VLDB property in Strategy SQL Generation Engine 9.x?
CAUSE:
To perform an outer join against metrics of different dimensionalities, the higher-level metrics must be adjusted downward to match the dimensionality of the lowest level metric. In this report, M1's dimensionality of {A, B} must be adjusted to include C also: {A, B, C}. In SQL (ZZMD00 is the result set for M1):

select pa11.a_id a_id,
   pa11.b_id b_id,
   a12.c_id c_id
into ZZOG002
from ZZMD000 pa11
   join DIM_ABC a12
      on (pa11.a_id = a12.a_id and
      pa11.b_id = a12.b_id)
That is, from the attribute elements A and B in the metric result set, the query extrapolates to the corresponding elements of C according to the table relating the attributes. The result of this operation is:

4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6

The Full Outer Join Support VLDB property alters the way this dimensionality adjustment is subsequently processed, and this accounts for the different results.

With Full Outer Join Support disabled, the Strategy SQL Generation Engine creates a temporary table holding all the attribute elements that are needed to preserve all the rows for both metrics. This is accomplished by taking the union of the distinct attribute elements from each metric pass.

4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6

Before this table can be used as the driving table against the metric result tables, the corresponding elements of B must be added to the element results. The results of A, C are joined against the dimension table to obtain the correct elements, and the table is completely populated, as shown below:

4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6

By contrast, when Full Outer Join Support is enabled, no additional table containing the attribute elements is produced. Instead, the lower level metric's results are full outer joined to the dimensionality adjustment table {A, B} --> {A, B, C}, and this set of attribute elements is joined to the higher-level metric's results. It is expected that the result of this full outer join will match the result of the attribute element union produced when Full Outer Join Support is disabled.

This dataset has a specific deficiency in which elements of the parent attribute (B) are missing from the higher-level metric's result set. Consequently they are also missing from the dimensionality adjustment table. The set of attribute elements is therefore not the same:

4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6
4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6	4 ABC113214Union of attribute elements for the key attributes (parent is omitted)ACSource13M1 results adjusted to include C2415M2 results (already at {A, C})26ABC113214125226ABC1132141NULL52NULL6

The null values in an attribute ID column mean that for those rows, the join to attribute B's lookup table will fail, eliminating those rows from the result set.

The parent-child relationship between B and C is critical in this scenario. If B were not a parent of C, it would be included in the dimensionality of M2. This would mean that all the required elements of B would be present in the result table and the rows would be preserved.

ACTION:
The VLDB property Select/Insert -> Attribute Selection Option for Intermediate Pass allows parent attributes to be included in a metric result pass even if not strictly required. Provided the fact table for M2 (the report level metric) includes attribute B's ID column, enabling the property as shown below causes the result set for M2 to include A, B and C and the report produces the correct results, as shown below:

NOTE: By design, the Attribute Selection Option VLDB property takes effect only when parent attributes exist in tables that are already present in the join tree of the SQL pass. This is why the B attribute's ID should be present in the fact table; otherwise, this condition may not be met and the Attribute Selection Option will have no effect.

WORKAROUND:
If the attribute selection option does not resolve the issue, then there may be no alternative but to disable Full Outer Join Support for reports exhibiting this behavior.

Comment

0 comments

Details

Knowledge Article

Published:

May 22, 2017

Last Updated:

May 22, 2017