Dan English's BI Blog

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.

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.

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

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

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

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.

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

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

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.

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])

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.

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.

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