March 8, 2008 at 1:56 pm
Hi there,
We intend on using partitioning for some of our large tables and were hoping partitioning would eliminate the need of archiving data out of those tables.
I never used partitioning before so I have been reading different articles on the topic lately.
However one thing that most these articles have in comment is they recommend to "slide in" partition with new data and "slide out" the partitions containing the older data. Does 'sliding out' older data for archiving really necessary? We would really prefer keeping all our data available at one location. I was hoping that partitioning would minimize if not eliminate the need of having to archive data out.
FYI: The tables are used strictly for reporting purposes. Also the reports going after those tables are going after the replicated copy of our main database, not the live one. Also for the heaviest report we intend on generating daily aggregates and such to avoid going after years of raw data, etc...
Long story short is it acceptable in such scenario to never archive data out of the partitioned table?
to give you a heads up my next question will be to get some advises on how to automate the 'sliding in of new partition at the end of the week or month' part... I have been looking at the REAL project and seen how it's done there. Any other suggestions / advises?... but we can keep this discussion for the next post 🙂
Thanks!
May 26, 2008 at 8:54 am
Hi,
The necessity of keeping historical data should be business driven. if the Business wants to report on historical data (say 5 years), it should be kept.
Regarding partition, you can process only the current data in the cube by having partitions. All the historical data can be kept in 'frozen' partitions, meaning, they need not be processed and are used only for reporting purposes.
That said, if the fact tables size is huge, you should consider archiving older data (say more than 5 years of data). That's really a trade off between the business requirements and DW maintenance.
Hope this helps,
vijay.
Warm Regards,
Neel aka Vijay.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply