EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB20580: What is the difference between Global Optimization levels 3 and 4 in MicroStrategy SQL Generation Engine?

Community Admin

• Strategy

Beginning with Strategy SQL Generation Engine, Global Optimization levels 3 and 4 merge metrics into a single pass that otherwise would require separate passes because of slightly different filtering qualifications.
 
Level 3 restricts its operation to passes that use a warehouse table as the fact table. Level 4 can apply also to passes that use an intermediate table in place of a fact table. In most cases, level 3 and level 4 will produce the same SQL because most metrics evaluate directly against warehouse fact tables.
 
Some types of metrics calculate against intermediate tables. These include nested aggregation metrics, metrics against partitioned fact tables and non-aggregatable metrics. For instance, a user may want to build a report to show the average revenue per employee, with one metric including mid-Atlantic employees and another including West Coast employees. To accomplish this, a metric is created to take the sum of Revenue at the level of Employee and report level (both with standard grouping and filtering). Then, a pair of metrics take the average of this employee-level metric, with report level dimensionality and the filters for Mid-Atlantic and West Coast, respectively.
 
Employee Revenue:

ka04W000000OcafQAC_0EM440000002EbB.gif

 
Avg Employee Rev, Mid-Atlantic

ka04W000000OcafQAC_0EM440000002EbM.gif

 
Avg Employee Rev, West Coast

ka04W000000OcafQAC_0EM440000002EbO.gif

 
Global Optimization level 4 produces two intermediate tables: one for the employee-level revenue metric, and the other for both conditional average metrics. By contrast, Global Optimization level 3 creates three intermediate tables. The employee-level metric still renders in one pass, because it calculates from the warehouse fact table and level 3 can thereby combine the two filters (inherited from the parent metrics). The average metrics, however, are based on this intermediate table, and level 3 does not combine passes using intermediate tables for fact data.
 
 

This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'.
 Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an indexForm NameForm TypeDefault Sort OrderIDIDNoneDESCDESCNoneLongDescNoneNoneForm NameForm TypeDefault Sort OrderIDIDNoneDESCDESCDescendingLongDescNoneAscendingForm NameForm TypeDefault Sort OrderIDIDAscendingDESCDESCDescendingLongDescNoneAscendingForm NameForm TypeDefault Sort OrderIDIDAscendingDESCDESCNoneLongDescNoneAscendingGlobal Optimization Level 3Global Optimization Level 4Calculation of inner level of aggregation
Metrics use warehouse fact tables and are combined

This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'.
 Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an indexForm NameForm TypeDefault Sort OrderIDIDNoneDESCDESCNoneLongDescNoneNoneForm NameForm TypeDefault Sort OrderIDIDNoneDESCDESCDescendingLongDescNoneAscendingForm NameForm TypeDefault Sort OrderIDIDAscendingDESCDESCDescendingLongDescNoneAscendingForm NameForm TypeDefault Sort OrderIDIDAscendingDESCDESCNoneLongDescNoneAscendingGlobal Optimization Level 3Global Optimization Level 4Calculation of inner level of aggregation
Metrics use warehouse fact tables and are combined

This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'.
 Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an indexForm NameForm TypeDefault Sort OrderIDIDNoneDESCDESCNoneLongDescNoneNoneForm NameForm TypeDefault Sort OrderIDIDNoneDESCDESCDescendingLongDescNoneAscendingForm NameForm TypeDefault Sort OrderIDIDAscendingDESCDESCDescendingLongDescNoneAscendingForm NameForm TypeDefault Sort OrderIDIDAscendingDESCDESCNoneLongDescNoneAscendingGlobal Optimization Level 3Global Optimization Level 4Calculation of inner level of aggregation
Metrics use warehouse fact tables and are combined

create table ZZMD00 (
   QUARTER_ID SHORT,
   EMP_ID SHORT,
   WJXBFS1 DOUBLE,
   GODWFLAG1_1 LONG,
   WJXBFS2 DOUBLE,
   GODWFLAG3_1 LONG)

insert into ZZMD00
select a12. AS QUARTER_ID,
   a11. AS EMP_ID,
   sum(iif(a14. in (2), (a11. * (a11. - a11.)), NULL)) AS WJXBFS1,
   max(iif(a14. in (2), 1, 0)) AS GODWFLAG1_1,
   sum(iif(a14. in (6, 7), (a11. * (a11. - a11.)), NULL)) AS WJXBFS2,
   max(iif(a14. in (6, 7), 1, 0)) AS GODWFLAG3_1
from a11,
    a12,
    a13,
    a14
where a11. = a12. and
   a11. = a13. and
   a13. = a14.
 and (a14. in (2)
 or a14. in (6, 7))
group by a12.,
   a11.

create table ZZMD00 (
   QUARTER_ID SHORT,
   EMP_ID SHORT,
   WJXBFS1 DOUBLE,
   GODWFLAG1_1 LONG,
   WJXBFS2 DOUBLE,
   GODWFLAG3_1 LONG)

insert into ZZMD00
select a12. AS QUARTER_ID,
   a11. AS EMP_ID,
   sum(iif(a14. in (2), (a11. * (a11. - a11.)), NULL)) AS WJXBFS1,
   max(iif(a14. in (2), 1, 0)) AS GODWFLAG1_1,
   sum(iif(a14. in (6, 7), (a11. * (a11. - a11.)), NULL)) AS WJXBFS2,
   max(iif(a14. in (6, 7), 1, 0)) AS GODWFLAG3_1
from a11,
    a12,
    a13,
    a14
where a11. = a12. and
   a11. = a13. and
   a13. = a14.
 and (a14. in (2)
 or a14. in (6, 7))
group by a12.,
   a11.
Calculation of higher-level average metrics
Metrics do not use warehouse fact tables and, by Level 3 restriction, are not combinedCalculation of higher-level average metrics
Metrics do not use warehouse fact tables and are combined by Level 4

create table ZZMD00 (
   QUARTER_ID SHORT,
   EMP_ID SHORT,
   WJXBFS1 DOUBLE,
   GODWFLAG1_1 LONG,
   WJXBFS2 DOUBLE,
   GODWFLAG3_1 LONG)

insert into ZZMD00
select a12. AS QUARTER_ID,
   a11. AS EMP_ID,
   sum(iif(a14. in (2), (a11. * (a11. - a11.)), NULL)) AS WJXBFS1,
   max(iif(a14. in (2), 1, 0)) AS GODWFLAG1_1,
   sum(iif(a14. in (6, 7), (a11. * (a11. - a11.)), NULL)) AS WJXBFS2,
   max(iif(a14. in (6, 7), 1, 0)) AS GODWFLAG3_1
from a11,
    a12,
    a13,
    a14
where a11. = a12. and
   a11. = a13. and
   a13. = a14.
 and (a14. in (2)
 or a14. in (6, 7))
group by a12.,
   a11.
Calculation of higher-level average metrics
Metrics do not use warehouse fact tables and, by Level 3 restriction, are not combinedCalculation of higher-level average metrics
Metrics do not use warehouse fact tables and are combined by Level 4

create table ZZMD00 (
   QUARTER_ID SHORT,
   EMP_ID SHORT,
   WJXBFS1 DOUBLE,
   GODWFLAG1_1 LONG,
   WJXBFS2 DOUBLE,
   GODWFLAG3_1 LONG)

insert into ZZMD00
select a12. AS QUARTER_ID,
   a11. AS EMP_ID,
   sum(iif(a14. in (2), (a11. * (a11. - a11.)), NULL)) AS WJXBFS1,
   max(iif(a14. in (2), 1, 0)) AS GODWFLAG1_1,
   sum(iif(a14. in (6, 7), (a11. * (a11. - a11.)), NULL)) AS WJXBFS2,
   max(iif(a14. in (6, 7), 1, 0)) AS GODWFLAG3_1
from a11,
    a12,
    a13,
    a14
where a11. = a12. and
   a11. = a13. and
   a13. = a14.
 and (a14. in (2)
 or a14. in (6, 7))
group by a12.,
   a11.
Calculation of higher-level average metrics
Metrics do not use warehouse fact tables and, by Level 3 restriction, are not combinedCalculation of higher-level average metrics
Metrics do not use warehouse fact tables and are combined by Level 4

create table ZZMD01 (
   QUARTER_ID SHORT,
   WJXBFS1 DOUBLE)

insert into ZZMD01
select pa11. AS QUARTER_ID,
   avg(pa11.) AS WJXBFS1
from pa11,
    a12,
    a13
where pa11. = a12. and
   a12. = a13.
 and (a13. in (2)
 and pa11. = 1)
group by pa11.

create table ZZMD02 (
   QUARTER_ID SHORT,
   WJXBFS1 DOUBLE)

insert into ZZMD02
select pa11. AS QUARTER_ID,
   avg(pa11.) AS WJXBFS1
from pa11,
    a12,
    a13
where pa11. = a12. and
   a12. = a13.
 and (a13. in (6, 7)
 and pa11. = 1)
group by pa11.

create table ZZMD01 (
   QUARTER_ID SHORT,
   WJXBFS1 DOUBLE,
   GODWFLAG2_1 LONG,
   WJXBFS2 DOUBLE,
   GODWFLAG4_1 LONG)

insert into ZZMD01
select pa11. AS QUARTER_ID,
   avg(iif((a13. in (2) and pa11. = 1), pa11., NULL)) AS WJXBFS1,
   max(iif((a13. in (2) and pa11. = 1), 1, 0)) AS GODWFLAG2_1,
   avg(iif((a13. in (6, 7) and pa11. = 1), pa11., NULL)) AS WJXBFS2,
   max(iif((a13. in (6, 7) and pa11. = 1), 1, 0)) AS GODWFLAG4_1
from pa11,
    a12,
    a13
where pa11. = a12. and
   a12. = a13.
 and ((a13. in (2)
 and pa11. = 1)
 or (a13. in (6, 7)
 and pa11. = 1))
group by pa11.

select pa11. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa11. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa11,
    pa12,
    a13
where pa11. = pa12. and
   pa11. = a13.

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)

Note: The performance gain of combining passes with different WHERE clauses is expected to be more significant for metrics calculating against fact tables. In general, intermediate tables created by Strategy will take filtering conditions into account and possibly perform some level of aggregation; thus they will usually be smaller than physical fact tables.
 
Further reading
For general information on Global Optimization levels 3 and 4, consult the following Strategy Knowledgebase article.
 
KB20579 (KB5200-9X-0001): How does the Global Optimization feature merge SQL passes with different WHERE clauses in Strategy SQL Generation Engine?

Comment

0 comments

Details

Knowledge Article

Published:

May 9, 2017

Last Updated:

May 9, 2017