This is something we've been looking at as part of our load (just an idea at the minute).
We have a multi-part process to load our tables as follows:-
- Load a "process list" table (this just gets any new/updated records and only contains basic information to identify the record affected)
- Load a working table with all the data used for that table (using the process list as a base)
- Merge the working table with the "proper" table
- Load/update all our DIM tables (kept in a separate database)
- Load/update all our FACT tables (kept in the same database as 4)
- Process cubes
- Will this still work in our scenario?
- We have cubes attached to the FACT/DIM tables. Can we use a similar methodology to have minimal downtime with our cubes (i.e. point the cubes to the shadow tables and the just rename the cubes after thay have processed)
We want to have minimal downtime (don't we all?) when updating the tables and we currently only do a load once a day. We are looking at trying several loads a day but need a method of keeping downtime to a minimum.
Any help on this would be greatly appreciated.