Deploying the Loading Architecture

The final step is to deploy your chosen data loading architecture, which means defining all its related tasks and their execution order. To some, this may seem intuitively obvious (e.g., dropping bitmap indexes before loading fact tables). But I've found it better to spell out the tasks involved to be sure.

For transform, then load, the tasks would be:

• Drop fact table bitmap indexes.

• Perform transform, then load.

• Create fact table bitmap indexes.

• Gather new fact table statistics.

For load, then transform, the tasks would be:

• Perform load (i.e., data files to staging table).

• Drop fact table bitmap indexes.

• Perform transform (i.e., staging table to fact table).

• Truncate staging table.

• Create fact table bitmap indexes.

• Gather new fact table statistics.

Note that we truncate the staging table only after a successful move of the staging data to the fact table. This permits us to perform cumulative batch cycle loads. Thus, we can stage any number of batch cycles' data before promoting that data to the fact table. If you remember, this was a key advantage of the load, then transform approach.