I started a long
conversation about this subject in the
MDSN SSAS forum. I think that it's a question and a principal that every advanced MDX programmer should be familiar with.
It all started with a customer that needed a standard deviation aggregation. I thought that it would be simple because there's a
StdDev function in MDX, but it turned out that my customer had major plans for me: He wanted this aggregation to act for every dimension he puts on his axis. This means that the aggregation is not defined over a specific dimension (such as date), but the std-dev is defined over the current dimension in the axis.
The solution for this problem consists of a principle and an answer.
The Principle
Aggregation or a measure that is based on the current user's query is bad. This can and will result two users to see different results using the same measure. This will cause confusion and disinformation. The sacred principle of One Truth will be desecrated. Taken from the thread, in
Chris Webb's words:
"
I quite often see
people wanting to write calculations that behave differently depending
on the query that's being run, and I always tell them not to do it. You
can hack something but it's almost impossible to get it work properly
for every single possible query - MDX just doesn't work like that"
In the end I explained that to the user and he agreed. One more reason for his approval is that std-dev often doesn't really says something about the data. In other words, it isn't informative. "The standard deviation is 0.432. That means that... ???"
The AnswerIf you (or the customer) still insists on that crazy measure, the following MDX will work.
With
Member [Measures].[RowSTDOrders] as
iif(Count(NonEmpty(StrToSet("Axis(1)").Item(0).Hierarchy.Children,
{[Measures].[Order Quantity]}) as ChildSet) < 2,
Null,
StDev(ChildSet, [Measures].[Order Quantity]))
select
[Date].[Calendar Year].[Calendar Year] on 0,
Non Empty [Product].[Product Categories].Members on 1
from [Adventure Works]
where [Measures].[RowSTDOrders]
Thanks for Deepak Puri for this code. Notice that the StrToSet function will cause performance degrade, but this is the only way that the code will also work in MDX script and not only in queries.
P.S
It doesn't matter if you write StDev or StdDev.