KB484738: Options for the VLDB Property, "Attribute to Join When Key From Neither Side can be Supported by the Other Side" cannot be selected

Why This Change Was Made

This setting controls how the SQL Engine joins two tables when neither table's full unique key is completely present in the other table.

• Legacy Behavior (Join common key on both sides): The engine joined only on the shared key columns between the two tables. If both tables also shared a non-key attribute (such as Quarter), the engine ignored it in the JOIN clause.
• 2021+ Behavior (Join common attributes (reduced) on both sides): The engine now automatically includes shared non-key attributes in the JOIN condition to tighten the join and reduce unintentional row multiplication.
  

Example Scenario & Impact

Suppose a report calculates Cost at the Category level by joining a Fact Table (T1) and a Relationship Table (T2) with mismatched composite keys:

• Table T1 (Fact Table)
  • Unique Key: Customer City, Subcategory, Month_A
  • Other Attributes: Quarter (parent of Month_A), Cost (metric)
• Table T2 (Relationship Table for Category and Subcategory)
  • Unique Key: Customer City, Subcategory, Month_B
  • Other Attributes: Quarter (parent of Month_B), Category
    

What Changes in the SQL and Data?

1. Shared Keys vs. Shared Attributes: The only common keys between T1 and T2 are Customer City and Subcategory. However, both tables also share the non-key attribute Quarter.
2. Additional Join Condition: After upgrading to Data Engine Version 2021 or later, the generated SQL adds an extra join condition on Quarter (ON T1.Customer_City = T2.Customer_City AND T1.Subcategory = T2.Subcategory AND T1.Quarter = T2.Quarter).
3. Metric Value Changes: Because the join is now restricted by Quarter, fewer unrelated rows cross-join across different time periods, which may change the returned metric values compared to prior engine versions.
   

Why Metric Data Can Still Be Inaccurate (and How to Fix It)

Whenever a join between a Fact table and a Relationship table does not include the entire Unique Key of the Relationship table (Customer City, Subcategory, and Month_B), duplicate rows are generated and aggregated, inflating the Cost metric.

Because the VLDB property is retired, schema modification is required to resolve underlying row duplication. Update your schema using one of two approaches:

1. Clean up the Relationship Table Key (Recommended): Modify the relationship table (T2) for Category and Subcategory so that its Unique Key is strictly Subcategory, removing unnecessary granular keys (Customer City, Month_B).
2. Align the Fact Table Key: Ensure the Fact table (T1) contains the exact full combination of unique keys required by the relationship table (Customer City, Subcategory, and Month_B).