# Aggregate Storage and MDX Outline Formulas

To write formulas for block storage outlines, Essbase provides a set of calculation functions and operators known as the Calculator, or Calc, language. The Calculator language cannot be used to write member formulas for aggregate storage databases. Formulas in aggregate storage outlines use the MDX language.

The following sections provide information for rewriting Calculator formulas in MDX for outlines that have been migrated from block storage to aggregate storage. Before attempting to rewrite formulas you should be familiar with the basic workings of aggregate storage outlines in Essbase. See the Oracle Essbase Database Administrator's Guide, which discusses all aspects of aggregate storage.

Translating Calculator Functions to MDX Functions

When translating Calculator formulas to MDX, keep in mind the following differences between block storage outlines and aggregate storage outlines:

• The storage characteristics of a member and hence all its associated cells are defined in a block storage outline through Dynamic Calc (and Dynamic Calc and Store) attributes, and stored attributes. Such attributes do not exist in an aggregate storage outline. Upper level members along an explicitly tagged accounts dimension and members with formulas attached to them are always calculated dynamically in such a database.

• In block storage outlines, calculation order is dependent on the order in which members appear in the outline whereas formulas are executed in order of their dependencies in aggregate storage outlines. In addition, calculation order in the event of ambiguity in the evaluation of a cell, and two-pass calculation tags are not required in an aggregate storage outline.

• The layout of block storage outlines and the separation of dimensions into dense and sparse has an effect on the semantics of certain calculations, giving rise to concepts such as top-down calculation mode, cell and block calculation mode, and create-blocks on equations. The simplicity of the aggregate storage outlines, which do not separate dimensions into dense and sparse, do not require such concepts.

General Guidelines for Translating Calculator Formulas to MDX

This section provides some general guidelines for translating Calculator formulas to MDX.

Be certain that the application has been redesigned to use an aggregate storage outline. In this regard, make certain that formulas do not reference any block-storage specific outline constructs, such as variance functions that rely on expense tagging, or functions that operate on shared members (for example, @RDESCENDANTS). Such constructs are not valid in aggregate storage outlines.

Rewrite each function in the formulas attached to an explicitly tagged accounts dimension for which a direct counterpart in MDX exists. Table 1 provides specific information and examples. Then identify functions for which an indirect rewrite is required. Table 1 also provides information and examples for these functions.

Understand the calculation order semantics for the formulas in the block storage outline. Organize the dependent formulas in the aggregate storage outline carefully to achieve the same results as block storage.

If formulas reference custom-defined functions or macros consider rewriting them, if possible, using other MDX functions.

The following table lists all functions in the Calculator language and their analogs in MDX (and vice versa). Where a direct analog does not exist, transformation rules and examples are provided.

Table 1. Calculator to MDX Function Mapping

CalculatorMDXRemarks/Examples
@ABSAbs

Calculator

`@ABS(Actual-Budget)`

MDX

`Abs([Actual]-[Budget])`
@ALLANCESTORSAncestorsShared members are not relevant to aggregate storage outlines.
@ALIASNot required.In MDX, the argument to @ALIAS can be passed as-is to the outer function.
@ANCESTAncestor with CurrentMember as input. Use a tuple to combine the result with the optional third argument to the @ANCEST function.Calculator
`@ANCEST(Product,2,Sales) `
MDX
```(
Sales,
Ancestor(
Product.CurrentMember,
Product.Generations(2)
)
)```
@ANCESTORSAncestorsCalculator
`@ANCESTORS("New York")`
MDX
` Ancestors([New York].parent, [Market].levels(2))`
@ANCESTVALAncestor with CurrentMember as input. Use a tuple to combine the result with the optional third argument to the @ANCESTVAL function.Calculator
`@ANCESTVAL(Product,2,Sales) `
MDX
```(Sales,
Ancestor(
Product.CurrentMember,
Product.Generations(2)
)
).Value```
@ATTRIBUTEAttribute

Calculator

`@ATTRIBUTE(Can)`

MDX

`Attribute([Can])`
`@ATTRIBUTEBVAL(Caffeinated)`
MDX
`Product.CurrentMember.Caffeinated`
`@ATTRIBUTESVAL("Pkg Type")`
MDX
`Product.CurrentMember.[Pkg Type]`
@ATTRIBUTEVAL[BaseDim] .CurrentMember AttributeDimSee About MDX Properties.Calculator
`@ATTRIBUTEVAL(Ounces)`
MDX
`Product.CurrentMember.Ounces`
@AVG

If the dimensionality of all elements in the input set to @AVG is the same, use Avg. Translate SKIPNONE to INCLUDEEMPTY.

If the dimensionality of all elements in the input set to @AVG is not the same, then perform average by explicitly adding the tuples and dividing by the set cardinality (the number of tuples in the set).

Note that the MDX Avg function skips missing cell values by default.

Calculator

`@AVG(SKIPMISSING, @CHILDREN(East))`

MDX

`Avg([East].Children)`

If SKIPMISSING is replaced by SKIPNONE, the translation changes to:

`Avg([East].Children, Sales, INCLUDEEMPTY)`

For SKIPZERO, the translation is:

```Avg([East].Children,
IIF(Market.CurrentMember.Value=0, Missing,
IIF(Market.CurrentMember= Missing,0,
Market.CurrentMember.Value
)
)
)```

For SKIPBOTH, the translation is:

```Avg([East].Children,
IIF(Market.CurrentMember=0, Missing,
Market.CurrentMember.Value)
)```
@AVGRANGECrossJoin (first argument, set created out of second argument). The rest is similar to @AVG when the dimensionality of all elements of the input set is identical.

Calculator

`@AVGRANGE(SKIPMISSING, Sales, @CHILDREN(West))`

MDX

`Avg(CrossJoin({Sales},{[West].Children)})`

If SKIPMISSING is replaced by SKIPNONE, the translation becomes:

`Avg({[West].Children)},Sales,INCLUDEEMPTY)`

If SKIPZERO is used, then the translation is:

```Avg([West].Children),
IIF(Sales = 0,  Missing,
IIF(Sales =  Missing, 0, Sales)
)
)```
@CHILDRENChildren

Calculator

`@CHILDREN(Market)`

MDX

`Children(Market)`

or

`Market.Children`
@CONCATENATEConcat

Calculator

`@MEMBER(@CONCATENATE("Qtr1", "1"));`

MDX

`Concat("01", "01")`
@CORRELATIONNot supported in MDX..
@COUNT

Use Count if SKIPNONE.

Use NonEmptyCount if SKIPMISSING.

For SKIPZERO, see the example in the next column.

For SKIPBOTH, use Count (Filter(set, value <> 0 && value <> MISSING))

Calculator

`@COUNT(SKIPMISSING,@RANGE(Sales, Children(Product)))`

MDX

`NonEmptyCount(CrossJoin({Sales},{Product.Children}))`

Note that Count always counts including the empty cells, whereas NonEmptyCount does not.

For SKIPNONE, the translation is:

`Count(Product.Children)`

For SKIPZERO, the translation is:

```NonEmptyCount
(Product.Children,
IIF(Sales=0,  Missing,
IIF(Sales =  Missing, 0, sales)
)
)```
@CURGENGeneration (CurrentMember(dimension))

Calculator

`@CURGEN(Year)`

MDX

`Year.CurrentMember.Generation`
@CURLEVLevel (CurrentMember(dimension))

Calculator

`@CURLEV(Year)`

MDX

`Year.CurrentMember.Level`
@CURRMBRCurrentMember

Calculator

`@CURRMBR(Product)`

MDX

`[Product].CurrentMember`
@CURRMBRRANGERelMemberRange

Calculator

`@CURRMBRRANGE(Year, LEV, 0, -1, 1)`

MDX

```RelMemberRange
(Year.CurrentMember, 1, 1, LEVEL)```
@DESCENDANTSDescendants (member)See MDX Descendants documentation for examples.
@EXPExp

Calculator

`@EXP("Variance %"/100);`

MDX

`Exp([Scenario].[Variance %]/100)`
@FACTORIALFactorial

Calculator

`@FACTORIAL(5)`

MDX

`Factorial(5)`
@GEN, @LEVGeneration, Level.
@GENMBRS, @LEVMBRSlayer.Members.
@IALLANCESTORSAncestorsShared members are not relevant to aggregate storage outlines.
@IANCESTORSAncestorsShared members are not relevant to aggregate storage outlines.
@ICHILDRENUnion(member, member.Children)

Calculator

`@ICHILDREN(Market)`

MDX

`Union({Market}, {Market.children})`
@IDESCENDANTSDescendants(member)

Calculator

`@IDESCENDANTS(Market)`

MDX

`Descendants(Market)`
@ILSIBLINGSMemberRange (member.FirstSibling,member)

Calculator

`@ILSIBLINGS(Florida)`

MDX

`MemberRange(Florida.FirstSibling, Florida.Lag(1))`
@INTInt

Calculator

`@INT(104.504)`

MDX

`Int(104.504)`
@ISACCTYPEIsAccTypeSee MDX IsAccType documentation for examples.
@ISANCESTIsAncestor

Calculator

`@ISANCEST(California)`

MDX

`IsAncestor(Market.CurrentMember, California)`
@ISCHILDIsChildSee MDX IsChild documentation for examples.
@ISDESCSee examples.

Calculator

`@ISDESC(Market)`

MDX

`IsAncestor([Market], [Market].Dimension.CurrentMember)`

or

```Count(Intersect({Member.Descendants},
{Member.dimension.CurrentMember}) = 1```
@ISGENIsGeneration

Calculator

`@ISGEN(Market, 2)`

MDX

```IsGeneration(
Market.CurrentMember, 2)```
@ISIANCESTIIF(Is(member, ancestormember) OR IsAncestor(member, ancestormember), <true-part>, <false-part>)

Calculator

`@ISIANCEST(California)`

MDX

```IIF(
IS(Market.CurrentMember, California)
OR
IsAncestor(Market.CurrentMember, California),
<true-part>, <false-part>
)```
@ISIBLINGSSiblings(member)Returns a set that includes the specified member and its siblings.
@ISICHILDIIF(Is(member, childmember) OR IsChild(member, childmember), <true-part>, <false-part>)

Calculator

`@ISICHILD(South)`

MDX

```IIF(
Is(Market.CurrentMember,South)
OR
IsChild(Market.CurrentMember,South),
<true-part>, <false-part>
)```
@ISIDESCSee examples.

Calculator

`@ISIDESC(South)`

MDX

```(Count(Intersect({[South].Descendants}, {South}) = 1
OR
Is(CurrentMember, [South]))```
@ISIPARENTIIF(Is(member, parentmember)

Calculator

`@ISIPARENT(Qtr1) `

MDX

```IIF(
Is(Time.CurrentMember, [Qtr1])
OR
IsChild([Qtr1], Time.CurrentMember),
<true-part>, <false-part>)```
@ISISIBLINGIsSibling(member, siblingmember)

Calculator

`@ISISIBLING(Qtr2)`

MDX

```IIF(
IsSibling(
[Qtr2], Time.CurrentMember
),
<true-part>, <false-part>
)```
@ISLEVIsLevel.
@ISMBRIIF(Count(Intersect (member-set, member)) = 1, true-part, false-part)Calculator allows a collection of members or cross members that do not subscribe to the rules of an MDX set to appear as the second argument. This functionality cannot be easily replicated without enumerating each element of the second set and testing for intersection.

However, if the second argument subscribes to MDX set rules then the translation is easier, as shown. For example:

Calculator

`@ISMBR("New York":"New Hampshire")`

MDX

```IIF(
Count(
Intersect(
{MemberRange([New York],[New Hampshire])},
{Market.CurrentMember}
)
) = 1,
<true-part>, <false-part>
)```
@ISPARENTUse IsChild.

Calculator

`@ISPARENT("New York")`

MDX

`IsChild(Market.CurrentMember,[New York])`
@ISSAMEGEN,@ISSAMELEVIIF (member.Generation = CurrentMember(dimension).Generation, <true-part>, <false-part>)

Calculator

`@ISSAMEGEN(West)`

MDX

```IIF(
Ordinal(
Market.CurrentMember.Generation
)
= Ordinal(West.Generation),
<true-part>, <false-part>
)```
@ISSIBLINGIsSiblingSee MDX IsSibling documentation for examples.
@ISUDAIsUdaSee MDX IsUda documentation for examples.
@LIST.If the member set does not subscribe to MDX set rules, then explicit enumeration is required. For rangelist use CrossJoin(member, set).
@LN, @LOG, @LOG10Ln, Log, Log10.
@LSIBLINGS

@RSIBLINGS

MemberRange(member.FirstSibling, member.Lag(1))

Calculator

`@LSIBLINGS(Qtr4)`

MDX

`MemberRange([Qtr4].FirstSibling, [Qtr4].Lag(1))`

Calculator

`@RSIBLINGS(Qtr1)`

MDX

`MemberRange([Qtr1].Lead(1), [Qtr1].LastSibling)`
@MATCH..
@MAXMaxUse Max if argument list is a set. Otherwise, rewrite logic using Case constructs by explicit enumeration of the argument list.

Calculator

`@MAX(Jan:Mar)`

MDX

`Max(MemberRange([Jan], [Mar]))`
@MAXRANGEMax

Calculator

`@MAXRANGE(Sales, @CHILDREN(Qtr1))`

MDX

```Max(
CrossJoin(
{Sales},
{[Qtr1].Children}
)
)
Max([Qtr1].Children, Sales)```
OR
@MAXSMax

Calculator

`@MAXS(SKIPMISSING,Sales,@CHILDREN(Qtr1))`

MDX

```Max(
Children([Qtr1]),Sales)
)```

For SKIPZERO, the translation is:

`Max (Children ([Qtr1]), IIF (Sales = 0, MISSING, Sales))`

For SKIPBOTH, the translation is the same as for SKIPZERO, because Max skips missing values by default.

@MAXSRANGEMax

Calculator

`@MAXSRANGE(SKIPMISSING, Sales, @CHILDREN(Qtr1))`

MDX

```Max(
Children([Qtr1]),Sales)
)```

For SKIPZERO, the translation is:

`Max (Children ([Qtr1]), IIF (Sales = 0, MISSING, Sales))`

For SKIPBOTH, the translation is the same as for SKIPZERO, because Max skips missing values by default.

@MDANCESTVALUse Ancestor, Value, and Currentmember as shown in the example.

Calculator

`@MDANCESTVAL(2, Market, 2, Product, 2, Sales)`

MDX

Construct a tuple consisting of Sales from the Measures dimension, the ancestor of the current member along the Market dimension, and the ancestor of the current member along the Product dimension. Then get the value of the tuple.

```(Sales, Ancestor(Market.CurrentMember, 2),
Ancestor(Product.CurrentMember, 2)).Value```
@MDPARENTVALUse Parent, Value, and CurrentMember as shown in the example.

Calculator

`@MDPARENTVAL(2, Market, Product, Sales)`

MDX

Construct a tuple consisting of Sales from the Measures dimension, the parent of the current member along the Market dimension, and the parent of the current member along the Product dimension. Then get the value of the tuple.

```(Sales, Market.CurrentMember.Parent,
Product.CurrentMember.Parent).Value```
@MDSHIFTSee MDX equivalent for @NEXT, and repeat it for each dimension that needs to be shifted. CrossJoin the results from each dimension and get the value of the final tuple. See comments for @MDANCESTVAL..
@MEDIANNot supported in MDX..
@MEMBERNot needed in MDX..
@MERGEUnion(set1,set2)If the lists specified as inputs to @MERGE do not subscribe to the rules of an MDX set, then the @MERGE function cannot be translated. The following example assumes that the lists do subscribe to MDX set rules.

Calculator

`@MERGE(@CHILDREN(East),@CHILDREN(West))`

MDX

`{Union([East].Children, [West].Children)}`
@MINMinUse Min if argument list is a set. Otherwise, rewrite logic using Case constructs by explicit enumeration of the argument list.

Calculator

`@MIN(Jan:Mar)`

MDX

`Min(MemberRange([Jan], [Mar]))`
@MINRANGEMin

Calculator

`@MINRANGE(Sales, @CHILDREN(Qtr1))`

MDX

```Min(
CrossJoin(
{Sales},
{[Qtr1].Children}
)
)
Min([Qtr1].Children, Sales)```
OR
@MINSMin

Calculator

`@MINS(SKIPMISSING,Sales,@CHILDREN(Qtr1))`

MDX

```Min(
Filter(
Children([Qtr1]),
Sales <> Missing
)
)```

For SKIPZERO, the translation is:

```Min(
Filter(
Children([Qtr1]),
Sales <> 0
)
)```

For SKIPBOTH, the translation is:

```Min(
Filter(
Children([Qtr1]),
Sales <> 0 AND
Sales <> Missing
)
)```
@MINSRANGEMin

Calculator

`@MINSRANGE(SKIPMISSING, Sales, @CHILDREN(Qtr1))`

MDX

```Min(
Filter(Children([Qtr1]),
Sales <> Missing
)
)```

For SKIPZERO, the translation is:

```Min(
Filter(Children([Qtr1]),
Sales <> 0
)
)```

For SKIPBOTH, the translation is:

```Min (
Filter(Children([Qtr1]),
Sales <> 0 AND
Sales <> Missing
)
)```
@MODMod.
@MODENot supported in MDX..
@NAMENot needed in MDX..
@NEXT

@NEXT(member,[n, range]) returns the nth cell value in the range from the supplied member. The function returns a missing value if the supplied member does not exist in the range. If range is not specified, level-0 members of the Time dimension are used.

MDX does not have an equivalent function for an arbitrary range. However, if the range is restricted to members from a specific level or generation, then using NextMember (if n=1) or Lead/Lag will work as shown in the sample translation. This is probably the common case.

Calculator

`@Next(Cash)`

MDX

```(NextMember(
[Year].CurrentMember, LEVEL),
[Cash]).Value```

Alternative:

Calculator

`@Next(Cash, 2)`

MDX

```CrossJoin(
Cash).Value```
@NEXTSNot supported in MDX..
@PARENTParent.
@PARENTVALParent with CurrentMember as input. Use a tuple to combine the result with the optional second argument to the @PARENTVAL function.

Calculator

`@PARENTVAL(Market, Sales)`

MDX

`([Sales], [Market].CurrentMember.Parent).Value`
@POWERPower.
@PRIOR

@PRIOR(member,[n, range]) returns the nth cell value in the range from the supplied member. The function returns a missing value if the supplied member does not exist in the range. If range is not specified, level-0 members of the Time dimension are used.

MDX does not have an equivalent function for an arbitrary range. However, if the range is restricted to members from a specific level or generation, then using PrevMember (if n=1) or Lead/Lag will work as shown in the sample translation. This is probably the common case.

Calculator

`@Prior(Cash)`

MDX

`PrevMember(Year.CurrentMember, LEVEL), [Cash]).Value`

Alternative:

Calculator

`@Prior(Cash, 2)`

MDX

`(Year.CurrentMember.Lag(2, LEVEL), [Cash]).Value`
@PRIORSNot supported in MDX..
@RANGECrossJoin(member, rangeset)Calculator automatically uses level-0 members of the Time dimension if a range is unspecified. That feature does not exist in MDX, so you must explicitly include the range.

Calculator

`@RANGE(Sales, @CHILDREN(East))`

MDX

`CrossJoin({Sales}, {[East].Children})`
@RANKNot supported in MDX. This is a vector function..
@REMAINDERRemainder.
@REMOVEExcept(set1, set2)Translation will work only if set1 and set2 are true MDX sets.

Calculator

`@REMOVE(@CHILDREN(East),@LIST("New York",Connecticut))`

MDX

`Except ({[East].Children}, {[New York],[Connecticut]})`
@ROUNDRound.
@SHIFTSee @PRIOR and @NEXT..
@SIBLINGSSiblings.
@STDEV, @STDEVP, @STDEVRANGENot supported in MDX..
@SUBSTRINGNot supported in MDX..
@SUMSumConvert each element of the explist to a tuple so that collectively the tuples can form a set.
@SUMRANGESum(CrossJoin(member, Xrangelist))

Calculator

`@SUMRANGE("New York",Jan:Jun)`

MDX

`Sum(CrossJoin({[New York]}, {[Jan]:[Jun]}))`
@TODATETodate.
@TRUNCATETruncate.
@UDAUda.
@VAR, @VARPERArg1 - Arg2An aggregate storage outline has no expense tags. Therefore, variance functionality defaults to subtraction.
@VARIANCE, @VARIANCEPNot supported in MDX..
@WITHATTRWithAttr.
@XRANGENot supported in MDX..
@XREFNot supported in MDX..