Product of values in MDX

There was an interesting question on the Analysis Services newsgroup tonight which involved a requirement to produce a product of a set of values.

ie. value1 * value2 * value3 * .... valueN

You could do something like this with a unary operator, but that would change the aggregation for all measures.

In order to do this for just a single measure I proposed exploiting a technique I saw Itzik Ben-Gan use in T-SQL.

The basic mathematical proof for this technique was the following:

logN (val1*val2*...*valn) = logN(val1) + logN(val2) + ... + logN(valn)

logN (val1*val2*...*valn) = sum(logN(col_with_vals))

val1*val2*...*valn = EXP(sum(logN(col_with_vals)))

The question on the newsgroup related to trying to geometrically link quarter totals based on the following formula.

Date
====
2005
  Q1
     Month 1 - 2%
     Month 2 - 3%
     Month 3 - 2.5%
  Q2 - 3%
  Q3 - 4%

The formula for Q1 would be (1+2%*1+3%*1.2.5%)-1. See http://www.russell.com/ca/Investor_Services/Personal_Rate_of_Return.asp

Below is a rough sample against the AS2k5 Adventure Works sample database. I did not have a rate measure handy so I fabricated one from the 2 reseller order measures. I have included all the raw figures in the query so that you can confirm the results for yourselves.

WITH
   MEMBER Measures.QtyPerOrder as measures.[reseller Order Count]
      / measures.[Reseller Order Quantity]
  
MEMBER Measures.GeometricLinkedTotal as
     EXP(
       SUM(
         Descendants([Date].Calendar.CurrentMember
           ,[Date].[Calendar].[Month])
         ,LN(1+Measures.QtyPerOrder)
       )
     ) - 1

SELECT
   {[Measures].[Reseller Order Count]
   ,[Measures].[Reseller Order Quantity]
   ,[Measures].[QtyPerOrder]
   ,Measures.GeometricLinkedTotal}
ON COLUMNS,

   Descendants([Date].[Calendar].[Calendar Year].&[2003]
   ,[Date].[Calendar].[Month],
SELF_AND_BEFORE) ON ROWS

FROM [Adventure Works];

Update 21 Aug 2006: There is now also a Stored Procedure that will do multiplication at http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures. The stored procedure appears to offer a significant performance increase, so it would be well worth investigating.

Print | posted on Tuesday, July 18, 2006 9:30 PM

Comments on this post

# re: Product of values in MDX

Requesting Gravatar...
Great idea, I posted this to a newsgroup a while ago and never saw a response.
Left by Michael on Aug 21, 2006 8:22 PM

# re: Product of values in MDX

Requesting Gravatar...
Do you need to include anything in order to use LN() function ?
When i try to run your sample query on AS2K i get an error at the position where LN function is used. Any idea ?
Left by miner on Jun 25, 2008 8:35 PM

# re: Product of values in MDX

Requesting Gravatar...
AS2K would use slightly different libraries, the LN function might be called something different like LogN or seomthing. Unfortunately I don't have easy access to an AS2k box where I could check this for you.
Left by Darren Gosbell on Feb 04, 2009 12:09 PM

Your comment:

 (will show your gravatar)