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
MDX
|
@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 MDX
|
@ANCESTORS | Ancestors | Calculator MDX
|
@ANCESTVAL | Ancestor with CurrentMember as input. Use a tuple to combine the result with the optional third argument to the @ANCESTVAL function. | Calculator MDX
|
@ATTRIBUTE | Attribute |
Calculator
MDX
|
@ATTRIBUTEBVAL | [BaseDim] .CurrentMember.AttributeDim | See About MDX Properties.Calculator MDX
|
@ATTRIBUTESVAL | [BaseDim] .CurrentMember.AttributeDim | See About MDX Properties.Calculator MDX
|
@ATTRIBUTEVAL | [BaseDim] .CurrentMember AttributeDim | See About MDX Properties.Calculator MDX
|
@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
MDX
If SKIPMISSING is replaced by SKIPNONE, the translation changes to:
For SKIPZERO, the translation is:
For SKIPBOTH, the translation is:
|
@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
MDX
If SKIPMISSING is replaced by SKIPNONE, the translation becomes:
If SKIPZERO is used, then the translation is:
|
@CHILDREN | Children |
Calculator
MDX
or
|
@CONCATENATE | Concat |
Calculator
MDX
|
@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
MDX
Note that Count always counts including the empty cells, whereas NonEmptyCount does not. For SKIPNONE, the translation is:
For SKIPZERO, the translation is:
|
@CURGEN | Generation (CurrentMember(dimension)) |
Calculator
MDX
|
@CURLEV | Level (CurrentMember(dimension)) |
Calculator
MDX
|
@CURRMBR | CurrentMember |
Calculator
MDX
|
@CURRMBRRANGE | RelMemberRange |
Calculator
MDX
|
@DESCENDANTS | Descendants (member) | See MDX Descendants documentation for examples. |
@EXP | Exp |
Calculator
MDX
|
@FACTORIAL | Factorial |
Calculator
MDX
|
@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
MDX
|
@IDESCENDANTS | Descendants(member) |
Calculator
MDX
|
@ILSIBLINGS | MemberRange (member.FirstSibling,member) |
Calculator
MDX
|
@INT | Int |
Calculator
MDX
|
@ISACCTYPE | IsAccType | See MDX IsAccType documentation for examples. |
@ISANCEST | IsAncestor |
Calculator
MDX
|
@ISCHILD | IsChild | See MDX IsChild documentation for examples. |
@ISDESC | See examples. |
Calculator
MDX
or
|
@ISGEN | IsGeneration |
Calculator
MDX
|
@ISIANCEST | IIF(Is(member, ancestormember) OR IsAncestor(member, ancestormember), <true-part>, <false-part>) |
Calculator
MDX
|
@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
MDX
|
@ISIDESC | See examples. |
Calculator
MDX
|
@ISIPARENT | IIF(Is(member, parentmember) |
Calculator
MDX
|
@ISISIBLING | IsSibling(member, siblingmember) |
Calculator
MDX
|
@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
MDX
|
@ISPARENT | Use IsChild. |
Calculator
MDX
|
@ISSAMEGEN,@ISSAMELEV | IIF (member.Generation = CurrentMember(dimension).Generation, <true-part>, <false-part>) |
Calculator
MDX
|
@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 | MemberRange(member.FirstSibling, member.Lag(1))
MemberRange(member.Lead(1), member.LastSibling) |
Calculator
MDX
Calculator
MDX
|
@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
MDX
|
@MAXRANGE | Max |
Calculator
MDX OR
|
@MAXS | Max |
Calculator
MDX
For SKIPZERO, the translation is:
For SKIPBOTH, the translation is the same as for SKIPZERO, because Max skips missing values by default. |
@MAXSRANGE | Max |
Calculator
MDX
For SKIPZERO, the translation is:
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
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.
|
@MDPARENTVAL | Use Parent, Value, and CurrentMember as shown in the example. |
Calculator
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.
|
@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
MDX
|
@MIN | Min | Use Min if argument list is a set. Otherwise, rewrite logic using Case constructs by explicit enumeration of the argument list.
Calculator
MDX
|
@MINRANGE | Min |
Calculator
MDX OR
|
@MINS | Min |
Calculator
MDX
For SKIPZERO, the translation is:
For SKIPBOTH, the translation is:
|
@MINSRANGE | Min |
Calculator
MDX
For SKIPZERO, the translation is:
For SKIPBOTH, the translation is:
|
@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
MDX
Alternative: Calculator
MDX
|
@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
MDX
|
@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
MDX
Alternative: Calculator
MDX
|
@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
MDX
|
@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
MDX
|
@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
MDX
|
@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. | . |