SSAS : Partitions in a multidimensional cube

Introduction

When your fact tables grows, consequently your multidimenional cube will grow, too (if you have a multidimensional cube off course). You can increase your performance by splitting up the measures in partitions. Partitions allows you to divide a cube in one or more folders. These folders are usually placed on one or more hard drives for improved performance. Multiple partitions can have the following advantages (mssqltips):

• Increased performance by placing the partitions on different disks.
• Each partition can have it's own storage mode.
• Parallel processing of partitions
• etc. 

In this blogpost I'll show you how to create partition in SSAS by using the SSAS tutorial of MSDN. In this tutorial a cube is created on AdventureWorks2012DW database. The measuregroup internetsales is divided into partitions by year.

Creating the partitions

Open the cube and navigate to the partitions tab and click on New partition. A screenshot is shown in the picture below. Here you can zsee the standard partitions.

A wizard is opened and the following window appears. click on Next.

Change from Table binding to Query binding. Check the "internet tables" in the box Available tables and press OK

If you change to Query binding the following window is shown.The WHERE statement is not finished yet and there you have to enter your partition range.

The data of AdventureWorks is from 2005 until 2008. Below the first range is entered from 20050101 until 20051231. Now press on check and the messagebox "Syntax check succesful". Press on OK.

Now you have to enter the location of your partition. You can also process the partitions on different servers. Press OK

Do you want to design partitions by aggregating it? In this example I will not do that. Press Finish

Now The first partition is ready and now you can create the next partition.

Finally, when you have done this for 2005, 2006, 2007 and 2008 the partition tab will look like below.

Conclusions

It's is very easy to add partitions to a cube and a simple partition design is very straightforware and simple.

Greetz,

Hennie