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

Jon Harvey

Jon has been a part of the Oracle team at eCapital Advisors since 2009 and is passionate about educating and teaching clients and team members how to apply the technology to drive business value within organizations. An Oracle Certified Expert in both Hyperion Planning and Essbase, Jon oversees the technical aspects of EPM project delivery and managed services. In addition, he’s responsible for ensuring the continued growth of the practice’s technical capabilities and serves as the primary interface between eCapital and the Oracle user community.

Comments are closed.