KB17078: SQL execution error occurs when using MicroStrategy partitioning against a Netezza warehouse in MicroStrategy SQL Generation Engine 9.x

SYMPTOM
A report using a partitioned fact table is executed against a Netezza warehouse in Strategy 9.x. Depending on the configuration, the following error may be produced:
 

 
QueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. ERROR: 'insert into ZZTP400GHV6PO000 select a12.QUARTER_ID QUARTER_ID, a11.MONTH_ID MONTH_ID, sum(a11.EOH_QTY) WJXBFS1 from INVENTORY_Q1_2003 a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where a12.QUARTER_ID in (20031, 20032) group by a12.QUARTER_ID, a11.MONTH_ID DISTRIBUTE ON RANDOM'
error
CAUSE
Netezza's SQL syntax requirements permit the "DISTRIBUTE ON RANDOM" phrase to be used only in "create table" statements. This has a negative interaction with Strategy partitioning when implicit table creation is used.
 
Strategy partitioning follows a general two-step algorithm, with three branches in the second step. This applies to warehouse and metadata partitioning.
 

1. Issue a pre-query to determine which partitions contain data relevant to the report.
2. Based on the number of tables, collect the metric value(s) into an intermediate table.
    
   
• If no tables are chosen in the pre-query, create an empty table for the metric values.
• If exactly one table is chosen, create an intermediate table using one "select" statement using the single partition that matches the report filter.
• If more than one table returns, the intermediate table is created as the union of the results of one "select" statement per valid partition.

Whenever possible, Strategy uses the UNION keyword to handle multiple partitioned tables. Not all databases support UNION. Netezza is one of these databases. When UNION is not supported, Strategy issues SQL of the form:
 
create table #ZZPO01 as
select .. from .. where .. group by ..
insert into #ZZPO01
select .. from .. where .. group by ..
The SQL illustrates the "Implicit Table" value of the "Table Creation Type" VLDB property, in which the table is created and data selected into it in one statement. When implicit table creation is used, the recommended way to ensure that the "distribute on random" phrase is included for all insertions is to specify the phrase in the "Insert Post String" VLDB property, found in the "Select/Insert" category.
 

 
However, with this configuration, the aforementioned SQL for multiple partition tables includes "distribute on random" not only at table creation, but also when the data from the subsequent partitions are inserted.
 
create table #ZZPO01 as
select .. from .. where .. group by .. DISTRIBUTE ON RANDOM
insert into #ZZPO01
select .. from .. where .. group by .. DISTRIBUTE ON RANDOM
It is the appearance of the phrase in the "insert into" statement that causes the syntax error.
 
ACTION
The issue may be resolved by using explicit table creation and the "Create Post String" VLDB property.
 

• Remove the "distribute on random" phrase from the Insert Post String property.
• Add the phrase to the "Create Post String" property, found in the "Tables" category.
  

• Set the "Table Creation Type" VLDB property to "Explicit Table."
  

After these changes, the SQL for multiple partition tables (third branch) becomes:
 
create table #ZZPO01 (column definitions) DISTRIBUTE ON RANDOM
insert into #ZZPO01
select .. from .. where .. group by ..
insert into #ZZPO01
select .. from .. where .. group by ..
This SQL structure is valid for Netezza.
 
Note: The same issue may occur in other scenarios where a union of datasets is required. One other notable case involves metric full outer joins when the "Full Outer Join Support" VLDB property has been disabled (set to "No support"). Here, a union is used to collect all of the attribute IDs that will be required in the final result set. The full outer join scenario is not expected to affect Netezza users because Netezza does support full outer join syntax, and the VLDB properties are configured by default to use "full outer join" whenever possible.