KB321444: Data Mart table is not created when “Parallel Query Execution” is enabled in MicroStrategy Secure Enterprise 10

• Strategy

This article notes an issue with data marts and parallel query execution

SYMPTOM
In Strategy Secure Enterprise 10, Data Mart tables are not created when the VLDB setting for “Parallel Query Execution” is enabled.
STEPS TO REPRODUCE
The following steps are based on the Strategy Tutorial project:

Create a report named R1 with Custom group “Age Group” in rows and metric “Revenue” in columns as shown below:

From the Data menu, select to configure a Data Mart to create new table called TEST as shown below:

Execute the Data Mart and notice Pass 10 is performing an INSERT into TEST values as shown below:


SQL Statements:
Pass0 - [Tutorial Data]
 Query Pass Start Time: 1/13/2017 7:58:59 AM
 Query Pass End Time: 1/13/2017 7:58:59 AM
 Query Execution: 0:00:00.07
 Data Fetching and Processing: 0:00:00.00
 Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.04
........
........
Pass9 - [Operational Datamart]
 Query Pass Start Time: 1/13/2017 7:59:00 AM
 Query Pass End Time: 1/13/2017 7:59:00 AM
 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.04
create table TEST (
 ELM_ID TEXT(254), 
 ELM_NAME TEXT(254), 
 F_ID LONG, 
 F_NAME TEXT(254), 
 Revenue DOUBLE)

Pass10 - [Operational Datamart]
 Query Pass Start Time: 1/13/2017 7:59:00 AM
 Query Pass End Time: 1/13/2017 7:59:00 AM
 Query Execution: 0:00:00.07
 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 TEST values (null, null, 2, '< 25', 3064711.1)

Pass11 - [Tutorial Data]
 Query Pass Start Time: 1/13/2017 7:59:00 AM
 Query Pass End Time: 1/13/2017 7:59:00 AM
 Query Execution: 0:00:00.06
 Data Fetching and Processing: 0:00:00.00
 Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.06
drop table ZZTMP9W6WG3OP000 purge
[Analytical engine calculation steps:
]

4. Turn on Parallel Query Execution at the project level following Technical Document below:
KB44052: Feature in the Strategy SQL engine: "Parallel Query Execution".
5 . Re-execute the Data Mart report and notice that the SQL is very different and that the table TEST is not even created.


* This report has some passes that have been executed in parallel. Individual time components may not add up to Total Elapsed Time in Query Engine.

SQL Statements:
Pass0 - [Tutorial Data]
 Query Pass Start Time: 1/13/2017 7:52:05 AM
 Query Pass End Time: 1/13/2017 7:52:09 AM
 Query Execution: 0:00:03.72
 Data Fetching and Processing: 0:00:00.00
 Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.59
create table ZZT7LGK6UITOP000 as
select a13.YEAR_ID YEAR_ID,
 sum((Case when a12.AGE_YEARS < 25 then (a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)) else NULL end)) Revenue,
 max((Case when a12.AGE_YEARS < 25 then 1 else 0 end)) GODWFLAG1_1,
 sum((Case when a12.AGE_YEARS between 25 and 35 then (a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)) else NULL end)) Revenue1,
 max((Case when a12.AGE_YEARS between 25 and 35 then 1 else 0 end)) GODWFLAG2_1,
 sum((Case when a12.AGE_YEARS between 36 and 50 then (a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)) else NULL end)) Revenue2,
 max((Case when a12.AGE_YEARS between 36 and 50 then 1 else 0 end)) GODWFLAG3_1,
 sum((Case when a12.AGE_YEARS between 51 and 60 then (a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)) else NULL end)) Revenue3,
 max((Case when a12.AGE_YEARS between 51 and 60 then 1 else 0 end)) GODWFLAG4_1,
 sum((Case when a12.AGE_YEARS > 60 then (a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)) else NULL end)) Revenue4,
 max((Case when a12.AGE_YEARS > 60 then 1 else 0 end)) GODWFLAG5_1
from ORDER_DETAIL a11
 join LU_CUSTOMER a12
 on (a11.CUSTOMER_ID = a12.CUSTOMER_ID)
 join LU_DAY a13
 on (a11.ORDER_DATE = a13.DAY_DATE)
where (a13.YEAR_ID in (2016, 2015)
and (a12.AGE_YEARS < 25
or a12.AGE_YEARS between 25 and 35
or a12.AGE_YEARS between 36 and 50
or a12.AGE_YEARS between 51 and 60
or a12.AGE_YEARS > 60))
group by a13.YEAR_ID
Pass1 - [Tutorial Data]
 Query Pass Start Time: 1/13/2017 7:52:09 AM
 Query Pass End Time: 1/13/2017 7:52:09 AM
 Query Execution: 0:00:00.03
 Data Fetching and Processing: 0:00:00.00
 Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.43
 Rows selected: 2
select pa01.YEAR_ID YEAR_ID,
 pa01.Revenue WJXBFS1
from ZZT7LGK6UITOP000 pa01
where pa01.GODWFLAG1_1 = 1
Pass2 - [Tutorial Data]
 Query Pass Start Time: 1/13/2017 7:52:09 AM
 Query Pass End Time: 1/13/2017 7:52:09 AM
 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.56
 Rows selected: 2
select pa01.YEAR_ID YEAR_ID,
 pa01.Revenue2 WJXBFS1
from ZZT7LGK6UITOP000 pa01
where pa01.GODWFLAG3_1 = 1
Pass3 - [Tutorial Data]
 Query Pass Start Time: 1/13/2017 7:52:09 AM
 Query Pass End Time: 1/13/2017 7:52:09 AM
 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.65
 Rows selected: 2
select pa01.YEAR_ID YEAR_ID,
 pa01.Revenue3 WJXBFS1
from ZZT7LGK6UITOP000 pa01
where pa01.GODWFLAG4_1 = 1
Pass4 - [Tutorial Data]
 Query Pass Start Time: 1/13/2017 7:52:09 AM
 Query Pass End Time: 1/13/2017 7:52:09 AM
 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.78
 Rows selected: 2
select pa01.YEAR_ID YEAR_ID,
 pa01.Revenue1 WJXBFS1
from ZZT7LGK6UITOP000 pa01
where pa01.GODWFLAG2_1 = 1
Pass5 - [Tutorial Data]
 Query Pass Start Time: 1/13/2017 7:52:09 AM
 Query Pass End Time: 1/13/2017 7:52:09 AM
 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.87
 Rows selected: 2
select pa01.YEAR_ID YEAR_ID,
 pa01.Revenue4 WJXBFS1
from ZZT7LGK6UITOP000 pa01
where pa01.GODWFLAG5_1 = 1
Pass6 - Query Pass Start Time: 1/13/2017 7:52:09 AM
 Query Pass End Time: 1/13/2017 7:52:10 AM
 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:01.01
[Populate Report Data]
Pass7 - [Tutorial Data]
 Query Pass Start Time: 1/13/2017 7:52:10 AM
 Query Pass End Time: 1/13/2017 7:52:10 AM
 Query Execution: 0:00:00.07
 Data Fetching and Processing: 0:00:00.00
 Data Transfer from Datasource(s): 0:00:00.00
 Other Processing: 0:00:00.20
drop table ZZT7LGK6UITOP000 purge

[Analytical engine calculation steps:
]

CAUSE
This is a known issue with Strategy Secure Enterprise 10.
ACTION
Disable Parallel Query Execution for Data Mart reports.

The Strategy Internal Reference Number for the issue discussed in this technical note is DE51556.

Comment

0 comments

Details

Knowledge Article

Published:

May 17, 2017

Last Updated:

July 17, 2017

SQL Statements: Pass0 - [Tutorial Data] Query Pass Start Time: 1/13/2017 7:58:59 AM Query Pass End Time: 1/13/2017 7:58:59 AM Query Execution: 0:00:00.07 Data Fetching and Processing: 0:00:00.00 Data Transfer from Datasource(s): 0:00:00.00 Other Processing: 0:00:00.04 ........ ........ Pass9 - [Operational Datamart] Query Pass Start Time: 1/13/2017 7:59:00 AM Query Pass End Time: 1/13/2017 7:59:00 AM 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.04 create table TEST ( ELM_ID TEXT(254), ELM_NAME TEXT(254), F_ID LONG, F_NAME TEXT(254), Revenue DOUBLE) Pass10 - [Operational Datamart] Query Pass Start Time: 1/13/2017 7:59:00 AM Query Pass End Time: 1/13/2017 7:59:00 AM Query Execution: 0:00:00.07 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 TEST values (null, null, 2, '< 25', 3064711.1) Pass11 - [Tutorial Data] Query Pass Start Time: 1/13/2017 7:59:00 AM Query Pass End Time: 1/13/2017 7:59:00 AM Query Execution: 0:00:00.06 Data Fetching and Processing: 0:00:00.00 Data Transfer from Datasource(s): 0:00:00.00 Other Processing: 0:00:00.06 drop table ZZTMP9W6WG3OP000 purge [Analytical engine calculation steps: ]

* This report has some passes that have been executed in parallel. Individual time components may not add up to Total Elapsed Time in Query Engine. SQL Statements: Pass0 - [Tutorial Data] Query Pass Start Time: 1/13/2017 7:52:05 AM Query Pass End Time: 1/13/2017 7:52:09 AM Query Execution: 0:00:03.72 Data Fetching and Processing: 0:00:00.00 Data Transfer from Datasource(s): 0:00:00.00 Other Processing: 0:00:00.59 create table ZZT7LGK6UITOP000 as select a13.YEAR_ID YEAR_ID, sum((Case when a12.AGE_YEARS < 25 then (a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)) else NULL end)) Revenue, max((Case when a12.AGE_YEARS < 25 then 1 else 0 end)) GODWFLAG1_1, sum((Case when a12.AGE_YEARS between 25 and 35 then (a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)) else NULL end)) Revenue1, max((Case when a12.AGE_YEARS between 25 and 35 then 1 else 0 end)) GODWFLAG2_1, sum((Case when a12.AGE_YEARS between 36 and 50 then (a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)) else NULL end)) Revenue2, max((Case when a12.AGE_YEARS between 36 and 50 then 1 else 0 end)) GODWFLAG3_1, sum((Case when a12.AGE_YEARS between 51 and 60 then (a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)) else NULL end)) Revenue3, max((Case when a12.AGE_YEARS between 51 and 60 then 1 else 0 end)) GODWFLAG4_1, sum((Case when a12.AGE_YEARS > 60 then (a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)) else NULL end)) Revenue4, max((Case when a12.AGE_YEARS > 60 then 1 else 0 end)) GODWFLAG5_1 from ORDER_DETAIL a11 join LU_CUSTOMER a12 on (a11.CUSTOMER_ID = a12.CUSTOMER_ID) join LU_DAY a13 on (a11.ORDER_DATE = a13.DAY_DATE) where (a13.YEAR_ID in (2016, 2015) and (a12.AGE_YEARS < 25 or a12.AGE_YEARS between 25 and 35 or a12.AGE_YEARS between 36 and 50 or a12.AGE_YEARS between 51 and 60 or a12.AGE_YEARS > 60)) group by a13.YEAR_ID Pass1 - [Tutorial Data] Query Pass Start Time: 1/13/2017 7:52:09 AM Query Pass End Time: 1/13/2017 7:52:09 AM Query Execution: 0:00:00.03 Data Fetching and Processing: 0:00:00.00 Data Transfer from Datasource(s): 0:00:00.00 Other Processing: 0:00:00.43 Rows selected: 2 select pa01.YEAR_ID YEAR_ID, pa01.Revenue WJXBFS1 from ZZT7LGK6UITOP000 pa01 where pa01.GODWFLAG1_1 = 1 Pass2 - [Tutorial Data] Query Pass Start Time: 1/13/2017 7:52:09 AM Query Pass End Time: 1/13/2017 7:52:09 AM 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.56 Rows selected: 2 select pa01.YEAR_ID YEAR_ID, pa01.Revenue2 WJXBFS1 from ZZT7LGK6UITOP000 pa01 where pa01.GODWFLAG3_1 = 1 Pass3 - [Tutorial Data] Query Pass Start Time: 1/13/2017 7:52:09 AM Query Pass End Time: 1/13/2017 7:52:09 AM 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.65 Rows selected: 2 select pa01.YEAR_ID YEAR_ID, pa01.Revenue3 WJXBFS1 from ZZT7LGK6UITOP000 pa01 where pa01.GODWFLAG4_1 = 1 Pass4 - [Tutorial Data] Query Pass Start Time: 1/13/2017 7:52:09 AM Query Pass End Time: 1/13/2017 7:52:09 AM 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.78 Rows selected: 2 select pa01.YEAR_ID YEAR_ID, pa01.Revenue1 WJXBFS1 from ZZT7LGK6UITOP000 pa01 where pa01.GODWFLAG2_1 = 1 Pass5 - [Tutorial Data] Query Pass Start Time: 1/13/2017 7:52:09 AM Query Pass End Time: 1/13/2017 7:52:09 AM 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.87 Rows selected: 2 select pa01.YEAR_ID YEAR_ID, pa01.Revenue4 WJXBFS1 from ZZT7LGK6UITOP000 pa01 where pa01.GODWFLAG5_1 = 1 Pass6 - Query Pass Start Time: 1/13/2017 7:52:09 AM Query Pass End Time: 1/13/2017 7:52:10 AM 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:01.01 [Populate Report Data] Pass7 - [Tutorial Data] Query Pass Start Time: 1/13/2017 7:52:10 AM Query Pass End Time: 1/13/2017 7:52:10 AM Query Execution: 0:00:00.07 Data Fetching and Processing: 0:00:00.00 Data Transfer from Datasource(s): 0:00:00.00 Other Processing: 0:00:00.20 drop table ZZT7LGK6UITOP000 purge [Analytical engine calculation steps: ]