How to handle SSAS cube partition – a practical approach

Check list of all posts

Requirements

1. Partition will be performed for fact tables that has more than 2 Million records
2. Partition will be implemented in the cube as well
3. Both table partition and cube partition will be consistent
4. Table partition will be implemented by SQL script
5. Cube partition will be implemented by XMLA script
6. Table partition will be setup 2 years ahead
7. Cube partition will be setup 2 years ahead, consistent with Cube
8. All partitions will be done based on DateKey
9. All partitions are NOT shared with same partition scheme.

Approach

Partition will be done based on number of records, It could be yearly, or quarterly, but definitely not monthly. PARTITION FUNCTION will use RANGE LEFT. Please see sample below
CREATE PARTITION FUNCTION YearlyPF (bigint) AS RANGE LEFT FOR VALUES (20061231, 20071231, 20081231, 20091231, 20101231, 20011231,)
CREATE PARTITION SCHEME YearlyPS AS PARTITION YearlyPF TO (FGP,FG2007,FG2008,FG2009,FG2010,FG2011,FGF)

#	File Group	Date Range		Cube (where)
		From	To
P	FGP	-oo	20061231	<=20061231
1	FG1	20070101	20071231	Between 20070101 and  20071231
2	FG2	20080101	20081231	Between 20080101 and 20081231
3	FG3	20090101	20091231	Between 20090101 and 20091231
4	FG4	20010101	20101231	Between 20010101 and 20101231
5	FG5	20110101	20011231	Between 20110101 and 20011231
F	FGF	20120101	+oo	>=20091001

Implementation

1. 
   

   1. Edw partition

Based on the number of records for all fact tables, some fact tables are identified to do partition with DateKey.

SQL script

• Drop primary key as it is NOT used at all

Alter table Fact_table_name drop CONSTRAINT [PK_ Fact_table_name]

• Create new clustered index

CREATE CLUSTERED INDEX IDX_ Fact_table_name ON Fact_table_name (Date_key) ON MonthlyPS (Date_key)

Mart partition

Mart partition is supposed to be the same as partition of the matched fact table. The real difference that different script XMLA will be used to implement. For example, the script below creates a new partition for the cube measure group InsuranceMartTEST.[Fact Auto Policy Premium]

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <ParentObject>
       <DatabaseID>InsuranceMartTEST</DatabaseID>
       <CubeID>Monthly Auto Policy Premium Mart</CubeID>
       <MeasureGroupID>Fact Auto Policy Premium</MeasureGroupID>
   </ParentObject>
   <ObjectDefinition>
       <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
           <ID>Fact Auto Policy Premium 2009</ID>
           <Name>FactAutoPolicyPremium 2009</Name>
           <Source xsi:type="QueryBinding">
               <DataSourceID>Edw</DataSourceID>
               <QueryDefinition>SELECT * FROM [FactAutoPolicyPremium] where MonthEndDate&gt;20090101 and MonthEndDate&lt;=20091231</QueryDefinition>
           </Source>
           <StorageMode>Molap</StorageMode>
           <ProcessingMode>Regular</ProcessingMode>
       </Partition>
   </ObjectDefinition>

</Create>

Based on this pattern, we can generate script for each measure group to perform the partition based on given parameters, such as @CubeID @MeasureGroupID,@PartitionID, @Name and date range