June 29, 2011 at 12:01 pm
I am designing the load a Data Warehouse from a Data Mart. The existing data mart is partitioned by yyyymm. The warehouse will be partitioned the same way. The data will be loaded into the warehouse once a month for the last month’s data. The warehouse data is not updated or deleted during the month. I have two years of data available for loading and another 1.5 years of history being prepared. The goal is to have 5 years of data online.
The question is will I be able to load the partitions in any order? My plan is to load the most current and useful data first, then go back and fill in the previous months and history after the current data is loaded.
June 30, 2011 at 1:13 am
You can load data in any order using partitioning, so you will be able to do what you want and load more current data first, then older data later. The partition functions you define are what determine in which partition a row lands. Let's say your scheme partitions data in this way:
201106 --> partition1
201107 --> partition2
201108 --> partition3
If the first row I insert has a date of 2001107, then it will go in partition2. When I insert a second row and it's date is 201106, it will go in partition1. Storing a third row with a date of 201107 causes that row to be put in partition2, and so on.
LinkedIn - http://www.linkedin.com/in/carlosbossy
Blog - http://www.carlosbossy.com
Follow me - @carlosbossy
June 30, 2011 at 8:46 am
I guess I need to clarify my question more.
My plan is to load the data one partition at a time by loading a staging table and then merging it into the partition.
Can this be done in any partion or just the first or last partition?
July 8, 2011 at 6:47 am
michael.french 172 (6/30/2011)
I guess I need to clarify my question more.My plan is to load the data one partition at a time by loading a staging table and then merging it into the partition.
Can this be done in any partion or just the first or last partition?
Any.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 8, 2011 at 6:56 am
Just make sure you're up to speed on the rules for partition switching. Got caught out this week where we have a partitioned table but non-aligned indexes (ie index was partitioned, was on a different file group), this isn't a supported config for partition switching.
Steve.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply