KB20433: How to have the MicroStrategy Engine generate syntax that involves index creation before data insertion on intermediate tables against Sybase IQ warehouse

Generation of indexes on intermediate tables is governed by the 'Intermediate Table Index' VLDB setting which provides the following four options:

• Do not create an index
• Create Primary index (Teradata)/Partition key (DB2/UDB EEE)/Primary Key (RedBrick, Tandem)
• Create Primary index (Teradata)/Partition key (DB2/UDB EEE)/Primary Key (RedBrick, Tandem) and Secondary index on intermediate table
• Create table, insert into table, create index on intermediate table (all platforms other than Teradata,UDB EEE, RedBrick and Tandem)

The default value of this setting for the 'Sybase IQ 12.7' database object (set in Database Instance Manager > Database Connection Type) is 'Do not create an index'. This option as well as the second option, 'Create Primary index (Teradata)/Partition key (DB2/UDB EEE)/Primary Key (RedBrick, Tandem)' does not create an index for intermediate tables against Sybase IQ. Only the third and fourth options - 'Create Primary index (Teradata)/Partition key (DB2/UDB EEE)/Primary Key (RedBrick, Tandem) and Secondary index on intermediate table' and 'Create table, insert into table, create index on intermediate table (all platforms other than Teradata,UDB EEE, RedBrick and Tandem)' - result in index creations against Sybase IQ and both these options create indexes only after the data has been inserted into the intermediate table. Per Sybase, index creation before data insertion is preferable for better performance.
 
To have the Strategy Engine generate syntax that involves index creation before data insertion on intermediate tables against Sybase IQ warehouse, follow the steps below:
 

1. Consider a report that generates intermediate tables with multiple columns. Set the following VLDB properties for that report:
   
• Intermediate Table Type = Permanent table or True Temporary table
• Table Creation Type = Explicit
• Create Post String = create index ???_i on ??? (!a)
• Intermediate Table Index = Do not create an index
2. The generated SQL will now look something like this:
   create table ZZMD00 (
   CUST_REGION_ID SMALLINT,
   CATEGORY_ID SMALLINT,
   BRAND_ID SMALLINT,
   Revenue DOUBLE,
   WJXBFS1 DOUBLE)
   
   create index ZZMD00_i on ZZMD00 (CUST_REGION_ID, CATEGORY_ID, BRAND_ID)
   
   insert into ZZMD00
   select a16.CUST_REGION_ID CUST_REGION_ID,
   a13.CATEGORY_ID CATEGORY_ID,
   a12.BRAND_ID BRAND_ID,
   .......
   group by a16.CUST_REGION_ID,
   a13.CATEGORY_ID,
   a12.BRAND_ID
   

NOTES:

• The above solution will only work when the indexes are created together for multiple columns (composite indexes). It will not work if indexes need to be created separately for each column (individual indexes). Refer to the following Knowledge Base document for additional details on creation of composite versus individual indexes against Sybase IQ:
  
• KB20430 - What functionality is provided by the 'Create Individual Index' VLDB setting in Strategy
• The !a wildcard will only include non-aggregatable columns (attributes). For all columns (including fact and metric columns) to be included, use the wildcard !!!

Third Party Software Installation:
WARNING:
The third-party product(s) discussed in this article is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.