Read me first

Note No. 1

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.

And thus ends the default Planning fx strawman.

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

It turns out that the primary way to speed calculation is to reduce the scope of the data. The old page and POV dimension technique works there, as does the user variable trick for rows and columns that I documented some time ago for row/column focused aggregations.

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

Remember the use case requirements of automatic code, integration with Planning, and acceptable speed? They have raised their collective head again.

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

I chose to create Accounts that support the different kinds of rates so I answered Yes.

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.

27 October 2014

An Essbase ASO procedural calculation too screwy to be true

But it is

The numbers

Split code line

Single code line

The analysis

The code