SUMMARY
This Technical Note describes an issue with the Strategy SQL Engine when generating the SQL for reports using parallel query execution and using a freeform SQL report as a report as filter. This behavior has been observed in Strategy 10
SYMPTOM
When running SQL View for a report that contains a freeform SQL report as filter, there is no “Insert statement” shown in the SQL statements to insert the freeform SQL data back into the database for use as a filter. This causes a join to an empty temp table and the report to fail to return data correctly or to return at all.
STEPS TO REPRODUCE:
Follow these steps to reproduce the issue with the Strategy Tutorial:
select a11.[REGION_ID] AS REGION_ID,
a11.[REGION_NAME] AS REGION_NAME0
from [LU_REGION] a11;
2. Create a regular report with Region Attribute and Revenue Metric; also add the Free Form SQL report created in step 1 as the filter of this Report.
3. Set the following VLDB properties at Project configuration level, if not mentioned here leave them as default:
Project Configuration Level:
- Query Optimizations folder:
4. The behavior observed is that if Parallel query execution is disabled the insert statement is shown in the SQL view of the Report and if enabled no insert statement is shown.
Review Pass 2 and see how the INSERT statement is shown in there, with Parallel Query Execution disabled: | After enabling “Parallel Query Execution” and running the report, the following SQL view is observed: Look how after Pass2, there is no Insert statement: |
SQL Statements: Pass0 - Query Pass Start Time: 11/21/2016 9:18:15 PM Query Pass End Time: 11/21/2016 9:18:15 PM 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 selected: 12 select a11.[REGION_ID] AS REGION_ID, a11.[REGION_NAME] AS REGION_NAME0 from [LU_REGION] a11 Pass1 - Query Pass Start Time: 11/21/2016 9:18:15 PM Query Pass End Time: 11/21/2016 9:18:15 PM 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 create table ZZTKZ1IHVTLOP000 ( REGION_ID SHORT) Pass2 - Query Pass Start Time: 11/21/2016 9:18:15 PM Query Pass End Time: 11/21/2016 9:18:15 PM 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.03 Rows inserted: 12 insert into ZZTKZ1IHVTLOP000 values (1) Pass3 - Query Pass Start Time: 11/21/2016 9:18:15 PM Query Pass End Time: 11/21/2016 9:18:15 PM 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 create index ZZTKZ1IHVTLOP000_i on ZZTKZ1IHVTLOP000 (REGION_ID) Pass4 - Query Pass Start Time: 11/21/2016 9:18:15 PM Query Pass End Time: 11/21/2016 9:18:16 PM 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: 8 select pa13.[REGION_ID] AS REGION_ID, a14.[REGION_NAME] AS REGION_NAME0, sum(a11.[TOT_DOLLAR_SALES]) AS Revenue from [CITY_CTR_SLS] a11, [LU_CALL_CTR] a12, [ZZTKZ1IHVTLOP000] pa13, [LU_REGION] a14 where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID] and a12.[REGION_ID] = pa13.[REGION_ID] and pa13.[REGION_ID] = a14.[REGION_ID] group by pa13.[REGION_ID], a14.[REGION_NAME] Pass5 - Query Pass Start Time: 11/21/2016 9:18:16 PM Query Pass End Time: 11/21/2016 9:18:16 PM 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.53 [Populate Report Data] Pass6 - Query Pass Start Time: 11/21/2016 9:18:16 PM Query Pass End Time: 11/21/2016 9:18:16 PM 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 drop table ZZTKZ1IHVTLOP000 [Analytical engine calculation steps: 1. Perform cross-tabbing] | SQL Statements: Pass0 - [Tutorial Data] Query Pass Start Time: 11/21/2016 7:02:03 PM Query Pass End Time: 11/21/2016 7:02:03 PM 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.77 create table ZZTYWI4F6SGOP000 ( REGION_ID SHORT) Pass1 - [Tutorial Data] Query Pass Start Time: 11/21/2016 7:02:03 PM Query Pass End Time: 11/21/2016 7:02:03 PM 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.77 Rows selected: 12 select a11.[REGION_ID] AS REGION_ID, a11.[REGION_NAME] AS REGION_NAME0 from [LU_REGION] a11 Pass2 - [Tutorial Data] Query Pass Start Time: 11/21/2016 7:02:03 PM Query Pass End Time: 11/21/2016 7:02:03 PM 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.80 create index ZZTYWI4F6SGOP000_i on ZZTYWI4F6SGOP000 (REGION_ID) Pass3 - [Tutorial Data] Query Pass Start Time: 11/21/2016 7:02:03 PM Query Pass End Time: 11/21/2016 7:02:03 PM 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.83 Rows selected: 0 select pa13.[REGION_ID] AS REGION_ID, a14.[REGION_NAME] AS REGION_NAME0, sum(a11.[TOT_DOLLAR_SALES]) AS Revenue from [CITY_CTR_SLS] a11, [LU_CALL_CTR] a12, [ZZTYWI4F6SGOP000] pa13, [LU_REGION] a14 where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID] and a12.[REGION_ID] = pa13.[REGION_ID] and pa13.[REGION_ID] = a14.[REGION_ID] group by pa13.[REGION_ID], a14.[REGION_NAME] Pass4 - Query Pass Start Time: 11/21/2016 7:02:03 PM Query Pass End Time: 11/21/2016 7:02:03 PM 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.85 [Populate Report Data] Pass5 - [Tutorial Data] Query Pass Start Time: 11/21/2016 7:02:03 PM Query Pass End Time: 11/21/2016 7:02:03 PM 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 drop table ZZTYWI4F6SGOP000 [Analytical engine calculation steps: 1. Perform cross-tabbing] |
CAUSE:
This has been identified as a defect in the Strategy SQL Engine related with how the engine generates SQL for Parallel Query Execution for Reports using FreeformSQL reports as report as a filter.
ACTION:
Currently this issue is still being reviewed for feasibility by our Technology team and it is not scoped for any upcoming Strategy Release.
WORKAROUND:
Disable Parallel Query Execution if this scenario is encountered.
This issue is being tracked by internal reference number DE50405.
