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

KB14654: Redundant passes of SQL are generated when using the 'Additional Final Pass' VLDB property in MicroStrategy SQL Generation Engine

Community Admin

• Strategy

Normally, MicroStrategy SQL Generation Engine attempts to combine metric calculations and attribute lookups into one pass of SQL. Multiple passes are generated only when necessary to resolve all the metric definitions.

SYMPTOM:
Normally, Strategy SQL Generation Engine 9.4.x and 10.x attempts to combine metric calculations and attribute lookups into one pass of SQL. Multiple passes are generated only when necessary to resolve all the metric definitions.
 
The VLDB property, 'Additional Final Pass Option' is intended to override this behavior for single-pass reports. For more details, consult the following Strategy Knowledge Base Technical Note:
 
KB11182: How to force the SQL Generation Engine to generate multiple passes in Strategy 8.1.x and 9.x, even when all calculations can be done in one pass
 
When this VLDB property is activated in reports that contain any type of set qualification, the VLDB property will produce not only the expected final pass, but also redundant SQL passes for the set qualification.
 
The following report illustrates the issue using a ranking qualification that is evaluated on the database. Similar behavior may be observed with report-as-filter qualifications or relationship filters.
 

ka04W00000148n8QAA_0EM440000002FPU.gif

 
Against Oracle, compare the SQL generated with and without the Additional Final Pass Option enabled.
 

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID 

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID 

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID 

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID 

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID 

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11
 
create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11  create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID
 
select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11, 
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)
 
select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11, 
   ZZMQ01   pa12, 
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and 
   a11.SUBCAT_ID = a13.SUBCAT_ID 

 
Passes ZZAM01 and ZZEA02 add no new information to the query and could be omitted without affecting the results.
 
CAUSE:
This is the result of a known issue in Strategy SQL Generation Engine 9.4.x and 10.x.
 
ACTION:
Strategy will continue to investigate and post updates as soon as they are available
WORKAROUND:
The Additional Final Pass Option may be enabled or disabled on a per-report basis. It is recommended to disable this VLDB property for reports exhibiting this issue.
 
 
 

Comment

0 comments

Details

Knowledge Article

Published:

May 16, 2017

Last Updated:

May 16, 2017