Under some circumstances, the Strategy SQL Generation Engine 9.x can generate SQL passes that will not be used later in the report SQL. Such passes do not affect the report results and are thereby redundant.
A common case is a report which includes a metric qualification, where the dimensionality of template metrics ignores filtering on the output level of the metric qualification. For example, consider a report intended to show monthly revenue, but only including categories whose profit exceeds $500,000, as shown below:
SQL for this report is as follows. The first pass determines which categories meet the condition, while the second pass calculates revenue for those categories. The result of the first pass is inner-joined into the metric calculation to preserve only the rows meeting the metric qualification.
select a11.CATEGORY_ID CATEGORY_ID
into #ZZMQ00
from YR_CATEGORY_SLS a11
group by a11.CATEGORY_ID
having sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) >= 500000.0
select a11.MONTH_ID MONTH_ID,
max(a15.MONTH_DESC) MONTH_DESC,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from ITEM_MNTH_SLS a11
join LU_ITEM a12
on (a11.ITEM_ID = a12.ITEM_ID)
join LU_SUBCATEG a13
on (a12.SUBCAT_ID = a13.SUBCAT_ID)
join #ZZMQ00 pa14
on (a13.CATEGORY_ID = pa14.CATEGORY_ID)
join LU_MONTH a15
on (a11.MONTH_ID = a15.MONTH_ID)
group by a11.MONTH_ID
drop table #ZZMQ00
If the template metric is modified to ignore filtering on the Category attribute shown below, the metric calculation pass should omit the join to the intermediate table. However, without global optimization, Strategy SQL Generation Engine will still generate the metric qualification pass.
select a11.CATEGORY_ID CATEGORY_ID
into #ZZMQ00
from YR_CATEGORY_SLS a11
group by a11.CATEGORY_ID
having sum((a11.TOT_DOLLAR_SALES - a11.TOT_COST)) >= 500000.0
select a11.MONTH_ID MONTH_ID,
max(a12.MONTH_DESC) MONTH_DESC,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from CITY_MNTH_SLS a11
join LU_MONTH a12
on (a11.MONTH_ID = a12.MONTH_ID)
group by a11.MONTH_ID
drop table #ZZMQ00
With Level 1 to Level 4 global optimization, intermediate tables that are not used in any subsequent passes will be removed from the report SQL prior to submitting the job to the database.
select a11.MONTH_ID MONTH_ID,
max(a12.MONTH_DESC) MONTH_DESC,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from CITY_MNTH_SLS a11
join LU_MONTH a12
on (a11.MONTH_ID = a12.MONTH_ID)
group by a11.MONTH_ID
Refer to the following Strategy Knowledge Base technical note for general information about the Global Optimization feature introduced into Strategy 8.0.2:
KB13505: What is the purpose of the Strategy SQL Generation Engine 8.1.x feature: Global Optimization?
