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
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 | 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} ) ) Max([Qtr1].Children, Sales)OR |
@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} ) ) Min([Qtr1].Children, Sales)OR |
@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. | . |