Dear Oracle and Oracle’s competitors. I’m about to use the straw man technique to illustrate currency conversion in Hyperion Planning. All of my complaints are from my offended sense of elegant design, not actual functionality.
Note No. 2
NB
– When you see fx, substitute the words “currency conversion”. I am
too lazy mentally and physically to type that out 100 times in this
post.
A rant (yeah, I’m good at them) about Planning’s currency conversion
We’re
all familiar with Planning’s widely reviled (although somewhat unfairly
if you will read past the rant) currency conversion functionality in
multi-currency Planning applications. It’s been a part of Planning
since at least version 1.5 (perhaps 2.1 – for sure I implemented it
there but I think it was also available in 1.5) and its functionality
really hasn’t changed a bit. I suppose the thought is that the
functionality works, so why bother improving it? But the opening
sentence isn’t hyperbole – no one has a kind word for it. Why?
I think a lot of the dislike of the native Planning currency conversion is because of 3 reasons:
Design
It
does odd things with data locations. Writing rates to the tops of
dimensions? A sparse rate dimension that’s the first dimension in the
outline? It works, but there aren’t many Essbase developers who would
design currency conversion that way. Weird.
I
have to believe that Hyperion located the rates where they did because
these are dimension points that are guaranteed to exist. However,
common practice in non-multiple currency applications is to set the tops
of dimensions to label-only because planners cannot view that top of
the dimension so there is no reason to store data points that only the
administrator can see. That particular security design decision I
really can’t figure out but I’ll save that rant for another day.
Here’s
a partial snippet of this data in Essbase. Note Version, Currency,
Product, and PostCode at their dimension tops, but Scenario set to the
specific value of Actual.
Frustrating.
Code
An automatically (so this is good) generated calc script
(a good thing for a guy that writes a blog called “Essbase hackers” but
an odd choice for Planning – why no business rule?). Calc script
sourcing means the Planning administrator must copy and paste the code
into a Calculation Manager script. Manual.
Speaking of maintenance, when the currency conversion calc script is generated, it is generated for all
years that contain rates. Only want to fx FY14 but your application
has FY11 to FY14? Edit that calc script or you will convert historical
years. Whoops.
HspCRtB?
You mean I have to run it to get the send of rates to work? And I
need to rerun it for out years? And this isn’t terribly well documented
anywhere? And if you don’t run it, exchange rate refreshes will not work, with nary an error message. Confused.
The
generated code isn’t hard to understand, but it is 100% undocumented.
As someone once told me when I was first starting out in IT (so we are
talking 1990), “Good programmers don’t need documentation. They just
read the code.” At the time, I was too young and callow to know any
better and just took it, but I haven’t heard anyone else say that since.
Perhaps that programmer went on to work for Hyperion development in
the late 1990s? Sarcasm. ;)
There’s
no automatically generated aggregation after the currency conversion.
But that is almost always the next step after fx. Why couldn’t that
get automatically generated? Frustrating, again.
Type of fx
Planning
assumes a currency conversion design where the Planner inputs data into
the Currency member Local and, based on UDAs assigned to Entities via
the Base Currency property, performs fx. So long as fx is focused on an
Account breaking cleanly across country-based Entities, all is well.
But what happens when there is Account activity across more than one
currency for a single Entity? How does Planning know that an expense or
revenue item has US Dollar, Sterling, and Swiss Franc activity in that
context? It doesn’t, because that’s not how Planning fx is designed. Bummer.
I feel much better. There really isn’t anything quite as satisfying as venting one’s spleen.
Just for the record
The fx script that Planning generates has three parts:
1) Copy USD with Local. This creates blocks more than anything else as the Local-in-USD gets overwritten by the fx calculation.
2) There’s a FIX that touches all level zero Accounts, Entities (Product), and custom dimensions (PostCode).
3) A member formula to do the actual rate conversion. In this case, because I set up the exchange rate as Multiply in Planning, the calc script multiples Local by the rate.
That’s it. I just thought it would be nice if it was finally documented.
Is there anything good about Planning fx?
Having just slagged off
Planning fx design, I likely now have an army of current Oracle and
former Hyperion developers and product managers gunning for my hide. Is
the above totally fair?
The
rant (you have to admit, it is a fairly epic one at that) covers what
Essbase hackers find objectionable about Planning’s in-built fx. Does
any of that matter to Planning administrators or planners? Actually, no, not a bit, because all of the whining is on the developer side.
Why?
It’s easy
There are easy places to enter rates via a special web form:
It’s (mostly) automatic
Currency
conversion code gets generated automatically. So the first time round,
there’s not a scintilla of code to write. If currencies are added, a
rerun of the fx calculation script generation picks up those new
currencies. Easy peasy, lemon squeezy.
Even
with a bit of deleting of unneeded years and copying and pasting into a
Calc Man businsess rule, it really isn’t that hard to to manage.
It’s invisible to the user
And
planners don’t know or care how the fx is calculated. Why would they?
They enter local currency data in, the system generates USD out via
attached calc scripts (unlikely) or Calc Man business rules (quite a bit
more likely). Who cares how the sausage is made?
Performance is acceptable
I
have heard from lots of other consultants, “We roll our own fx and it’s
way better than Planning’s.” Really? I’ll bet they didn’t benchmark
it because the out of the box performance is actually pretty good. I
know this disparaging view of the default fx calc because I assumed the
same, inflicted implemented Cameron’s-obviously-better-fx at multiple clients, and was generally quite pleased with myself.
For
the record, I used a technique I learnt while I was at interRel – it
looked an awful lot like the old Essbase currency partition, was easy to
maintain in a separate Essbase database, used the cool ARRAY calc
script function, and in general should have been the berries.
Then, for a blog post that as you might imagine never got written, yr. obt. svt. decided to benchmark my approach and Planning’s in a like-for-like set of Planning databases. And…
My code was slower. Hubris.
If only, and I do mean only, I had tested instead of assumed I could have been out there defending Planning’s built in fx functionality. I assumed that Planning’s code sucked eggs because I didn’t like the design. Except that design is better. As my buddy Natalie Delemar said to me at OpenWorld when I got something or other wrong, “You really aren’t infallible, are you?” Nope, I am most definitely not. Alas and alack.
Why Planning’s fx is good and why yr. obt. svt. can be an idiot
My
guess is that consultants who tout their fx approach have done just
that: compared to the calc-everything-all-the-time approach of the
auto-generated calc script code to their focused custom code. That
ain’t faster code, that’s smaller and thus faster. A rate calc is a
rate calc and at the end of the day, that is exactly what fx is all
about.
Everything else is soi-disant Essbase geeks (like yr. obt. svt.) having their sense of design offended. Think of the default Planning fx as an engineering problem. If one defines engineering as the art of the possible given limited resources,
then it follows that the Planning fx use case had a bunch of
requirements (automatic, integrated with Planning, fast) that the
Hyperion (it is that old) development team satisfied. Ta da, that’s how
a commercial product is written, Essbase hackers design sensibilities
be damned.
Software engineering
As
an aside, I come from a family of engineers and given my computer
orientation I am the failure at family dinners because I couldn’t hack
Differential Equations.
Oh the shame.
But I did pick up engineering’s
weltanschauung that I try to apply to my design and code although I obviously fail that approach sometimes, cf. Cameron’s-obviously-better-fx.
Engineers are designers – we as Oracle EPM implementers should have the same philosophy when it comes to solving a problem –
figure out the problem and then do the most with the least.
And that approach drives my questions over on Network54 where I ask
why someone has gone down some unbelievably complex, unsustainable, and generally awful approach.
I note that very often these
why questions of mine go completely unanswered.
Do I offend?
Am I so dense that a
Rube Goldberg/
Heath Robinson approach is the best way and I just can’t see it?
Could it be hubris on the part of the poster?
Do I just like whacking hornet’s nests with sticks?
You decide.
So where does fx go from here?
We
have two views: the strawman that Planning’s default fx is absolute
pants, and the counter argument that Planning’s default fx is actually
perfectly adequate. As much as it pains me, I have to admit that the
base functionality, with a bit of tweaking, is probably more than good
enough. Remember that comment about engineering and the art of the
possible.
Given that, is there any point in even talking about fx? Absolutely, for the use case in rant point number three –
fx that requires contributory currencies. Planning fx can’t do that.
Consultants can, and do, write these kinds of fx conversions, but maybe
there’s a better way to handle it.
Enter Calculation Manager
The Calc Man development team of Sree Menon and Kim Reeve
looked at this issue, and as they so often do, came up with a really
clever way of meeting those fx requirements in Calc Man via a System
Template.
Not applicable to multi-currency Planning apps
Although this is a bit unintuitive, you cannot use the Calc Man fx system template in multi-currency applications. This is easy to suss out by trying to find it in one of those multi-currency applications. It isn’t there.
But when you look at a single currency (I told you this wasn’t intuitive) that fx template is there.
Required dimensionality
Currency
A typical multi-currency Planning application has a Currency dimension that looks like this with one reporting currency:
The
Calc Man fx template requires a very different looking Currency
dimension based on the requirement of the contributory currencies.
Trust me, this structure will make sense in due time.
NB – I believe that the
Reporting hierarchy I show here is probably not necessary. I will update this post as I hear back from
Oracle.
Also,
remember planners do not enter data into Local, but instead have to
select the correct currency for a given Account/Entity/custom member
combination. And yes, that makes forms more complex, but that is the
price of this kind of fx.
The
Base and Reporting members will become important during the fx template
wizard. Take it as read this is required and the actual reasoning
behind this hierarchy will be covered later in this post.
Account
More custom members must be created for the rate types.
As
you know, periodic line items like income statement accounts use
average rates and balance sheet accounts use end of month rates. Both
need an Account to live in.
Using the fx template
The
fx template is a graphical object, so it’s a wizard like the other Calc
Man graphical objects. Let’s take a walk through the template and see
how many mistakes I can make.
Before the beginning
Although
the wizard will start on object drag, cancelling out of the wizard will
show the below instructions. The design I came up with above for the
Currency and Account dimensions reflects the instructions below.
Again,
the bit about, “A parent that contains the reporting currencies (USD
Reporting, EUR Reporting, etc)” is in error, I think. The template
still works, but it only does one reporting currency. This is a
difference from the way Planning works with its optional multiple
reporting currencies.
With that, let’s go back to actually creating this fx process.
Drag it into the rule
The drag and drop initiates the Wizard.
Set the Currency dimension
It begins off with questions about how currency should be defined. In
the case of a non-currency application, Currency (I could have named it taters and neeps – this is totally up to the developer) is a custom dimension; all custom dimensions show up in the dropdown control.
Set the reporting curency
Once you’ve selected a Currency dimension, you must then select the reporting currency.
Pick a base currency parent
Can you guess why I chose “Base” instead of USD, GBP, or CHF? Read on, Gentle Reader.
Pick an account type to drive currency type
Exchange rate option screen filled out
NB
– I used the wizard’s member selector to define member names. You will
see later that I got bored with this and decided to type in values on
my own. Beware.
One thing to note – the parent member that contains the currency members is used to drive the currencies in use -- the template is automatic in that it generates code for all children of the parent member. Cool.
POV
Just
as with the in-built Planning fx, you must select a Point of View for
the fx. This is going to be pretty straight forward as I will simply
type in the functions for the level 0 members of YearTotal, Total, and
Entity. Can you spot one of the errors?
Set the location of the Average rate
Oh,
the errors in this one. Again, this is a quiz for those who, unlike
me, closely look at what they type (hint). But there is a different
error here as well – again, can you spot it?
Setting the location of the ending rate
Oh the shame. The same two errors. Hint. At least I am consistent.
And with that, we are done
When
the ending rate is set, the fx template wizard is complete. It really
was kind of easy, wasn’t it? We also get a nice summary of the
selections. It’s across two screens as SnagIt doesn’t play nice in
scrolling windows on a VM.
Or are we?
Let’s
have a look at the code by clicking on the Script tab in the rule, and
then copying and pasting the code to EAS’ script editor. Sorry, Oracle,
but to understand what the template does, I have to read the code.
For those of you that do not know the trick about seeing the code behind the graphical object, see below:
Getting back to the code, some bits of this make perfect sense:
There’s a FIX that matches the POV setting.
There are two FIXes that select Average versus Ending Accounts based on Planning-derived UDAs.
USD Reporting gets cleared in both fx types.
There’s a rate calculation…wait, hmm, something (several somethings, actually) isn’t right.
Let’s take a closer look
Issue no. 1
@RELATIVE(“YearTotal”,
0)->fx_HSP_Average? A cross dim and a function that returns a set
of members? Is that possible? Er, no.
How
did I manage to do this? It was stupidity on my part after I set the
POV to @RELATIVE(“YearTotal”, 0). That’s fine for the POV, but not so
fine for the FX_Average rate setting. Whoops.
Here’s the culprit:
Remember that comment about my consistency in making mistakes? I did it again with the ending rates. Double whoops.
So get rid of the erroneous @RELATIVE(“YearTotal”, 0):
Issue no. 2
Did I delimit member names with double quotes? Especially the ones with spaces in the names? Sometimes.
Bugger.
Now it’s fixed.
Consistency
I did it with Total Geography, then I did it with No Segment and then with No Entity. Clever, aren’t I? No.
What happens when I try to validate this in Calc Man?
Well, there’s definitely an error…
Sree
tells me Oracle know this and are working on getting better errors.
Probably someone ought to use this template and tell Oracle that they
are. Squeaky wheels and all that.
So fix it per the above errors
Once I recover from my stupidity, all is well.
How does it work?
Quite nicely, actually.
Here are the rates:
Remember, as this is not a multi-currency application, the rates must be entered through a form.
And here are the results:
If you want to see this in action, with proof from Essbase and Excel calculations, see this video:
The end of this blog post
See, Hyperion Planning fx is actually pretty awesome, whether it is in native Planning or via Calc Man’s fx template.
Native Planning provides single Entity fx. And it's automatic. And it's fast.
Calc
Man provides contributory Entity fx. And it's wizard driven, and once
wizard driven, automatic for all currencies under the Base Currency
member. And it's fast, too.
What's not to like?
I am a huge fan of Calc Man’s fx template – it’s fast, it’s easy, even I can do it. And, if I a
m so inclined, I can steal (ahem, “borrow the idea from”) it from the code and do what I want with it.
So cool. Thanks Oracle for putting this out there. And thanks Sree and Kim for answering my questions.
Be seeing you.
