Attribute Join Behavior

Overview

The Attribute Outer Join Behavior property provides control over how an attribute's lookup table joins with fact and relationship tables. This setting ensures data integrity in scenarios where reference data is incomplete, facts contain null keys, or when managing complex parent-child dimension hierarchies.

This property explicitly separates outer join behavior from pre-existing legacy settings, avoiding unexpected data differences.

This property overrides the legacy Preserve all final pass result elements, Preserve all lookup elements, and Preserve all lookup table elements settings.

Common Business Scenarios

Missing Dimension Data: Ensures all fact records are shown even if fact keys contain nulls and dimension data is missing. It explicitly supports multi-pass scenarios by cleanly collapsing duplicate rows when intermediate metric passes with null lookup IDs are joined.

Parent-Child Asymmetry: Supports parent-child dimension lookups by showing all parents when no fact rows exist or showing children even if parents show no data.

Configuration Settings and SQL Behavior

SELECT a.attr_id, SUM(f.metric)
FROM lookup_table a
LEFT OUTER JOIN fact_table f ON a.attr_id = f.attr_id
GROUP BY a.attr_id

-- Establishes the full dimensional space first, then joins facts

SELECT a1.attr_id_1, a2.attr_id_2, SUM(f.metric)
FROM lookup_table_1 a1
FULL OUTER JOIN lookup_table_2 a2 ON a1.shared_key = a2.shared_key 
LEFT OUTER JOIN fact_table f ON a1.attr_id_1 = f.attr_id_1 AND a2.attr_id_2 = f.attr_id_2
GROUP BY a1.attr_id_1, a2.attr_id_2

SELECT f.attr_id, SUM(f.metric)
FROM fact_table f
LEFT OUTER JOIN other_tables o ON f.attr_id = o.attr_id
GROUP BY f.attr_id

-- Hierarchy determines order: Lower-level (Item) evaluated before Higher-level (Category)

SELECT f.item_id, f.category_id, SUM(f.metric)
FROM fact_table f
LEFT OUTER JOIN lookup_item i ON f.item_id = i.item_id
LEFT OUTER JOIN lookup_category c ON f.category_id = c.category_id
GROUP BY f.item_id, f.category_id

SELECT COALESCE(a.attr_id, f.attr_id), SUM(f.metric)
FROM fact_table f
FULL OUTER JOIN lookup_table a ON f.attr_id = a.attr_id
GROUP BY COALESCE(a.attr_id, f.attr_id)

Resolution and Inheritance Hierarchy

The engine resolves the property value based on the execution context: Mosaic schema or Mosaic model. The highest priority setting overrides all lower levels.

Mosaic Models

When a dashboard or query is run against a Mosaic model, the property is resolved in the following priority, from highest to lowest:

1. Mosaic attribute level

2. Mosaic model level

3. Data source level

To be able to set this property at the attribute level in Mosaic models, this property must be turned on for the Mosaic model by setting Attribute Join Behavior in the Mosaic model settings.

Mosaic Schema (Standard Report)

When a traditional report against the Mosaic schema is run, the property is resolved in the following priority, from highest to lowest:

1. Report level

2. Attribute level

3. Data source level

   This outer join behavior applies to both live and import query modes for Mosaic models.

Migrate to the Attribute Join Behavior

This property overrides the legacy Preserve all final pass result elements , Outer Join to Lookup, and Full Outer Join Support settings but it does provide similar functionality with consistency at all levels (data source, attribute, report, and more). It also adds improved cascading logic to avoid related attributes from dropping rows due to related attributes that are marked as to be respected.

See the following table that maps the use of the new Attribute Outer Join Behavior property to the combined settings of the old properties: