Simplified Currency Conversion – Doing currency conversion with a single line script

Introduction

This blog is called Cameron’s Blog for Essbase Hackers but that’s a misnomer.  This blog is for Essbase (and Planning and ODI and SQL and Dodeca and many other things) hackers, but it isn’t necessarily mine.  By that I mean I am always happy to let others use this blog to share information via a guest post or two.  And someone else doing the work also means I don’t have to write anything for a given week.  :)

All kidding aside, this week’s post is by someone I’ve never met, never even talked to, but I know from both the web and email – Joe Watkins of The Hackett Group.

Last week I saw this post about an improved currency conversion technique over on Network54.  I’ve stolen utilized Joe’s technique for fast ASO procedural calcs, gotten a lot of publicity for it (and even had the technique attributed to me despite my protestations), and then felt quite guilty about the accolades.  Folks, it ain’t my work although I am happy to use it and glad that Joe shared the approach.  And with that in mind I invited Joe to write a quick post on his approach for a much better fx technique.  It is, in a word, brilliant.

Why do I say this technique is brilliant?  

• It reduces code
• It reduces maintenance
• It is efficient
• It works in both BSO and ASO (yes, really)
• It is an awesome hack

What’s not to like?  

With that, let me turn this blog over to Joe. Joe, take it away.

Currency conversion with a single line script

Currency conversion is a well-known process that is required in most implementations these days.  There are many ways to do fx:  custom code, Planning’s automatically generated calc scripts, and Calculation Manager’s fx system template.  Those are all valid approaches but they require some kind of maintenance as currency requirements change over time.  There is a much easier way to implement currency conversion with huge benefits around implementation and maintenance.  

It is a single line of code.

Olde Skool fx

Here is the typical implementation in the ASO world although this also applies to BSO.  

UDAs

Each entity is tagged with a UDA to determine the ‘Entity Currency’ for each entity:

• Entity_#1 has a UDA of ‘USD’
• Entity_#2 has a UDA of ‘BRL’
• …ETC for each level zero entity member

Formula

A member formula is created in the currency dimension with the following code:

Member name: USD@BR – USD at Budget Rate (can be called anything)

CASE

    WHEN ISUDA ([ENTITY].CURRENTMEMBER,"USD") THEN ([LOCAL_])

    WHEN ISUDA ([ENTITY].CURRENTMEMBER),"BRL") THEN ([LOCAL_]) / ([BRL], [Other members where rates are stored])

    WHEN ISUDA ([ENTITY].CURRENTMEMBER),"CZK") THEN ([LOCAL_]) / ([CZK], [Other members where rates are stored])

    … Additional lines for additional currencies

    ELSE MISSING

END

This member formula can be quite long and needs to be updated as currencies are added.  

The implementation for BSO is very similar except you use a calculation script instead of a member formula.

A better approach

There is a much simpler solution and one that does not require any maintenance as new currencies are added.

All we have to do is test each entity for the value of the UDA.  If we could do that we could make the currency portion of the formula dynamic and get rid of the CASE statement.  Unfortunately there is no way to test for the ‘value’ of a UDA.  Meaning when Entity_#1 has a UDA of ‘USD’ there is no to test for the value ‘USD’.   Luckily there is something that we can test for and that is an attribute.  

We can test for an Attribute in BSO using @ATTRIBUTEVAL and we can test for the attribute value in MDX by using the following line of MDX – [ENTITY].CURRENTMEMBER.ATTRIBUTEDIM.  Can you see where I’m going with this?  Let me continue on.  

The steps

How do I simplify currency conversion and make it a one liner?  

Here’s the step by step:

• Remove the Currency UDA from the Entity dimension (if it currently exists)
• Give the attribute dimension a name that makes sense, something like ‘CURR’ will work.
• Create an attribute dimension that mimics the currency dimension.  
• Make sure that each level zero member of the CURR attribute has a name that is very similar to the currency dimension so that we can easily substring the value of the attribute.  For example – USD_Entity.  Naming it USD will cause a conflict with the Currency dimension unless your currency dimension has a prefix or a suffix such as USD_Currency.  
• Create a specific attribute for level zero entity.
• Assign the attributes to the relevant Entities
• Apply the one line formula (ASO member formula or BSO calc script) to the database.

CURR Attribute dimension

Here is an example of what the attribute dimension looks like:

Entity dimension with CURR attributes

Here is what the Entity dimension looks like with the CURR attributes highlighted on the level 0 members:

Getting the currency member from the attribute

Using this attribute dimension and naming convention I can substring the first 3 characters from the base member’s CURR attribute to get the currency member to use in the formula.  

MDX

STRTOMBR (SUBSTRING ( [ENTITY].CURRENTMEMBER.CURR,1,3) )

BSO

@member(@SUBSTRING(@Attributesval("CURR"),0,3))

Currency dimension

Given member E_1198_230_1000’s CURR attribute of CAD_Entity, the above MDX substring of the attribute value gives us the CAD currency member from the currency dimension:

All we need to do now is apply this dynamic member generation to the currency formula.

Apply the new code to the USD@BR member formula

As the calc script (ASO or BSO) cycles through the database (POV or FIX), the USD converted value is calculated by taking the Local inputted value and applying the fx rate.  That fx rate member is the result of string manipulation based on the current member.  Consequently there is no need to write anything more than this single line of code to do the fx.

ASO version of the MDX member formula:

( [LOCAL_] ) / ( STRTOMBR (SUBSTRING ( [ENTITY].CURRENTMEMBER.CURR,1,3) ), [Other members where currency rate is stored])

BSO version of the calculation script:

"USD" =   ( "Local_" * @member(@SUBSTRING(@Attributesval("CURR"),0,3))->" Other members where currency rate is stored ");

Calculate it

The above formula created in only works at the level zero entities.  It will not work at higher levels in the entity dimension.  To properly get the data into the ‘USD’ member you need a procedural calculation.

In BSO, use a calc script and FIX at level 0 as required.

Something like the following will work for ASO:

/* STANDARD P&L CURRENCY CONVERSION FOR ONE SCENARIO */

execute allocation process on database ECT_RPT.ECT_RPT with

    Pov

        "CROSSJOIN ({([FY14])},

        CROSSJOIN ( Descendants ( YEARTOTAL, PERIOD.Levels(0)),

        CROSSJOIN ( Descendants ( [Income_Statement] , ACCOUNT.Levels(0)),

            (Descendants ([E_ALL_ENTITY], ENTITY.Levels (0))))))"   

    Amount "([USD@BR])"

    Amountcontext "([ACTUALS], [FINAL])"

    Target "([USD])"

    Range "{([ACTUALS], [FINAL])}"

SPREAD;

I’m not going to go into detail of doing procedural calculations as that has been covered many times before.  

And that’s it

This one liner for both ASO and BSO is quite simple making implementations and maintenance simple in both the ASO and BSO world.

And that’s really it and it’s awesome

Joe’s a bit modest.  Yr. obt. svt. is not quite as retiring as Joe, so for me “quite simple” doesn’t really cut it – more like This Is Freaking Awesome.  

However we (and by we I most certainly include myself) do fx today in Essbase, it is almost certainly maintenance intensive.  Are you really in love with complex CASE/IF tests?  If so, why?

Surely this is a better approach – one single line, one additional attribute dimension, and ta da, one line of code and no code maintenance in future.  It’s sort of like a fx holy grail.

The genius of Joe Watkins, indeed.

Thanks, Joe, for agreeing to share this. 

Be seeing you.

Addendum

GlennS aka Glenn Schwartzberg aka the older brother I never had and who strangely refuses to acknowledge our non-relationship pointed out that he wrote about a very similar solution back in 2011: 

http://glennschwartzbergs-essbase-blog.blogspot.com/2011/11/i-was-at-client-and-needed-to-do-some.html

The funny (Funny?  Sad is more like it) thing is I read that post way back when but have zero memory of doing so. 

The fact that Glenn figured this out beforehand takes nothing away from Joe’s work – great minds think alike after all and both of them deserve the credit for this approach.

For sure I am going to use this in my next fx application.