Hi Simon,

I worked on a DW project that used exactly this strategy of loading the incremental rows into a partition and then switching it into the main table. We disabled the indexes on the switch in partition, load the data, rebuilt the indexes then switch in.

One big downside is that you need a schema lock to switch partitions so anyone querying your fact table will prevent you switching in the new partition. I cannot remember exactly how we solved this but we were looking at creating a snapshot and pointing the reporting tool to the snapshot which would free up the DB for partition switching. We would then drop and recreate the snapshot.

If you are loading data every couple of hours you might want to consider have a current day fact table with hourly partitions and then migrate that to the main fact table with daily partitions as part of an end of day/start of day process. You would need a view over the two tables to allow the reporting to be seamless to the users.

Jez