Essbase map values

Essbase map values from one dimension to another is a common need in an Essbase calc script. For example, let’s say you have accounts that you want to map to entities.  First, you load values into those accounts at ‘Main Entity’.  Then, assign each account to its own unique entity.

Dimensions:

Account

  -Sales

  -COS

-SpecialEntitycharges1  {EntAttribute: “a_Entity001”}

-SpecialEntitycharges2  {EntAttribute: “a_Entity002”}

-SpecialEntitycharges3  {EntAttribute: “a_Entity003”}

-Software

Entity

  -MainEntity

-Entity001

-Entity002

-Entity003

EntAttribute

-SpecialEntities

  -a_Entity001

  -a_Entity002

  -a_Entity003

Calc wanted:

FIX(@WITHATTR(“EntAttribute”,”IN”,”SpecialEntities”))

   @MEMBER(@substring(@ATTRIBUTESVAL(“Account”),2))=MainEntity;

ENDFIX

Mapping values in an Essbase calc script

The issue: You cannot variablize the left-hand side of the equation in an Essbase calc

The solution: @XWRITE delivers your database data and will accept it in whatever intersection you define for it.  Including intersections derived from string/member functions.

The calc:

 Using @XWRITE for string/member functions in an Essbase calc script

While this simple trick isn’t a total replacement for variablization of the left side of the equation, it can often effect the desired result.  It opens up a lot of functionality in your Essbase cubes. If you’re in a jam, this calc, in combination with the use of specific naming conventions, might be able to help out.

If you have any questions, please feel free to email me at jkeskitalo@ecapitaladvisors.com

Happy Coding!

-Jon Keskitalo