Siebel Analytics Performance Tuning Guide > The ETL Process > Drop and Recreate Indexes >

Batches for the Initial Extract


You can use the following approach for the Full_load_Siebel_DW and Full_Extract_Siebel_DW batches which perform the initial extract and load of Siebel Data Warehouse.

The step Drop_Image_Indexes (drop_Image_indexes.sql) drops the Image table indexes and should be executed after the session Extract_RestartNextBatch1 (Should be executed against the OLTP).

The step Create_I_Image Indexes (create_i_image_indexes.sql) creates the Image table Index after the Image table is built and should be executed after the batch CreateIncrementalImage (Should be executed against the OLTP).

The step Drop Dimension and Fact Indexes (drop_all_indexes.sql) drops all the dimension and fact indexes after the staging tables are populated and prior to executing Full_Load_Siebel_DW batch.

Figure 9 includes the batches you can use to drop and create indexes before and after the initial extract loads.

Figure 9. Initial Extract Post Session Dummy Batches

Click for full size image

The step CreateHouseHoldIndexes (create_household_indexes.sql) should be executed after the batch Dimension2 under the folder Full_Load_Siebel_DW_Dimensions.

The step Create Dimension Indexes (create_Indexesfor_fact .sql) should be executed after the batch Dimension3 under the folder Full_Load_Siebel_DW_Dimensions as shown in Figure 10.

Figure 10. Initial Extract Post Session Dummy Batches

Click for full size image

The step Create Service Request Indexes (create_servicerequest_indexes.sql) should be executed after the batch Fact3 under the folder Full_Load_Siebel_DW_Facts.

The step Create Indexes For Aggregates (create_indexes_for_aggreagates.sql) should be executed right after the batch Load_Hierarchy batch under the folder Full_Load_Siebel_DW_Facts.

The step Create Rest Indexes (create_rest_indexes.sql) should be executed after the batch UpdateRowImange under the folder Full_Load_Siebel_DW_Facts.

The step Create R Image Index (create_r_image_indexes.sql) should be executed after the batch UpdateRowImage. (It should be executed against the OLTP). See Figure 11.

Figure 11. Create R Image Index

Click for full size image

The step Create SCD Indexes (create_scd_indexes.sql) should be executed after the batch SCD under the folder Full_Load_Siebel_DW_SCD as show in Figure 12.

Figure 12. Create SCD Indexes

Click for full size image

The step Drop Aggregate Indexes (drop_aggregate_indexes.sql) should be as the first session under the folder Load_Aggregates.

The step Create Aggregate Indexes (create_aggregate_indexes.sql) should be executed after the batch LoadAgg2 under the folder Load_Aggregates. See Figure 13.

Figure 13. Create Aggregate Indexes

Click for full size image


 Siebel Analytics Performance Tuning Guide 
 Published: 18 April 2003