Strategy SQL Generation Engine has the option, in Global Optimization, to merge SQL passes with different WHERE clauses known as SQL Global Optimization.
There are five levels of optimization available in this property
This article discusses levels 3 and 4. Both level 3 and level 4 behave similarly.
Note: Global Optimization is not compatible with the use of warehouse partition mapping tables.
When do levels 3 and 4 apply?
The Strategy SQL Generation Engine produces multiple SQL passes with different WHERE clauses when metrics with different filters exist on the same report. Custom groups may also generate different WHERE clauses, and metrics with absolute filtering can also modify filter qualifications in a way that requires multiple passes.
Currently, attribute element list qualifications and basic form qualifications can be optimized by levels 3 and 4. Set qualifications (metric and relationship filters, and report-as-filter qualifications) are not subject to these levels of Global Optimization as they can contain logic which cannot be converted/supported inside of a select clause.
Note: Passes with different WHERE clauses can be combined only if they are compatible in other respects. Specifically, they should have identical FROM and GROUP BY clauses. If either clause is different, no level of Global Optimization would apply.
How are passes with different WHERE clauses combined?
If the only difference between a pair of SQL passes is the elements chosen in an attribute element list qualification, the Strategy SQL Generation Engine can move the different qualifications into the metric calculation expressions, typically using CASE statement syntax to replace that values that do not match the filter with NULL. Instead of accessing the same fact table in separate passes for several metrics, the metrics can instead be evaluated with the same result using only one fact table access. This is expected to provide noticeable improvements in report execution time.
For example, this report contains two metrics, both using the formula Sum(Revenue) but with different conditions.
Report:
Mid-Atlantic Revenue:
West Coast Revenue:
Without Global Optimization level 3 or 4, both metrics would require separate passes of SQL because of the different filters. Strategy SQL Generation Engine is able to combine both metrics into one calculation pass, avoiding repeated fact table access and improving execution time.
create table ZZMD00 ( REGION_ID BYTE, WJXBFS1 DOUBLE, GODWFLAG1_1 LONG, WJXBFS2 DOUBLE, GODWFLAG2_1 LONG)insert into ZZMD00select a12.region_id AS REGION_ID, sum(iif(a12.region_id in (2), a11.revenue, NULL)) AS WJXBFS1, max(iif(a12.region_id in (2), 1, 0)) AS GODWFLAG1_1, sum(iif(a12.region_id in (6, 7), a11.revenue, NULL)) AS WJXBFS2, max(iif(a12.region_id in (6, 7), 1, 0)) AS GODWFLAG2_1from CITY_CTR_SLS a11, lu_call_Center a12where a11.call_center_id = a12.call_center_idand (a12.region_id in (2) or a12.region_id in (6, 7))group by a12.region_idselect pa12.region_id AS REGION_ID, a13.region_name AS REGION_NAME0, pa12.wjxbfs1 AS WJXBFS1, pa12.wjxbfs2 AS WJXBFS2from ZZMD00 pa12, lu_region a13where pa12.region_id = a13.region_id and (pa12.godwflag1_1 = 1 and pa12.pa12.godwflag2_1 = 1)
For additional details on the behavior of Global Optimization in general, and specifically Levels 3 and 4, consult the following Strategy knowledge base articles and documentation.
KB13505: New feature in Strategy SQL Generation Engine: Global Optimization
KB20580: What is the difference between Global Optimization levels 3 and 4 in Strategy SQL Generation Engine?
KB20586: How do Global Optimization levels 3 and 4 handle inner and outer joins between metrics in Strategy SQL Generation Engine?
System Admin Guide: SQL Generation and Data Processing
