In Microsoft Excel you can calculate the sum of products (A x B) for multiple lists of numbers (arrays) using a formula such as =SUMPRODUCT(D3:F3, D4:F4). Some common examples of when you may need this include weighted averages or an accumulated sum of products (quarter or year to date perhaps). I'm sure the math gals and guys in the room have other examples.
In this post I'll show one possible way to recreate the "SUMPRODUCT" formula in ASO databases using the MDX scripting language.
Requisite MDX
According to the Essbase Tech-Ref, MDX includes a Sum() function that takes up to two parameters as follows:
- A set of members (or Tuples) to "summarize"
- A numeric value expression (optional)
The first parameter is the list of members or tuples that we want to aggregate. My examples will be relative to Periods (months) so the first parameter will include sets of members from Period such as [Jan]:[Mar].
The second parameter is a "numeric value expression" which just means an MDX expression that returns a value. The documentation goes on to say...
Commonly used to restrict the aggregation to a slice from a Measures dimension (see MDX Grammar Rules). In the example below, [Measures].[Total Expenses] is the numeric value expression provided to the Sum function.
This is true but you are not required to limit your formulae to filtering slices in the ASO database. You can also include more interesting math provided that your expression returns a value. Here is an example MDX formula to achieve a sum of products across time periods by multiplying two slices (two members from the Accounts dimension):
Sum([Jan]:[Mar], ([A] * [B]))So what is this doing exactly? This formula is applying the numeric value expression of [A] * [B] to each member from the set (Jan, Feb, Mar) and summarizing the product calculated for each. It may help to see it written out in long form:
(([Jan],[A]) * ([Jan],[B])) + (([Feb],[A]) * ([Feb],[B])) + Etc...Year to Date Sum-Product Example
If you need a dynamic range of dates, such as when you want a Year-to-Date sum-product for example, you could use something like this:
Sum(PeriodsToDate([YearTotal].Generation, [Period].CurrentMember)
, ([A] * [B])
)Note: This assumes you are looking for a "year to date" sum of products and that all periods should come as descendants of a member called "YearTotal". You should adjust these concepts to suite your own databases as needed. If you have a more clever way of doing YTD, QTD, Etc. feel free to shoot me an email and I'll add your example.
Sum-Product in ASO Plan Types
If you were implementing "Sum Product YTD" in an ASO plan type you might have the calculated member show up on a form based on some user inputs such as Rate (A) x Volume (B) like this:
Sum-Product or Sum-Whatever...
You probably already made the leap on this one but just to spell it out, you are not limited to calculating the sum of products. Your formula may be the sum of any numeric value expression. (Add, Subtract, Multiply, Divide, Sum-of-Squares, Etc...) as described in the Tech-Ref:
For each tuple in set, the numeric value expression is evaluated in the context of that tuple and the resulting values are summed up.
NB: Keep in mind that as your MDX becomes more complex you may need to adjust the Essbase config setting MAXFORMULACACHESIZE. When in doubt, Essbase will throw a Not enough memory... error when this becomes an issue.
Sum-mary
A-ha, I see what you did there... This was an example of how you might do this in MDX. It doesn't mean that you should do this. Multiple layers of MDX in ASO can get hairy, especially when solve order comes into play. You may also consider decomposing this to achieve the same results using multiple members / formulae in MDX. In some cases, just sticking with the default Essbase outline aggregation is sufficient and even preferred so I would say keep it simple, until you can't.
I hope this was useful for you and that it helps you along the way.
Cheers!