Using SQL Partitions and the $partition function to define Cube Measure Group Partitions

 

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.