Thursday, October 4, 2012

How can easily create global calculations



How can easily create global calculations

The power of OLAP cubes and Essbase is truly realized with ease of creating calculations.  OLAP cubes were created for in depth analysis and speedy queries.  The easiest way to create 'global' calculations in Essbase is to create a new dimension which contains all of the global calculations.
 
Example:  A company has a cube which calculates product revenue.  The cube contains 20 separate measures ranging from revenue dollars to inventory.  Let us assume that the company has a metric called "Per Case" that they would like applied to each of the 20 measures.  One approach would be to create a new calculation for each measure.  (Inventory per case, Revenue per case, etc.)  Although this approach does work, it is pretty maintenance intensive.  

A better approach might be to create a new dimension called "Metrics".  The metrics dimension would contain one stored member called "Amount".  All data in the cube would be tied to this one member on the Metrics dimension.

With the addition of the new dimension, it is now very easy to create a new 'global' calculation called "Per Case".  The formula would be as follows:  

Amount / Amount -> Cases;

You can tag the member as "dynamic calc" and "two pass" and you will have a global calculation which calculates the "per case" amount for all of the accounts.

No comments:

Post a Comment