SSAS: banding measure values

In this first post, I’d like to talk about banding measure value by categorizing a measure according to ranges of values.

First let’s look at a typical requirement:
we have the data as follows -

sales_id salesman sales_amount
-------------------------------------
1 Mike $200
2 Mike $400
3 Phil $50
4 Phil $480
5 John $280
6 John $50

We want the result as follows:

sales_range distinct_count_of_salesman
--------------------------------
<$100 2
$100-$500 3
>$500 0

For this typical requirement, Marco Russo mentioned a solution in his great Analysis Services Advanced Best Practices presentation – writing the following sales_amount_range business logic in view for a new cube dimension to distinct count salesman:

CASE
WHEN sales_amount IS NULL OR sales_amount < 0 THEN 'N/A'
WHEN sales_amount < 100 THEN '<$100'
WHEN sales_amount <= 500 THEN '$500'
END

Now let’s look at the more complex requirement – what if the desired result looks as follows:

sales_TOTAL_range distinct_count_of_salesman
-------------------------------------------
<$100 0
$100-$500 1 (John : $100 <= ($280 [sales_id:5] + $50 [sales_id:6])
>$500 2 (Mike: ($200 [sales_id:1] + $400 [sales_id:2]) > $500, Phil: ($50 [sales_id:3] + $480 [sales_id:4]) > $500)

Because the distinct_count_of_salesman aggregate can be based on correlations salesman dimension with dynamic date dimension ranges, which makes pre-aggregation impossible, we have to resort to MDX solution.

More than 4 years ago when I worked on a business intelligence project, I had such a requirement and I asked for help on MSDN forum. Tomislav Piasevoli and Richard Less very kindly helped. Thanks Tomislav and Richard! So I copied Tomislav’s MDX solution as follows:

WITH
MEMBER Time.Count1 AS Aggregate(time1 : time2)
MEMBER Time.Count2 AS Aggregate(time3 : time4)
MEMBER Measures.[<$100] AS Count(Filter(NonEmpty(Customers.Members,
(Time.CurrentMember, Measures.Sales)
), Measures.Sales = 100.0 AND Measures.Sales $500] AS Count(Filter(NonEmpty(Customers.Members,
(Time.CurrentMember, Measures.Sales)
), Measures.Sales > 500.0))
SELECT
{ Time.Count1, Time.Count2 } on 0,
{ Measures.[$500] } on 1
FROM Cube

You can also find the original MSDN forum thread here.

One thing I’d like to quote from what Tomislav said in the MSDN forum thread is “In general, having a simple MDX is an indicator of good DW design. Having a complex MDX might be a good sign to reconsider your DW design. Or to use different approach, even technology sometimes.” Therefore, if there are possible efficient solution inside the cube, MDX solution will be much less preferred.