Are you setting ‘Slice’ property in SSAS partitions?

Are you setting ‘Slice’ property in SSAS partitions? If not, you should. Here’s what happened with me.  We have a fairly large SSAS cube. Initially this cube was having only two partitions, current data and historic data. The performance was degrading and so we partitioned the cube by month since majority of the queries are by month and below. This also helped us in improving the processing performance because we can now process only those partitions for which data has changed, two or three at the maximum. On the downside though, there are large number of partitions in cube; a fact we decided we can live with.
So I created the partitions and got the cube processed. I wrote a simple MDX query to observe how cube responds in profiler. Here is the query (slightly changed for anonymity).

SELECT
[Measures].[Measure Dummy] ON COLUMNS
FROM [Playbox]
WHERE [Period].[YearMonthDaySettlementPeriod].[Calendar Year].&[2010].&[4]

And here’s what I was seeing in profiler.

Without Slice all partitions are scanned

Without Slice, all the partitions are scanned

Hang on! I am querying only April 2010 data, why are those scans on all the other partitions? This defeated the whole purpose of partitioning by month because queries were scanning all the partitions. As mentioned earlier, we had a number of partitions and this is bound to have an impact.Even after creating partitions I was still not getting the benefit I wanted.

Now on the crux of the matter. As it turns out (after spending hours on google, msdn), automatic detection of Slice of SSAS 2008 is not so effective. Hence even though the partitions are clearly for a month, SSAS still has to scan all of them to get data. There are a number of blogs which elaborate the issue. I will just point to this, this and this.

So I set the slice property on each partition, run the same query and guess what, it is hitting only the required partition.

With Slice defined, only required partition is scanned

With Slice defined, only required partition is scanned

The performance benefit was quite apparent as well. I selected a very common real life query. I noted the query timing using the AS Performance Workbench which I have mentioned in my previous blog.  As you can see in the below images, the response time improved dramatically.

Query Duration without Slice

Query Duration without Slice

Query duration with Slice

Query duration with Slice

So the bottom line is “Always define slice when you create SSAS partition”. This has to go in my list of things to look for when you are building SSAS cube.

Advertisements

2 comments

  1. Is it needed to set the slices for MOLAP? For SQL2008R2? Most of the related blogs are about 2000/2005. My tests shows it works fine without slice.
    SQLCat says ‘Cube partitions each have a slice for each attribute. This slice is either set by the administrator as part of the partition definition, or for MOLAP partitions is created during creation of the bitmap index for the partition.’
    And in ‘Analysis Services Performance Guide SQL server 2008, October 2008’ you can find:
    ‘For MOLAP partitions, during processing Analysis Services internally identifies the range of data that is contained in each partition by using the Min and Max DataIDs of each attribute to calculate the range of data that is contained in the partition. The data range for each attribute is then combined to create the slice definition for the partition. Knowing this information, the storage engine can optimize which partitions it scans during querying by only choosing those partitions that are relevant to the query.’

    1. Hi Aar ded,
      You are right that most blogs are about 2005 (I wont go so far as 2000) but I observed this behaviour on SQL2008R2. It seems like there is some confusion on the issue and the mechanism of auto detecting the slice and eliminating partitions during query exectution is not perfect. The second link has some explanation on the behaviour. To be on the safer side, I would always define one unless there are compelling reasons not to do so.
      HTH

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