SUMMARY
This issue has been classified as a defect in Strategy 9.x-10.x and is specific to the scenario discussed below. A fix for this issue is currently not available in the current releases, but is being evaluated for a future Strategy release.
SYMPTOM
In Strategy 9.x-10.x, a redundant WHERE clause is seen in the final pass of SQL when the metric formula involves multiple facts and one fact contains a fact extension for one of the component facts.
The SQL calls fact table twice and the redundant WHERE pass has the format of “where (exists (select...... “
The issue is specific to metrics defined with a formula of (fact A x fact B) and fact B contains a fact extension on fact A.
STEP TO REPRODUCE
The steps below are based on the Strategy Tutorial project.
select a11.MONTH_ID MONTH_ID,
a12.region_id region_id,
sum((a11.MONTH_DURATION * a12.TOT_COST)) WJXBFS1
from LU_MONTH a11
join STATE_REGION_MNTH_SLS a12
on (a11.MONTH_ID = a12.MONTH_ID)
where (exists (select c21.MONTH_ID,
c21.region_id
from STATE_REGION_MNTH_SLS c21
where c21.region_id = a12.region_id
and c21.MONTH_ID = a11.MONTH_ID))
group by a11.MONTH_ID,
a12.region_id