KB42649: Multiple SQL/MDX passes are generated when using a metric qualification from an MDX source in a heterogenous data access report when compared to the same qualification in a simple MDX report

SYMPTOM:
In Strategy 9.4.x and 10.x, users of a heterogenous data access (HDA) project may notice that, multiple SQL passes are generated for a report when a metric qualification is added to a report while only one pass is generated if the same qualification is added to a simple MDX report with the same objects.
 
STEPS TO REPRODUCE IN Strategy TUTORIAL PROJECT:
 
Scenario 1:
 
     1.    Create a "normal" report New report>General>Blank Report with attribute Year from Tutorial and one metric Reseller Sales Amount from MSAS cube; add metric qualification >= 0 from MSAS cube.
     2.    Run the report, notice there are 10 passes in total; 2 passes are from , and rest from as shown below:
 
Sql
SQL Statements:
Pass0 -  
 Query Execution: 0:00:00.00
 Data Fetching and Processing: 0:00:00.00
   Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:04.66
 Rows selected: 5
with member . as '1'
set as '{...AllMembers}'
set as 'Filter(, . >= 0.0)'
set as '{Generate(, {..CurrentMember})}'
select {.} on columns,
non empty hierarchize({}) dimension properties MEMBER_KEY on rows
from
 
Pass1 -  
 Query Execution: 0:00:00.00
 Data Fetching and Processing: 0:00:00.00
   Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.03
create table #TSTVIBMK0OP000(
 YEAR_ID SMALLINT)
Pass2 -  
 Query Execution: 0:00:00.00
 Data Fetching and Processing: 0:00:00.00
   Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.00
 Rows inserted: 5
insert into #TSTVIBMK0OP000 values (2001)
Pass3 -  
 Query Execution: 0:00:00.00
 Data Fetching and Processing: 0:00:00.00
   Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.46
 Rows selected: 4
with set as '{...AllMembers}'
select {.} on columns,
non empty dimension properties MEMBER_KEY on rows
from
cell properties CELL_ORDINAL, FORMATTED_VALUE, VALUE, FORMAT_STRING
 
Pass4 -  
 Query Execution: 0:00:00.00
 Data Fetching and Processing: 0:00:00.00
   Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.03
create table #T4G9D7SHSOP001(
 YEAR_ID SMALLINT,
 WJXBFS1 VARCHAR(200))
Pass5 -  
 Query Execution: 0:00:00.00
 Data Fetching and Processing: 0:00:00.00
   Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.00
 Rows inserted: 4
insert into #T4G9D7SHSOP001 values (2001, '8065435.3053:B88A7E97415A0520A7FB07868495B7AA')
Pass6 -  
 Query Execution: 0:00:00.01
 Data Fetching and Processing: 0:00:00.00
   Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.03
 Rows selected: 4
select pa11.YEAR_ID  YEAR_ID,
 pa11.WJXBFS1  WJXBFS1
from #T4G9D7SHSOP001 pa11
 join #TSTVIBMK0OP000 pa12
   on  (pa11.YEAR_ID = pa12.YEAR_ID)
Pass7 -  
 Query Execution: 0:00:00.00
 Data Fetching and Processing: 0:00:00.00
   Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.00
 
Pass8 -  
 Query Execution: 0:00:00.01
 Data Fetching and Processing: 0:00:00.00
   Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.03
drop table #TSTVIBMK0OP000
Pass9 -  
 Query Execution: 0:00:00.01
 Data Fetching and Processing: 0:00:00.00
   Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.03
drop table #T4G9D7SHSOP001
 
 
Scenario 2:
 
     1.    Create a MDX report New report>MDX sources with the same steps as scenario 1
     2.    Run the report it is noticed that one MDX pass is generated as shown below:
 
Sql
MDX Statements:
with set as '{...AllMembers}'
set as 'Filter(, . >= 0.0)'
set as '{Generate(, {..CurrentMember})}'
select {.} on columns,
non empty hierarchize({}) dimension properties MEMBER_KEY on rows
from
cell properties CELL_ORDINAL, FORMATTED_VALUE, VALUE, FORMAT_STRING
 
 
CAUSE:
The observed behaviour of the product is the expected one. Metric qualifications in Strategy are implemented as multi-pass SQL in the Engine.
 

• In scenario 1 the report is a HDA (Heterogenous Data Access) report, and the filter is treated as Metric Qualification, thus we need separate pass and temp table to evaluate it. Also the HDA feature uses the project primary database instance to create temp tables and join the results since the MDX data sources are 'read only'.
• In scenario 2 the report is pure MDX report, and the filter is treated as Metric limit, thus it can be evaluated by single-pass MDX.

ACTION:
An enhancement request has been raised to try to fall back to pure MDX reports for HDA+MDX reports. Users may Strategy Technical Support for an update on the status of this enhancement request.