The Strategy SQL Generation Engine includes a feature to identify attribute roles automatically and produce correct SQL when multiple attributes are map to the same lookup columns. This feature is enabled at the database instance level in the VLDB Properties Editor under Query Optimizations -> Engine Attribute Role Options. More details on this option may be found in the following Strategy Knowledge Base articles:
KB6197 - How do Attribute Roles Work in Strategy?
As described in KB6197, attribute roles may be modeled explicitly using logical table aliases, which are distinct logical table objects in the Strategy schema that use the same underlying warehouse table. Using this approach, a schema with attribute roles might be defined as follows:
With the logical tables LU_A1 and LU_A2 defined explicitly, there is no need to use the attribute role recognition option in the SQL Generation Engine. This option is required if the schema has only one logical table for the A* attributes' lookup, as shown below:
Without attribute role recognition, if SQL is generated for a report containing attributes A1 and A2 and a metric coming from F_A1_A2, both attributes will be resolved against LU_A, and the two attribute IDs will be joined to the same lookup column. Thus, the results will be incorrect in that rows from F_A1_A2 will be omitted if A1_ID and A2_ID have different values, since in that case they cannot both match the same column in the lookup table as follows:
select a11.A1_ID A_ID,
a12.A_DESC A_DESC,
a11.A2_ID A_ID0,
a12.A_DESC A_DESC0,
a11.FACT WJXBFS1
from F_A1_A2 a11
join LU_A a12
on (a11.A1_ID = a12.A_ID and
a11.A2_ID = a12.A_ID)
Attribute role recognition scans the logical tables during schema loading for attributes that are defined on the same columns. Any such tables are split dynamically and logical tables are created on-the-fly to separate the duplicate attributes. These internal tables are not saved into the metadata, but are re-created every time the schema is loaded, as follows:
In this case, the LU_A logical table in the metadata will not be used for SQL generation. The Engine will use the internal tables and keep the attributes' join paths separate, as shown below:
select a11.A1_ID A_ID,
a12.A_DESC A_DESC,
a11.A2_ID A_ID0,
a13.A_DESC A_DESC0,
a11.FACT WJXBFS1
from F_A1_A2 a11
join LU_A a12
on (a11.A1_ID = a12.A_ID)
join LU_A a13
on (a11.A2_ID = a13.A_ID)
When the duplicate attributes are divided between the internal tables, the attributes themselves are separated along with parents and children that exist in the same table. For instance, if a fact table has Order Date and Shipping Date attributes which share the same lookup table, one would want Order Month and Shipping Month to be kept separate as well.
This may create unexpected behavior if it is a fact table rather than a lookup table that contains the duplicate attributes. For example, in the following schema, two attributes are mapped to the A_ID column in the fact table. If attribute role recognition is used, the fact table will be split into two logical tables, each containing only one of the A attributes. It therefore becomes impossible to report on the fact at the level of A1 and A2 simultaneously, because there is no longer a logical table containing both attributes along with the fact.
Before attribute role recognition:
After attribute role recognition:
As such, a report with A1 and A2 based on the fact table will produce the error shown below:
SQLEngine got an Exception from DFC: Engine Logic: Fact does not exist at a level that can support the requested analysis. Fact: "Fact294959b". Level: "A1, A2".
It is possible to define a fact table with duplicate attributes as above, but where the fact table also contains parents or children of one of the duplicate attributes. Because parents and children are split along with the duplicate attributes, the same error will occur if one of the related attributes is used along with the opposing duplicate attribute. For instance, if A1 has a one-to-many child B1, A1 and B1 will be placed into one internal table while A2 will go into the other. B1 may not have any attribute ID collisions itself, but it will still be impossible to use B1 and A2 on the same report because of the way the attributes are divided between the internal tables.
For this reason, it is not recommended to use the attribute role recognition feature in schemas where fact tables have multiple attributes defined on the same columns. When the attribute roles are present on lookup tables, there is no impact on fact entry levels as there is when the attribute roles are on fact tables. Therefore, if it is needed to have attribute roles on fact tables, the attribute roles should be modeled explicitly using logical table aliases.
