KB20430: What functionality is provided by the 'Create Individual Index' VLDB setting in MicroStrategy Engine

The 'Create Individual Index' VLDB setting was introduced in Strategy 8.0.3. It controls whether the indexes will be created separately for each column or whether the indexes will be created together for multiple columns. This VLDB setting is located in the 'Indexing' folder and has the following options:
 

• Create composite index for temporary table column indexing
• Create individual index for temporary table column indexing

This VLDB setting is available at Database Instance, Report and Template levels. The default value of this setting for most databases (except Sybase IQ) is to create a composite index. Sybase recommends the use of single column indexes for columns involved in joins.
 
The following example illustrates how this setting works for the 'Sybase IQ 12.7' database object (set in Database Instance Manager > Database Connection Type):
 

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
• Intermediate Table Index = Create table, insert into table, create index on intermediate table (all platforms other than Teradata, UDB EEE, RedBrick and Tandem)
• Max column in Index = 4 (This setting dictates the maximum number of columns that can be used when creating an index. For this test, it is recommended to have this setting at least a value of 2).
2. By default, index is created only on non-aggregatable columns (attributes). But that can be changed using the 'Allow Index on Metric' setting to use fact or metric columns for indexing.
    
   
3. 'Create Individual Index' VLDB setting is an advanced setting that is hidden by default. To display this property, enable 'Show Advance Settings' from the Tools menu in the VLDB properties editor.
    
   
4. Uncheck the 'Use default inherited value' box and set the option 'Create composite index for temporary table column indexing'. The generated SQL for indexing on intermediate tables looks something like the following:
   create table ZZMD00 (
   CUST_REGION_ID SMALLINT,
   CATEGORY_ID SMALLINT,
   BRAND_ID SMALLINT,
   Revenue DOUBLE,
   WJXBFS1 DOUBLE)
   
   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
   
   create index ZZMD00_i on ZZMD00 (CUST_REGION_ID, CATEGORY_ID, BRAND_ID)
   
5. If the 'Create individual index for temporary table column indexing' option is chosen, the generated SQL changes to:
   create table ZZMD00 (
   CUST_REGION_ID SMALLINT,
   CATEGORY_ID SMALLINT,
   BRAND_ID SMALLINT,
   Revenue DOUBLE,
   WJXBFS1 DOUBLE)
   
   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
   
   create index ZZMD00CUST_REGION_ID_i on ZZMD00 (CUST_REGION_ID) create index ZZMD00CATEGORY_ID_i on ZZMD00 (CATEGORY_ID) create index ZZMD00BRAND_ID_i on ZZMD00 (BRAND_ID)
   

NOTE:
The 'Create Individual Index' VLDB setting only applies to index creation after the data has been inserted into the intermediate table. It does not apply to situations where index creation occurs before insertion of data. Therefore, this setting only works when the 'Intermediate Table Index' VLDB setting is set to one of the following options:
 

• 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)

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