Table Index

• MicroStrategy allows you to query data with the help of an index using the indexing VLDB properties.

• Primary key for PostgreSQL is supported in MicroStrategy.

• To create an index, the intermediate table type should be permanent table or true temporary table.

• You can create an index before or after inserting data.

• You can create a composite or individual index.

• You can limit the number of indexing columns.

How to Modify VLDB Properties

1. Open a MicroStrategy report.
2. Go to Data > VLDB Properties.

3. In the VLDB Properties (Report) Editor, click Tools and select the Show Advanced Settings checkbox.

   

4. Under Indexing, there are several options to help the user to control index creation in SQL statement. See the table below for information on each option:

   Property	Description	Possible Values	Comments
   Allow Index on Metric	Determines whether to allow the creation of indexes on fact or metric columns.	Don’t allow the creation of indexes on metric columns Allow the creation of indexes on metric columns (if the Intermediate Table Index setting is set to create)	Select allow if you want to include metric columns in the index, Select don’t allow if only want to index attributes.
   Index Post String and Index Qualifier	Defines the string that is appended at the end of the CREATE INDEX statement. For example: IN INDEXSPACE	User-defined
   Retrieving data: Indexing	Defines the prefix to use when automatically creating indexes for intermediate SQL passes. The prefix is added to the beginning of the CREATE INDEX statement.	User-defined
   Index Post String and Index Qualifier	Defines the string to parse in between the CREATE and INDEX words. For example: CLUSTERED	User-defined
   Intermediate Table Index	Determines whether and when to create an index for the intermediate table.	Don't create an index Create partitioning key (typically applicable to MPP systems) Create a partitioning key and secondary index on an intermediate table Create only secondary index on the intermediate table	Partitioning key does not apply to  SQL SERVER, please select only secondary index if want to create an index.
   Max Columns in Column Placeholder	Determines the maximum number of columns that replace the column wildcard ("!!!") in pre and post statements. 0 = all columns (no limit).	User-defined
   Max Columns in Index	Determines the maximum number of columns that can be included in partition key or index.	User-defined	By default, no limit.
   Primary Index Type	Determines whether a primary key is created instead of a partitioning key for databases that support both types, such as UDB.	Create primary key (where applicable) if the intermediate table index setting is set to create a primary index. Create primary index/partitioning key (where applicable) if the intermediate table index setting is set to create a primary index.	No impact for SQL SERVER.
   Secondary Index Order	Defines whether an index is created before or after inserting data into a table.	Create the index after inserting data into a table. Create the index before inserting into the table.	To create an index after inserting data into a table, you have to select Explicit Table in VLDB setting for table creation type.
   Secondary Index Type	Defines what type of index is created for temporary table column indexing.	Create a Composite Index for Temporary Table Column Indexing. Create Individual Indexes for Temporary Table Column Indexing.