Users may sometimes notice that changing the relationship table for a child or parent attribute may result in identical Structured Query Language (SQL) for two or more settings when those attributes are joined. For example, consider two hypothetical attributes, ATTR1 and ATTR2. The lookup table for ATTR1 contains only its ID and description, while the lookup table for ATTR2 contains its own ID and description as well as the ID for ATTR1. In addition, a fact is defined with a dimensionality of , as illustrated in the diagram below:
ATTR2 is defined as a child of ATTR1, but the Relationship table is set to Fact1, as shown below:
A report containing both attributes in the rows generates the following SQL:
select a11. AS Attr1_ID,
a12. AS Attr1_DESC,
a11. AS Attr2_ID,
a11. AS Attr2_DESC
from a11,
a12
where a11. = a12.
Note that the fact table (the specified relationship table) is not present in this query.
This is because the expression for ATTR1's ID form can draw the Attr1_ID column from both LU_ATTR1 and LU_ATTR2, as in the image below.
Joins of this sort are always preferred by the Strategy SQL Engine
The Relationship table setting applies only when a separate table (either a dedicated relate table or a fact table) is the only way to relate two attributes:
In this case, the SQL must take the form (here, assuming the relate table is chosen as the Relationship table while editing ATTR1):
select a11. AS Attr1_ID,
a11. AS Attr1_DESC,
a12. AS Attr2_ID,
a12. AS Attr2_DESC
from a11,
a12,
a13
where a11. = a13.
and a12. = a13.
If the user would rather relate the tables using the fact table, changing the Relationship table in the attribute editor will cause FACT_221027 to be chosen as the third table, rather than Relate_Attr1_Attr2:
select a12. AS Attr1_ID,
a12. AS Attr1_DESC,
a13. AS Attr2_ID,
a13. AS Attr2_DESC
from a11,
a12,
a13
where a11. = a12.
and a11. = a13.
