Merging Analysis Services Partitions
Before merging partitions, it's helpful to understand the most common scenario for using multiple partitions and merging those partitions. If merging is not completed correctly, problems with double counting fact data may cause subsequent analysis results to be wrong. Regardless of how careful or well thought out your partition merging strategy, keep legacy data backed up periodically.
Common Partition Merging Scenarios
The single most common configuration for partition use involves the separation of data across the dimension of time. The granularity of time associated with each partition varies depending on the business requirements specific to the project. For example, divisions might be by years with the most recent year divided by months. Another design would be to divide by days with the most recent day represented by hours in day already passed. The most common configuration is to partition by year with the most recent year that contains months in year-to-date, plus a separate partition for the active month, which is regularly taking on new data. When the active month is completed, that partition is merged back into the months in the year-to-date partition and the process continues. At the end of the year, a whole new year partition has been formed.
Reasons for Partitioning Data
Besides pure data-size considerations, the reason the partition configuration described earlier is so popular is that it can provide the most time effective design for data storage in Analysis Services. For example, to process a cube containing one year of sales data for a company might take an entire day. Once the year 2004 is ended and the data for that year is processed, it is inefficient to continue adding new data to it, requiring a lengthy reprocess to bring all aggregations up to date. It is most effective to separate out the date for each year to its own partition.
See Also
Concepts
Partitions (Analysis Services)
Creating and Managing Local Partitions
Creating and Managing a Remote Partition
Configuring Storage
Setting Partition Writeback
Write-Enabled Partitions