When it comes to level of Fact tables and Dimension tables having billions of rows, partitioning becomes important. It will be perfect to partition the fact data in terms of Month. Depending on the growth of data we can partition the data either on months or on Quarter.
Partitioning tables in Datawarehouse would ensure that:
- More flexiblity to access data
- Easy to Recover the data
- Easy to Monitor the data
- Easy Retreival of data since query needs to check only in one partition
- Easy to port data from one media to other
- Easy to Maintain the Partitions