June 18, 2007 at 8:57 am
I have a 2005 x64 server that we are migrating to from a 2005 x86 environment. I have a Production data warehouse that has several dimension tables and fact tables. Obviously, the fact tables are read-only. Whereas, the dimension tables are loading on a nightly basis to feed the fact tables. I am setting up the new environment to follow an archival scheme that is VERY conducive to a flexible backup/restore model. In order to do this, I am creating multiple filegroups to accommodate the dimension tables and multiple filegroups to accommodate the fact tables.
My question primarily revolves around partitioning the fact tables. What I'd like to do is to use about four partitions per fact table where each partition table would be stored within it's own filegroup. That way if I need to do an 'on th fly' restore... I can restore the filegroup and be done with it, not having to reload the entire fact table. The partitions are based upon date (Fiscal Year and Fiscal Month). I have logically split the partitions (in the model) as follows... Partition One - Historical Fiscal Year (any data before the Prior Fiscal Year), Partition Two - Prior Fiscal Year, Partition Three - Fiscal Year/Closed Month, and Partition four - Fiscal Year/Open Month.
Which scenario would make more sense to accomplish this? A ranged partition, or a sliding-window partition?
June 21, 2007 at 5:55 am
"the fact tables are read-only. Whereas, the dimension tables are loading on a nightly basis to feed the fact tables"
Are you sure?
This sounds like textbook sliding window. Here is a good resource for that.
http://www.microsoft.com/technet/prodtechnol/sql/2005/realpart.mspx
Patrick G.
June 21, 2007 at 7:12 am
Thanks for the response Patrick. Yes, I am very sure. The DB was set up prior to my life here. No matter... it does work quite well for what we are using them for (Reporting - WebFocus). And I was leaning towards a sliding window scenario. At least, that is how I started the partitioning and FileGroup discussion, in regards to my proposal for this. I just wanted, I guess, a second opinion to make sure that my gut level feeling was correct.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply