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.      clip_image002

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.        clip_image004

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.       clip_image006

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.        clip_image008

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.        clip_image010

Which we can now see only used the Total_Sales_2005 Partition

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s