SSAS – Configuring the Slice Partition in your cube.

After recently going through an online video, I realized that I had not set the Slice option on the partition on a larger cube that I had. And by putting in the slice details this would then enable me to get faster queries when people were accessing the data.

Below are the steps that I did in order to configure the Slice details for a partition on your cube.

Example:

·         Configuring the slice for the Partition in our Adventure Works DW 2012, for each year that we have created a partition.

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

1.       The first thing that I had to ensure that was for each Partition in my cube, the corresponding table only had the correct date values.

a.        NOTE: In my actual data for some reason this was not 100% correct, so I ensured that each underlying SQL Partition had the valid dates in each configured partition.

2.       Next open up SQL Server Data Tools (SSDT) or in SQL Server Management Studio (SSMS).

3.       Then go to your Partition Window and click on a specific Partition

a.        In our example we went into our Partition called:

                                                               i.     

b.       Then you can either right click to go into the Properties or double click.

4.       Next you are looking for the section that says Slice

a.       

b.       Click on the Ellipses to go into the Partition Slice – Total_Sales_2005

5.       Next in order to configure our slice, we need to see how the data has been partitioned.

a.        As with our example it is partitioned by Year.

b.       So from our Metadata we can go down to Date click on the Date, then Calendar, then the Calendar Hierarchy, then Calendar Year.

                                                               i.      Now select CY 2005 and drag this into the Expression.

c.        It will now look like the following:

d.      

e.       Click on Check to ensure that your syntax is correct.

6.       Click Ok twice to go back to your SSDT or SSMS

7.       Now Process the Partition

8.       It should now come back with the following:

a.       

9.       Now when you run your MDX query you should see the following when querying data from 2005

a.        This is the query I ran

Selectnonempty {[Measures].[Sales Amount]} on 0

,nonempty {

[Date].[Date].&[20050701]:[Date].[Date].&[20050731]

} on 1

from [Adventure Works]

b.        

10.    And this was the result from Profiler

a.       

Which we can now see only used the Total_Sales_2005 Partition