THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

How to aggregate values as a product (multiplication) in MDX

MDX has some built-in aggregation functions - Sum, Count, Min and Max. There is also an ubiquitous Aggregate function, which will aggregate values according to the current measure aggregation function, which also adds Distinct Count and all of the semiadditive measures. But what if we need to compute not the Sum, but rather the Product (Multiplication) of values. The example that we will look here is somewhat artificial, but it is done on Adventure Works cube, so it can be repeated. Let's assume that we need to multiply the weights from the leaves of the Organization hierarchy. We can easily extract these weights with following query:

with 
  member Measures.Weight as [Organization].[Organizations].unary_operator
select 
  {Measures.Weight} on 0,
  Descendants([Organization].[Organizations],,Leaves) on 1
from [Adventure Works]

What we would like to do now is to aggregate them as product (multiplication), i.e.

with 
  member Measures.Weight as [Organization].[Organizations].unary_operator 
  member Measures.ProductOfWeights as Product(Descendants([Organization].[Organizations].CurrentMember,,Leaves), Measures.Weight)
select {Measures.SumOfWeightLogs, Measures.ProductOfWeights} on 0 
from [Adventure Works]

Unfortunately there is no MDX function called 'Product' which does such aggregation, but we can work around it. We just need to remember two math rules related to logarithms that we all learned in the 6th grade:

1. ln(a*b) = ln(a) + ln(b)
2. a = e^(ln(a))

Combining these rules we get the following:

a*b = e^(ln(a*b)) = e^(ln(a)+ln(b))
This equation allows us to convert multiplication into addition, which we can do easily with MDX's Sum function. So we need to compute logarithm at leaves, then Sum them up, and then use this Sum as exponent for power operator.

In order to do this we need to know two things:

How to take logarithm in MDX

This is a little tricky. Sure everybody knows that since OLAP Services 7.0 it was possible to use Log function inside MDX. In AS2005 it actually needs to be escaped and written as [Log], since the Log became a keyword in the parser. But it's just a little syntax thing, the real issue is that there are actually two different Log functions, and while they share the same name - they do different things ! Let's explain what's going on. Log is not a built-in MDX function. It is taken from either VBA or Excel. And both VBA and Excel export Log function. To disambiguate, we can use VBA![Log] and Excel![Log] respectively. The issue is that VBA![Log] function computes natural logarithm, i.e. base e. And Excel![Log] function computes decimal logarithm, i.e. base 10. We will use decimal logarithm alias from Excel - Log10 for three reasons

  • It is easier to write 10^x than e^x in MDX, since instead of e we would need to use approximation such as 2.718281828...
  • The name Log10 disambiguates which base of logarithm we are using
  • There is no need to write [Log10], since Log10 is not a keyword :)

How to compute power in MDX

It is not well publicized, but ever since OLAP Services 7.0, MDX always had a power operator. We already used its syntax a^b throughout the article. It is actually useful by itself for things like computing roots of the numbers. For example, one of the common questions - how to compute square root in MDX, since there is no SQRT function - can be answered by power operator. SQRT(x) = x^0.5

Putting it all together

Now, let's convert all this math to MDX.

a*b = 10^(log(a*b)) = 10^(log(a)+log(b))

applied to leaves of Organization becomes

with 
  member Measures.Weight as [Organization].[Organizations].unary_operator
  member Measures.WeightLog as log10(Measures.Weight)
  member Measures.SumOfWeightLogs as Sum(Descendants([Organization].[Organizations].CurrentMember,,Leaves), Measures.WeightLog)
  member Measures.ProductOfWeights as 10^Measures.SumOfWeightLogs
select 
  {Measures.ProductOfWeights} on 0
from [Adventure Works]

	
Published Monday, December 18, 2006 11:23 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement