Siebel Customer-Centric Enterprise Warehouse Installation and Configuration Guide > Initializing and Populating the Siebel Customer-Centric Enterprise Warehouse >

About Improving the Query Performance of Siebel Customer-Centric Enterprise Warehouse

After the initial ETL load you can run a script to create indexes and improve the query performance. These indexes are helpful when running queries against the Siebel Customer-Centric Enterprise Warehouse. These indexes can be created after the full ETL load as they are not required during the ETL load.

The Siebel Business Analytics Installer creates three folders—DB2UDB, Oracle, and MSSQL. In each folder there is a subfolder called Query Performance. In each Query Performance folder there is a file called create_indexes_<db_platform>.sql. After the initial ETL load, run the script to create all additional indexes to enhance the query performance of your Siebel Customer-Centric Enterprise Warehouse. You can enhance the script by adding parallel statements for database servers that have multiple processes. Also, the Database Administrator can split the script into multiple scripts and run them in parallel.

After creating these indexes the Database Administrator runs the appropriate, update-statistics operation for the customer RDBMS to update the statistics on the index statements.

For Oracle, the script creates a bitmap index on every foreign key in a fact table. These bitmap indexes enhance the performance during the query time but they are slow to update during data inserts or updates. It is recommended that you drop these indexes before the ETL incremental run and recreate the indexes after the run. You can create a PowerCenter mapping to call a drop index script and another mapping to call a create index script.

It is possible that the bitmap index on a foreign key could prevent the optimizer from using the multicolumn index. This happens when there is a filter on some dimension, which the optimizer assumes would reduce the number of selected fact records. If this reduction does not happen, then the foreign key index can slow the query. The Database Administrator can delete or deactivate the index.

You can build more aggregates on the large fact tables to reduce the number of indexes and increase the report performance. You need to compare the time taken for updating the indexes on a fact table with the time required to update the aggregate table during the ETL incremental run.

For more information on the general guidelines for setting up the Siebel Data Warehouse, see Siebel Analytics Applications Installation and Administration Guide.