The DirectSlice Property And Aggregations

A bit of an obscure one, this, but useful to know nonetheless…

You may or may not be aware of the DirectSlice property, which can be set on a many-to-many relationship in the Dimension Usage pane in Visual Studio:

One of the few good descriptions of it comes from this old white paper:

http://technet.microsoft.com/en-us/library/ms345139(v=sql.90).aspx

I quote:

When a measure expression is defined, it may be expensive to compute the result for the all member of the Many-to-Many dimension if the product needs to be computed across an enormous number of rows. And in examples like this, the value for the all member is the simple sum of the fact table. There is one more property on a many-to-many dimension that accommodates this, the Direct Slice. This is a tuple on the Many-to-Many dimension, where the server is instructed to not compute the measure expression except where the cube author guarantees that the aggregate value of the facts matches the result of this expression. For example, in a scenario using measure expression for currency conversion where the sales facts are stored in US Dollars, Currency.USD would be the DirectSlice in the Currency dimension.

Basically, what it does is let you tell SSAS to not evaluate the m2m relationship if a specific selection has been made and if doing so does not change the output of your query. Doing this could improve query performance if evaluating the m2m relationship is expensive – which it often is.

Here’s an example. The screenshot above shows a simplified example based on the Adventure Works cube containing two measure groups, Currency Rate Facts and Internet Sales Facts, and three dimensions, Source Currency, Destination Currency and Date. Destination Currency has a m2m relationship with the Internet Sales Facts measure group that is resolved through the Currency Rate Facts measure group; it also has IsAggregatable=False set and the US Dollar member is the default member.

Now, if you run a query to get data for a measure by year, like this:

select

{[Measures].[Sales Amount]} on 0,

[Date].[Calendar].[Calendar Year].members on 1

from 

[DirectSliceTest]

…and look in Profiler, you’ll see that both the measure groups are being queried by the Storage Engine – as you would expect.

What’s more, if you build an aggregation on the Internet Sales Facts measure group at the correct granularity, you’ll see this aggregation being used by the query:

Incidentally, there’s a widely-repeated myth that you can’t use aggregations with m2m relationships or measure expressions – this is not true, although you do have to understand the granularity of data that the Storage Engine is querying and building your aggregation in the right place.

Now it’s time to set the DirectSlice property. In this case I set it to a tuple containing US Dollars, which is as I said earlier the default member on the Destination Currency dimension:

([Destination Currency].[Destination Currency].&[US Dollar])

Remember that by setting this property, I’m telling SSAS that it doesn’t need to evaluate the m2m relationship when this member is selected on Destination Currency because the results of any queries will be identical.

If you rerun the query then there are two things to notice in Profiler:

First of all, the Currency Rates Facts measure group is no longer being queried, and so the m2m relationship is not being evaluated. This is good. Second, the aggregation seen above is no longer being used. In fact based on my research there’s no way to get an aggregation to be used when the DirectSlice property is set and the m2m relationship is being bypassed (and I’ve tried several variations, even building aggregations that include attributes from the Destination Currency dimension). This is not good. However as soon as you select something other than what is specified in the DirectSlice property, and the m2m relationship is being evaluated, aggregations can be used again.

In summary, then, using the DirectSlice property is a good idea if you have an expensive m2m relationship and you don’t expect your queries to use aggregations. However if you do need to use aggregations to improve query performance you probably should not set the DirectSlice property.

[Thanks to Akshai Mirchandani for helping with this]