KB46764: The report SQL chooses the lookup table of one attribute as the join path even though the attribute is not included in report in MicroStrategy 9.x-10.x

SYMPTOM:
 
Attribute S is the one-to-many parent attribute for attribute A and R, which are supposed to be equal. Their look up tables are Dim_A, Dim_R and Dim_S, respectively. Additionally, the Dim_A and Dim_R are the relationship table for S, A and S, R. However, in report SQL, a user will find that Dim_A is chosen to join the fact table prior to Dim_R, even if attribute A is not included in the report. Besides, the fact table has entry level for both attribute A and R, but not for S.
 
Check the list of child attributes for S in attribute editor, notice that attribute A is listed above R.  
 
 
STEPS TO REPRODUCE:
 
 
 
 

1. Create a report with Attribute B, C and metric Y. Here attribute C has no relation to attribute A, B or S and no entry level in the fact table for metric Y.
2. Add report filter on S attribute and defined as ID=1.
3. Run report in SQL view. Notice that the fact table inner joins Dim_A although A is not include in report objects.

Sample Code/Error
select      a11. AS R_ID,
               max(a14.) AS R_DESC,
               a12. AS C_ID,
               max(a12.) AS C_DESC,
               sum(a11.) AS WJXBFS1
from        (((               a11
               cross join    a12)
               inner join                  a13
                 on         (a11. = a13.))
               inner join     a14
                 on         (a11. = a14.))
where      a13. = 1
group by  a11.,
               a12.
 
 
CAUSE:
 
The cause of joining Dim_A is that, there is a report filer S@ID=1. S is not the entry level of the fact table of the metric. To apply this report filter, it has to find a join path, either through A or R. The current logic is that, since A is added as child of S before R, so A is chosen as the join path.
 
ACTION:
 
An enhancement request has been logged to improve the logic so that the attribute used in report template should has the higher priority to be chosen as the join path for applying the report filter. Contact Strategy Technical Support for updates of status.
 
WORKAROUND:
 
Add entry level S_ID to the fact table for attribute S so that the SQL will directly join Dim_S instead of query on Dim_A.