SYMPTOM:
A user finds that attributes or facts whose relationships are defined correctly through table mappings are not joined correctly in report Structured Query Language (SQL). For example, in the Strategy Tutorial project, a report is created with the attributes 'Customer State' and 'Income Bracket' on the template. Although these attributes are drawn from different tables, they exist in the same hierarchy and are directly related through the 'Customer' attribute.
Sometimes, the Strategy SQL Engine finds the join path from Customer State through Customer City and Customer to the Income Bracket lookup table, as illustrated below:
select distinct a11.[CUST_STATE_ID] AS CUST_STATE_ID,
a11.[CUST_STATE_NAME] AS CUST_STATE_NAME,
a13.[INCOME_ID] AS INCOME_ID,
a14.[BRACKET_DESC] AS BRACKET_DESC
from [LU_CUST_STATE] a11,
[LU_CUST_CITY] a12,
[LU_CUSTOMER] a13,
[LU_INCOME] a14
where a11.[CUST_STATE_ID] = a12.[CUST_STATE_ID] and
a12.[CUST_CITY_ID] = a13.[CUST_CITY_ID] and
a13.[INCOME_ID] = a14.[INCOME_ID]
However, in other circumstances, the SQL may appear as follows, resulting in an incorrect Cartesian join between the Customer State and Income Bracket attributes:
select distinct a11.[CUST_STATE_ID] AS CUST_STATE_ID,
a11.[CUST_STATE_NAME] AS CUST_STATE_NAME,
a12.[INCOME_ID] AS INCOME_ID,
a12.[BRACKET_DESC] AS BRACKET_DESC
from [LU_CUST_STATE] a11,
[LU_INCOME] a12
CAUSE:
This issue is the result of multiple join paths as covered in the following article.
https://community.Strategy.com/s/article/ka1440000009HDdAAM/KB8962-The-shortest-path-is-not-always-selected-when-two-tables
If multiple relationship paths exist between attributes the first one found is used. Sometimes this will be an indirect or many to many path. In those scenarios it is possible for a cartesian join to be placed into the SQL to avoid the possible data inconsistency that would be caused by multi-counting if the only relationship path was the many to many path.
ACTION:
The user should find and remove the invalid relationship path.
