There is a specific set of circumstances in which the Strategy Engine will generate SQL containing a subquery which appears to be redundant in the resolution of a simple report containing no filter of any kind. This behavior is highly dependent on the user's specific data model and object definitions, and the conditions under which the behavior is observed are not present in the Strategy Tutorial project.
However, the phenomenon may be outlined in generic terms as follows:
- The report in question contains one attribute (A1) and one metric (M1). Metric M1 is based on a fact F1.
- Attribute A1 is the direct parent of attribute A2, which in turn is the parent of attribute A3.
- Attribute A3 is in a many-to-many relationship with attribute A4.
- Attribute A4 represents the key to the fact table where fact F1 is stored.
- The project schema is highly normalized with none of the higher-level attributes stored in the fact table for F1, necessitating a large number of joins to resolve any report in which the data of F1 are being aggregated at higher levels.
- Therefore if the attribute relationships are traced down the hierarchy, it may be seen that attribute A1 is indirectly in a many-to-many relationship with attribute A4 which is the fact table key for F1.
- If A1 and M1 are placed on a report, the generated SQL will be similar to the following:
select a15.A1ID
max(a16.A1DESC)
count(distinct a12.F1) WJXBFS1
from TABLE1.a11
join TABLE2 a12
on (a11.A4ID = a12.A4ID)
join TABLE3. a13
on (a12.A3ID = a13.A3ID)
join TABLE4 a14
on (a13.A2ID = a14.A2ID)
join TABLE5 a15
on (a14.A1ID = a15.A1ID)
join TABLE6 a16
on (a15. A1ID = a16. A1ID)
where (exists (select *
from TABLE1 c21
join TABLE3 c22
on (c21.A3ID = c22.A3ID)
join TABLE4 c23
on (c22.A2ID = c23.A2ID)
join TABLE5 c24
on (c23.A1 = c24.A1)
where c24.A1 = a15.A1
and c21.A4 = a11.A4))
group by a15.A1
However, if the SQL is run directly against the data warehouse using the Strategy Test ODBC Tool, the user finds that the same results are returned regardless of whether the WHERE EXISTS subquery is present in the SQL or not. The WHERE EXISTS is therefore redundant in this particular case.
The WHERE EXISTS may be removed if the many-to-many relationship between attributes A3 and A4 is modified into a one-to-many relationship with A3 as the parent and A4 as the child. However, modification of attribute relationships may have other unintended consequences. Additionally, the addition of a filter to the report in question may have the effect that the presence or absence of the WHERE EXISTS does affect the results returned.
