Partition Slice Impact on Query Performance – SSAS2005

I’ve been going through a case with MS and ths issue of setting a partition slice came up.

Based on what I read here, http://msdn2.microsoft.com/en-us/library/ms174764(SQL.90).aspx, I didn’t think that the slice had any impact on MOLAP data. The article states: Data slices are applicable only to objects that use the ROLAP storage mode.

Easy enough I thought, no need to ever set a slice on MOLAP data. Well, apparently I was wrong. There are some instances which you will want to set a slice.

Below is a response I received from a support tech:

Yes, the data slice indexing is done for you automatically for you, but that does not necessarily mean that you should ignore it.

1 – It is an excellent mechanism to ensure that the only data you actually load into the partition is the data that you expect. It is quite possible to load incorrect data and not even know about it if you don’t specify the data slice. Without the data slice, SSAS loads everything into the partition based on the table/query binding and indexes it as it comes in. With the data slice set, it generates an error message if the incoming data does not match the data slice. Excellent way to double-check your expected data.

2 – For small datasets (<4096 is the default), indexing is not done at all. Therefore w/o the data slice, the partition will have to be scanned for all queries – granted with <4096 rows this is pretty fast, but it might be an issue if you had many hundreds or thousands of partitions.

3 – The automatic data slice detection isn’t perfect. It only works if you have a single data slice and you have no control over the level that it uses to detect the slice value. If you load two quarters of 2006 worth of data into a partition, then the auto slice detection selects the lowest common member; or the year in this case. It won’t OR data slices together (i.e. it won’t set Q1 ‘OR’ Q2). Rather it goes for the single member that would have AND’ed them together, or 2006 in this case. This means you could have lots of false positives in the scan and more data has to be examined than would have been if you had come up with a better partition strategy.

4 – It isn’t just ROLAP as a storage mechanism, it is any kind of relational access, So, for example, this becomes critical if you are using proactive caching and you have a relational mode while re-building the partitions. And the obvious follow-up to that is that if you have mis-tuned proactive caching settings and don’t set the data slice then you might never get in MOLAP mode. So it is important in more than just classical ROLAP storage.

 

Thus my general best practice for small systems is to not set the data slice – but for large systems it is critical to think through many issues and whether or not to set the data slice is one that should always be raised.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Partition Slice Impact on Query Performance – SSAS2005

  1. Pingback: Set the Slice on your SSAS Cube Partitions now! - Some Random Thoughts

  2. Pingback: Are you setting ‘Slice’ property in SSAS partitions? « Says My Mind

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