Creating Indexes And Views
At this point, the warehouse manager has to create indexes against the information in the fact or dimension tables. One would expect the index creation time to be significant, even if we need only to create an index against a fact table partition. Because of this, most relational technologies have facilities to create indexes in parallel, distributing the load across the hardware and significantly reducing the elapsed time.
The overhead of inserting a row into a table and indexes can be far higher with a large number of rows than the overhead of re-creating the indexes once the rows have been inserted. Therefore it is often more effective to drop all indexes against tables prior to inserting large numbers of rows. Fact data that tends to have a large amount of data inserted on a daily basis is a prime candidate for dropping indexes before the data load. This method will be more effective unless the partition that is being loaded to already contains a substantial amount of data. Dimension data tends to change or be added to in far smaller volumes than fact data, so unless the dimension is being changed wholesale, retain existing indexes on the dimension tables. In order to fool user access tools into thinking that the fact table partitions are one large table, at this point in the process the warehouse manager creates views that combine a number of partitions into a single fact table. We would suggest that you create a few views, corresponding to meaningful periods of time within the business.