Recently I partitioned a fact table in Sql Server and not surprisingly when it came to the cube this table’s measure group also needed to be partitioned. The sql server partition was a sliding month partition with each of the 12 months ,the previous year, and an Archive partition.
Instead I allowed Sql Server to handle the partitioning for me by creating the measure group partitions based on views using the $partition function.
Select *
From Fact
Where $Partition.SlidingMonth(TransactionDate) in (15,14,13)
The first three months were partitions 15,14,13 etc.
I’ve also used the $Partition function to keep cube measure groups of relatively equal size.
Select *
from Fact
Where $Partition.SlidingMonth(TransactionDate) % 4 = 2
Remember to make sure that the partitions in Sql Server and the cube are working in tandem and that you’re not adding unnecessary complexity.
