SYMPTOM:
Two different passes of SQL for metrics which only differ via a simple filter are note merged via SQL Global Optimization levels 3 and 4 despite notes that it can. The two metrics filters are simple attribute qualifications which are allowed under Global Optimization. SQL like the below is seen per an example designed in the Strategy Tutorial project.
create table ZZMD00 (
YEAR_ID SHORT,
REGION_ID SHORT,
CATEGORY_ID SHORT,
WJXBFS1 DOUBLE)
insert into ZZMD00
select a14.[YEAR_ID] AS YEAR_ID,
a13.[REGION_ID] AS REGION_ID,
a12.[CATEGORY_ID] AS CATEGORY_ID,
sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from [SUBCATEG_MNTH_CTR_SLS] a11,
[LU_SUBCATEG] a12,
[LU_CALL_CTR] a13,
[LU_MONTH] a14
where a11.[SUBCAT_ID] = a12.[SUBCAT_ID] and
a11.[CALL_CTR_ID] = a13.[CALL_CTR_ID] and
a11.[MONTH_ID] = a14.[MONTH_ID]
and (((a11.[MONTH_ID])
in (select r11.[MONTH_ID]
from [LU_DAY] r11
where r11.[DAY_DATE] in (#2009-04-06 00:00:00#)))
and a12.[CATEGORY_ID] in (2))
group by a14.[YEAR_ID],
a13.[REGION_ID],
a12.[CATEGORY_ID]
create table ZZMD01 (
YEAR_ID SHORT,
REGION_ID SHORT,
CATEGORY_ID SHORT,
WJXBFS1 DOUBLE)
insert into ZZMD01
select a14.[YEAR_ID] AS YEAR_ID,
a13.[REGION_ID] AS REGION_ID,
a12.[CATEGORY_ID] AS CATEGORY_ID,
sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from [SUBCATEG_MNTH_CTR_SLS] a11,
[LU_SUBCATEG] a12,
[LU_CALL_CTR] a13,
[LU_MONTH] a14
where a11.[SUBCAT_ID] = a12.[SUBCAT_ID] and
a11.[CALL_CTR_ID] = a13.[CALL_CTR_ID] and
a11.[MONTH_ID] = a14.[MONTH_ID]
and (((a11.[MONTH_ID])
in (select r11.[MONTH_ID]
from [LU_DAY] r11
where r11.[DAY_DATE] in (#2009-04-06 00:00:00#)))
and a12.[CATEGORY_ID] in (3))
group by a14.[YEAR_ID],
a13.[REGION_ID],
a12.[CATEGORY_ID]
select distinct pa11.[REGION_ID] AS REGION_ID,
a14.[REGION_NAME] AS REGION_NAME0,
pa11.[CATEGORY_ID] AS CATEGORY_ID,
a13.[CATEGORY_DESC] AS CATEGORY_DESC0,
pa11.[YEAR_ID] AS YEAR_ID,
pa11.[WJXBFS1] AS WJXBFS1,
pa12.[WJXBFS1] AS WJXBFS2
from [ZZMD00] pa11,
[ZZMD01] pa12,
[LU_CATEGORY] a13,
[LU_REGION] a14
where pa11.[CATEGORY_ID] = pa12.[CATEGORY_ID] and
pa11.[REGION_ID] = pa12.[REGION_ID] and
pa11.[YEAR_ID] = pa12.[YEAR_ID] and
pa11.[CATEGORY_ID] = a13.[CATEGORY_ID] and
pa11.[REGION_ID] = a14.[REGION_ID]
Notice that in both metric calculation passes there is the same subquery in the where clause. If this relationship filter is removed from the report filter then global optimization is able to function correctly.
CAUSE:
This is a known issue with Strategy 9.x. Currently Global Optimization levels 3 and 4 will only apply if the passes in question contain no filters applied through subqueries or other means even if they aren't in the specific metric conditions. This is due to the limitation present on some databases that cannot place subqueries inside of the select part of a SQL query and the fact that it is possible that Global Optimization may need to play these filters in the select clause as part of its optimization. There is an enhancement in place to allow Global Optimization to happen if the subquery filter is a report filter that applies to all conditional metrics on the report.
ACTION:
Currently this issue is still being reviewed for feasibility by our Technology team and it is not scoped for any upcoming scheduled Strategy release.
WORKAROUND:
The current workaround is to set the "Sub-Query Type" VLDB property to use temporary tables instead of an actual subquery. With this option Global Optimization is able to take effect without issue.
