1. Calculation and Query Reference for Oracle Essbase
  2. MDX
  3. Aggregate Storage and MDX Outline Formulas

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. You write formulas for aggregate storage outlines using MDX.

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.

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 4-23 provides specific information and examples. Then identify functions for which an indirect rewrite is required. Table 4-23 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 4-23 Calculator to MDX Function Mapping

Calculator MDX Remarks/Examples
@ABS Abs

Calculator

@ABS(Actual-Budget)

MDX

Abs([Actual]-[Budget])
@ALLANCESTORS Ancestors Shared members are not relevant to aggregate storage outlines.
@ALIAS Not required. In MDX, the argument to @ALIAS can be passed as-is to the outer function.
@ANCEST Ancestor 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)
   )
)
@ANCESTORS Ancestors Calculator
@ANCESTORS("New York")
MDX
 Ancestors([New York].parent, [Market].levels(2))
@ANCESTVAL Ancestor 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
@ATTRIBUTE Attribute

Calculator

@ATTRIBUTE(Can)

MDX

Attribute([Can])
@ATTRIBUTEBVAL [BaseDim] .CurrentMember.AttributeDim See About MDX Properties.Calculator
@ATTRIBUTEBVAL(Caffeinated)
MDX
Product.CurrentMember.Caffeinated
@ATTRIBUTESVAL [BaseDim] .CurrentMember.AttributeDim See About MDX Properties.Calculator
@ATTRIBUTESVAL("Pkg Type")
MDX
Product.CurrentMember.[Pkg Type]
@ATTRIBUTEVAL [BaseDim] .CurrentMember AttributeDim See 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)
   )
@AVGRANGE CrossJoin (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)
    )
 )
@CHILDREN Children

Calculator

@CHILDREN(Market)

MDX

Children(Market)

or

Market.Children
@CONCATENATE Concat

Calculator

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

MDX

Concat("01", "01")
@CORRELATION Not 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)
  )
 )
@CURGEN Generation (CurrentMember(dimension))

Calculator

@CURGEN(Year)

MDX

Year.CurrentMember.Generation
@CURLEV Level (CurrentMember(dimension))

Calculator

@CURLEV(Year)

MDX

Year.CurrentMember.Level
@CURRMBR CurrentMember

Calculator

@CURRMBR(Product)

MDX

[Product].CurrentMember
@CURRMBRRANGE RelMemberRange

Calculator

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

MDX

RelMemberRange
      (Year.CurrentMember, 1, 1, LEVEL)
@DESCENDANTS Descendants (member) See MDX Descendants documentation for examples.
@EXP Exp

Calculator

@EXP("Variance %"/100);

MDX

Exp([Scenario].[Variance %]/100)
@FACTORIAL Factorial

Calculator

@FACTORIAL(5)

MDX

Factorial(5)
@GEN, @LEV Generation, Level .
@GENMBRS, @LEVMBRS layer.Members .
@IALLANCESTORS Ancestors Shared members are not relevant to aggregate storage outlines.
@IANCESTORS Ancestors Shared members are not relevant to aggregate storage outlines.
@ICHILDREN Union(member, member.Children)

Calculator

@ICHILDREN(Market)

MDX

Union({Market}, {Market.children})
@IDESCENDANTS Descendants(member)

Calculator

@IDESCENDANTS(Market)

MDX

Descendants(Market)
@ILSIBLINGS MemberRange (member.FirstSibling,member)

Calculator

@ILSIBLINGS(Florida)

MDX

MemberRange(Florida.FirstSibling, Florida.Lag(1))
@INT Int

Calculator

@INT(104.504)

MDX

Int(104.504)
@ISACCTYPE IsAccType See MDX IsAccType documentation for examples.
@ISANCEST IsAncestor

Calculator

@ISANCEST(California)

MDX

IsAncestor(Market.CurrentMember, California)
@ISCHILD IsChild See MDX IsChild documentation for examples.
@ISDESC See examples.

Calculator

@ISDESC(Market)

MDX

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

or

Count(Intersect({Member.Descendants}, 
  {Member.dimension.CurrentMember}) = 1
@ISGEN IsGeneration

Calculator

@ISGEN(Market, 2)

MDX

IsGeneration(
  Market.CurrentMember, 2)
@ISIANCEST IIF(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>
)
@ISIBLINGS Siblings(member) Returns a set that includes the specified member and its siblings.
@ISICHILD IIF(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>
)
@ISIDESC See examples.

Calculator

@ISIDESC(South)

MDX

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

Calculator

@ISIPARENT(Qtr1)

MDX

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

Calculator

@ISISIBLING(Qtr2)

MDX

IIF(
     IsSibling( 
     [Qtr2], Time.CurrentMember
         ),
 <true-part>, <false-part>
)
@ISLEV IsLevel .
@ISMBR IIF(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>
)
@ISPARENT Use IsChild.

Calculator

@ISPARENT("New York")

MDX

IsChild(Market.CurrentMember,[New York])
@ISSAMEGEN,@ISSAMELEV IIF (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>
)
@ISSIBLING IsSibling See MDX IsSibling documentation for examples.
@ISUDA IsUda See 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, @LOG10 Ln, Log, Log10 .
@LSIBLINGS

@RSIBLINGS

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

MemberRange(member.Lead(1), member.LastSibling)

Calculator

@LSIBLINGS(Qtr4)

MDX

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

Calculator

@RSIBLINGS(Qtr1)

MDX

MemberRange([Qtr1].Lead(1), [Qtr1].LastSibling)
@MATCH . .
@MAX Max Use 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]))
@MAXRANGE Max

Calculator

@MAXRANGE(Sales, @CHILDREN(Qtr1))

MDX

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

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.
@MAXSRANGE Max

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.
@MDANCESTVAL Use 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
@MDPARENTVAL Use 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
@MDSHIFT See 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. .
@MEDIAN Not supported in MDX. .
@MEMBER Not needed in MDX. .
@MERGE Union(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)}
@MIN Min Use 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]))
@MINRANGE Min

Calculator

@MINRANGE(Sales, @CHILDREN(Qtr1))

MDX

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

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
 )
)
@MINSRANGE Min

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
 )
)
@MOD Mod .
@MODE Not supported in MDX. .
@NAME Not 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(
  Year.CurrentMember.Lead(2, LEVEL),
  Cash).Value
@NEXTS Not supported in MDX. .
@PARENT Parent .
@PARENTVAL Parent 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
@POWER Power .
@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
@PRIORS Not supported in MDX. .
@RANGE CrossJoin(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})
@RANK Not supported in MDX. This is a vector function. .
@REMAINDER Remainder .
@REMOVE Except(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]})
@ROUND Round .
@SHIFT See @PRIOR and @NEXT. .
@SIBLINGS Siblings .
@STDEV, @STDEVP, @STDEVRANGE Not supported in MDX. .
@SUBSTRING Not supported in MDX. .
@SUM Sum Convert each element of the explist to a tuple so that collectively the tuples can form a set.
@SUMRANGE Sum(CrossJoin(member, Xrangelist))

Calculator

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

MDX

Sum(CrossJoin({[New York]}, {[Jan]:[Jun]}))
@TODATE Todate .
@TRUNCATE Truncate .
@UDA Uda .
@VAR, @VARPER Arg1 - Arg2 An aggregate storage outline has no expense tags. Therefore, variance functionality defaults to subtraction.
@VARIANCE, @VARIANCEP Not supported in MDX. .
@WITHATTR WithAttr .
@XRANGE Not supported in MDX. .
@XREF Not supported in MDX. .