1. Database Administrator's Guide for Oracle Essbase
  2. Examples of Calculation Scripts for Block Storage Databases
  3. Calculate Product Share and Market Share Values

Calculate Product Share and Market Share Values

This Essbase calculation script example calculates product share and market share values by calculating each member as a percentage of the total and as a percentage of its parent.

The share values are calculated as follows:

  • Each member as a percentage of the total

  • Each member as a percentage of its parent

Assume that you added four members to the Measures dimension:

  • Market Share

  • Product Share

  • Market %

  • Product %

Example script:

/* First consolidate the Sales values to ensure that they are accurate */

FIX(Sales)
   CALC DIM(Year, Market, Product);
ENDFIX

/* Calculate each market as a percentage of the total market for each product */

"Market Share" = Sales % Sales -> Market;

/* Calculate each product as a percentage of the total product for each market */

"Product Share" = Sales % Sales -> Product;

/* Calculate each market as a percentage of its parent for each product */

"Market %" = Sales % @PARENTVAL(Market, Sales);

/* Calculate each product as a percentage its parent for each market */

"Product %" = Sales % @PARENTVAL(Product, Sales);

Essbase performs the following actions:

  1. Fixes on the Sales values and consolidates all the Sales values.

    The CALC DIM command calculates the Year, Market, and Product dimensions. The Measures dimension contains the Sales member and therefore is not consolidated. The Scenario dimension is label only and therefore does not need to be consolidated.

  2. Cycles through the cube and calculates Market Share by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of total Sales in all markets for each product (Sales -> Market).

  3. Calculates Product Share by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of total Sales of all products in each market (Sales -> Product).

  4. Calculates Market % by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of the Sales value of the parent of the current member on the Market dimension.

    The @PARENTVAL function obtains the Sales value of the parent on the Market dimension.

  5. Calculates Product % by taking the Sales value for each product in each market for each month, and calculating this Sales value as a percentage of the Sales value of the parent of the current member on the Product dimension.

    @PARENTVAL obtains the Sales value of the parent on the Product dimension.