Posted: 1/18/2012
Hello,
The DW does a full extract of all OLTP source tables into a staging area, drops all dimensions and fact tables, and repopulates them. The full rebuild in SSIS takes a couple of hours for 270GB stage data and 190GB in dimensions and facts.
Partition swapping looks to be a way to keep downtime for users to a minimum. A lot of articles focus on partitioning to implement add/changed records, which is fine, but in this case the 'new' partition would comprise a complete rebuild of the relevant cube, and then replace the 'live' partition.
To complicate matters the SQL Server version is 2008 R2 Standard and it can't be upgraded to Enterprise (to gain access to Partition Processing and Dimension Processing tasks) (reason: money...). So I guess the swapping would have to be accomplished in script tasks.
Is this scenario feasible?
I realise too that a drop/rebuild of the entire DW (which is what's currently happening) is highly inefficient. Here's the current process in summary :
SSIS : Drop stage table, rebuild / Extract OLTP data into stage table / (repeat for all staging tables) / Disable fact/dim table keys / Delete fact/dim table records / Load Dimensions, with new surrogate keys / Load Facts, including new surrogate dim foreign keys / Enable fact/dim table keys
SSAS : Process Full each dimension in turn, then Process Full each fact table.
The bulk of processing time is in SSAS (just) hence the interest in partition swapping. Bearing in mind the source OLTP data is flaky (hard deletes by users are permitted, and table indices are inconsistent), would it be faster to extract the full stage data area then use hashkey comparisons to insert/amend into the Facts and Dimensions?
Thanks for any info.
Dave