Developing Formulas on Aggregate Storage Outlines

Formulas calculate relationships between members in an Essbase outline. Aggregate storage (ASO) formulas are different than block storage formulas. You write aggregate storage (ASO) formulas in MDX as numeric value expressions, and apply them to the cube outline, where they are dynamically calculated upon data retrieval.

DIfferences Between ASO and BSO Formulas

If you are familiar with formulas on block storage (BSO) outlines, note the differences with formulas in aggregate storage (ASO).

Essbase provides a native calculation language to write formulas on block storage outlines. To write formulas for aggregate storage outlines, use the MDX (Multidimensional Expressions) language.
Apply formulas directly to members in the outline. For block storage cubes, formulas can be placed in a calculation script. For aggregate storage cubes, you cannot place formulas in a calculation script.

Formula Calculation for ASO Cubes

Essbase calculates formulas in aggregate storage outlines only when data is retrieved. Calculation order may affect calculation results. Whenever you use MDX formulas on multiple dimensions in an aggregate storage outline, it is good practice to set the solve order for each member or dimension. See Calculation Order and Solve Order in ASO Cubes.

Note:

When designing an aggregate storage calculation, consider that aggregate storage members with MDX formulas are dynamically calculated. The dynamically calculated members have a value of #MISSING until they are queried.

Using MDX Formulas

An MDX formula must always be an MDX numeric value expression. In MDX, a numeric value expression is any combination of functions, operators, and member names that does one of the following actions:

Calculates a value
Tests for a condition
Performs a mathematical operation

A numeric value expression is different from a set expression. A set expression is used on query axes and describes members and member combinations. A numeric value expression specifies a value.

A numeric value expression is used in queries to build calculated members, which are logical members created for analytical purposes in the WITH section of the query, but which do not exist in the outline.

The following query defines a calculated member and uses a numeric value expression to provide a value for it:

WITH MEMBER
 [Measures].[Prod Count]
AS
 'Count (
    Crossjoin (
     {[Units]},
     {[Products].children}
    )
  )', SOLVE_ORDER=1
SELECT
 {[Geography].children}
ON COLUMNS,
 {
  Crossjoin (
     {[Units]},
     {[Products].children}
    ),
   ([Measures].[Prod Count], [Products])
 }
ON ROWS
FROM
 ASOsamp.Sample

In the sample query, the WITH clause defines a calculated member, Product Count, in the Measures dimension, as follows:

WITH MEMBER
 [Measures].[Prod Count]

The numeric value expression follows the WITH clause and is enclosed in single quotation marks. In the sample query, the numeric value expression is specified as follows:

'Count ( 
    Crossjoin (
     {[Units]},
     {[Products].children}
    )
  )'

The SOLVE_ORDER property specifies the order in which members and formulas are evaluated. See Calculation Order and Solve Order in ASO Cubes.

Note:

For an explanation of the syntax rules used to build the numeric value expression in the example, see the documentation for the Count, CrossJoin, and Children functions.

A numeric value expression also can be used as an MDX formula to calculate the value of an existing outline member.

Therefore, rather than creating the example query, you can create an outline member on the Measures dimension called Prod Count that is calculated in the outline in the same way that the hypothetical Prod Count was calculated in the sample query.

To create a calculated member with a formula:

Create a member.
Attach an MDX formula to the member.

Assuming that you created the example Prod Count member, you would use the following formula, which is the equivalent of the numeric value expression used to create the calculated member in the example query:
```
Count(Crossjoin ( {[Units]}, {[Products].children}))
```
Verify the formula by verifying the outline.

When you retrieve data from the aggregate storage cube, the formula is used to calculate the member value.

You can use substitution variables within formulas. For example, you could define a substitution variable named “EstimatedPercent” and provide different percentages as substitution variable values. See Using Substitution Variables.

Before applying formulas to members in the outline, you can write MDX queries that contain calculated members. When you can write an MDX query that returns the calculated member results that you want, you are ready to apply the logic of the numeric value expression to an outline member and validate and test the expression. See Writing MDX Queries.