In SSAS multidimensional there is a concept within creating partitions for your measure groups called slicing. Slicing can be thought of as the definition Analysis Services uses to find data within partitions at query time. We are all aware of the benefit that creating multiple partitions can have in improving Analysis Services multidimensional performance, but how can partition slices help us further? As per BOL, “Explicitly setting the Slice property can improve query performance by overriding default slices generated for MOLAP and HOLAP partitions”. This means that slicing partitions ourselves could have a profound impact on query performance.
Let’s take a look in more detail. Starting off, we’ll turn SQL Server Profiler on against our SSAS instance. For this post, I’m just going to use the Adventure Works 2012 cube. My partitions currently in my SSAS solution appear like the below for Internet Orders, partitioned by year:
Now let’s an MDX query to show the problem:
SELECT [Measures].[Internet Order Count] ON 0,
[Date].[Calendar].[Calendar Year].& ON 1 –2008
FROM [Adventure Works]
Running a profiler trace against this clearly shows our problem:
We are looking for data in one specific partition (2008), yet the formula engine thought that it needed to look through every partition to find the answer! In a large cube, the impact of this can be disastrous. Scanning every partition is very inefficient when we know from a logical standpoint that our partition strategy should answer our question.
During query time, SSAS internally reads the partition header files to determine what partition contains the data it is trying to read. Navigating to a deployed cube to an Analysis Services instance in File Explorer (typically located at C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data), under the cube name folder, cube name.cub folder, all of the measure group partitions are located. Inside of these folders, generally a few levels down, lives an info.*.xml file, which contains relevant information for analysis services on what is contained in that partition. Frequently, it seems, the automatic detection available in MOLAP storage mode doesn’t always detect the partition where it belongs, and ALL partitions are scanned. So how can we fix this issue? Fortunately, there is a property available in SSAS that tells the engine specifically what a partition is sliced by, allowing us greater control over performance by targeting where query scans are sent during execution. Opening up our project in data tools, on the partition screen inside of the cube, I am going to select each partition and set the partition slice property:
Re-processing the project and re-executing the query yields the following results in profiler:
Voila, which is what we are looking for! Now our queries will be much more efficient when being sent from client applications, only hitting relevant partition data.
So we’ve learned that it is very important to set the slice property when creating partitions. However, it is important that these slices should correspond to the where clause in the partition inside.