Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Slicing Analysis Services (SSAS) Partitions and more

Posted by denglishbi on May 15, 2009

Just wanted to touch base on a couple of items with partitions referencing the ever popular Adventure Works 2008 sample Analysis Service project available to download from CodePlex here – SQL Server 2008 SR1 downloads – it appears that they have bundled the databases together now, used to be broken out by the operational and the data warehouse.  The sample project file will be part of this download for Analysis Services and will be located in the following directory if you go with the default setup – C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project.

Now if you open up the Enterprise version you will see that the measures groups have multiple partitions defined for them.  One thing to note is that the counts are not properly updated and that aggregations have not been defined for all partitions in the cube.

image

If you have BIDS Helper installed you can go ahead and use the add-in to perform an update on the estimated counts for all of your objects.

image

Just a warning, if you are going against a very large database you might not want to perform this operation.

image

As a work around you could go ahead and simply update the estimated row count in the properties for the partition to provide Analysis Services an estimated row count when you define and generate the aggregations for the partitions (actually there is a Partition Count setting that you will see when you go through the aggregation design wizard that gets used by the algorithm when creating aggregations, so it is important to set these values).

image

After the estimated counts have been updated you will see that the counts are updated, but you would still need to design aggregations for the partitions where these have not been defined yet (you might need to save the cube file, close it, and reopen the file to see the updated counts).

image

To create the aggregations in SSAS 2008 you have to switch over to the new Aggregations tab in the cube.  You can then select the group that you want to design the aggregates for and walk through the wizard.

image

And you can generate the aggregates for all of the partitions at once.

image

Ok, now let’s get back to the partitions portion.  I am going to make a modification to the Internet Sales partition for 2004 and break this out into Q1 and then place Q2 in a separate partition.  This really doesn’t need to be done for the Adventure Works data since the volume of data is extremely small, but in a real world scenario this could definitely improve query performance (plus reduce processing time if you are just processing the latest quarter instead of the entire year).

image

I went ahead and modified the query of the existing 2004 partition so that the cutoff was less than 20040401 instead of 20041231 for OrderDatekey.  You need to be careful that you do not overlap the ranges, because there is no validation going on, so you could potentially include data that is already in an existing partition.  After I had modified the existing partition for 2004 and added the new partition I went ahead and updated the estimated counts.

image

Now that this is done let’s run a query against the cube and take a look at what is going on.  Here is the query that I will execute against the cube:

SELECT {Measures.[Internet Sales Amount], 
        Measures.[Internet Order Quantity]} ON 0,
NON EMPTY([Customer].[Customer Geography].[Country]*
        [Product].[Category].[Category]) ON 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Month].&[2004]&[4])

image

So the query executed and if we look at the Profiler trace we can see that the query actually touched multiple partitions for the measure group that we were querying information from.  The reason for this is because we have modified the basic partition from a single year.  If we would have left it at the year level it would have been fine, but since we are dividing this up into multiple parts now it does not know where to retrieve the data to satisfy the query.

Lets go back into the partitions in the cube and set the ‘Slice’ property for the partitions.  This is where you specify the tuple that defines the partition.

image

Now that we have this setup we will redeploy and run the query again.  You will need to define the ‘Slice’ property on each of the partitions in the measure group.

image

UPDATE (5/16/2009): fixed this picture (before I had highlighted the aggregation, not the partition)

Now that we have defined the ‘Slice’ for the partitions we see that our query only touches the partition that we are querying against and it was faster in response timeSmile  Granted this is a small set of data and using this doesn’t really make too much of a difference, but you can imagine what this would do to a very large dataset.  And if we switched over to reference the Fiscal hierarchy instead we would see the same results.

That is it for now, hope you enjoyed this little tip and I want to thank Siva Harinath and Howie Dickerman from Microsoft for their presentation at last year’s Microsoft BI Conference Designing High Performance Cubes in SQL Server 2008 Analysis Services where they pointed out this item.

About these ads

3 Responses to “Slicing Analysis Services (SSAS) Partitions and more”

  1. Kory said

    Dan, I think the reason your initial query didn’t use the auto-slice was because your row counts were below the 4096 threshold where SSAS builds indexes. If you lower this threshold and reprocess, your first query without slicers should pick only the 2004 partition. You can also change the query to the month level and it should still work. However, there are a number of articles and white papers which detail why should should always set the slice property because the auto-slice behavior isn’t perfect.

  2. Dan said

    If you leave the partitions setup with the default year setup then query runs and only one partition is accessed even without setting the Slice property. As soon as the partition is modified this adds the layer of complexity was appears to confuse the engine. Unfotunately with the small value of records in this database it might not be a good use case, but it does so the importantance of setting this property and not assuming that the query is running as you would expect. I will take a look at modifying that setting to a lower value and reprocessing the partitions to see if that makes a difference in this case.

  3. Sam Kane said

    Here are this and some other articles on SSAS Partitions: http://ssas-wiki.com/w/Articles#Partitions

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

 
Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: