Fact Table Loads

[bi_at_nj]

Here is a scenario:

* Fact table contains 100 million records
* Monthly Load of 10million is done at the end of the month to the same fact table

In this scenario, how do you handle the indexes in fact table at the time of load?
If indexes are made unusable before load, then the rebuild index is time consuming after the load.

So, what strategy do you follow in such scenarios?

How do we ensure that the data is made available to the users in the shortest possible time. (Assume we could receive data only in the end of the month and should be made available the earliest in the fact table for detailed drilling purposes)

- Thanks,
bi_at_nj

[amarpal]

Partioning your fact table will definitely help you. After an appropriate partioning strategy is in place, you can try both the following approaches and pick up the one that is faster (it may depend on the datatypes and the actual data in the fact table):

1. Drop indexes before the load, load the data, rebuild the indexes.
2. Leave the indexes as they are. Don't drop them ever. Just keep loading data every day.