9,000 + 5,700 + 12,000 = wow

Short and sweet, for once

This post is going to be short and sweet because I am in the middle of Yet Another Really Big Project That Pays Bupkis.  You would think I would learn, but then you (and I) would be wrong.  Unfortunately.  Stay tuned to this blog for a Big Announcement in the next month or so.

What I should have, but don’t

I do have 80% of a really interesting (ahem) post on tap but you know how that last 20% goes and I don’t have the time.

I have another really interesting blog post on Calculation Manager that I have, at least in my head, about 50% written but that too is going to take time.

And I just don’t have any.  Bummer.

What I do have

I better not let too much time go by before I add new content, or this will be the last time I see numbers like this:

This blog has finally attracted more than 9,000 sessions, more than 5,700 users, and more than 12,000 pageviews in a 30 day period!  Small beer to many, but big news for me.

The blog has been just shy of 9,000 sessions for the last two or three months and it got to be kind of agonizing to see if it would ever break through.  It has.

According to Google Analytics, audience is defined in the following ways:

• Sessions – An instance of a user actively engaged on this blog
• Users – Users that have at least one session.
• Pageviews – The total number of pages viewed

You’ve engaged with this blog over 9,000 times in the last (as of Friday) 30 days.  That means that globally there were 9,000 instances of of people saying, “I wonder what that idiot Cameron has come up with this week.”

And here we are.

Btw, those statistics are not because I sat in front of my computer hitting F5 on http://camerons-blog-for-essbase-hackers.blogspot.com/ again and again and again till I got really good numbers although that thought did cross my mind.

Instead this reach is because of you.  You’re the ones that read this blog, find value (in theory), and come back for more.

It sort of amazes me that anyone, other than my mother, reads this thing.  And I have to prod her sometimes with comments like, “Mum, didn’t you see my mention of:  you/strange love of Faygo red pop/desire to visit the Henry Ford Museum again, yes, again/need to drink Tim Horton’s coffee/various strange references to (un)popular culture?”  Invariably, she has not.  So that’s at least one person who doesn’t pick up what I’m laying down but you, Gentle Reader, are.

Thank you

There really isn’t much to say other than thank you for finding so much value in this blog.  I’m going to do a year-end review of this blog by the numbers but breaking through this particularly statistic (and honestly, I don’t know why 9,000 became a goal, but it did) just tickled me pink.

Thanks again.

Be seeing you.

CalcMgrExecuteMaxLScript and RUNJAVA

Introduction

This is just a super (well sort of) quick post on the @CalcMgr CDF and running MaxL scripts.

You saw that I previously discussed the @CalcMgrExecuteEncryptMaxLFile command and showed how both to use that calc script function as well as the RUNJAVA equivalent.

Over the weekend, mostly because we are sad individuals, Peter Nitschke, Tyler Feddersen, and I were going over how to use @CalcMgrExecute in conjunction with MaxL shelling to run batch code.  Fascinating, eh?  Maybe.

In the course of that, I stumbled my way through the RUNJAVA syntax (which, of course, is maddeningly different than the RUNJAVA commands for running the encrypted MaxL file and no, not in the way you would think) and came up with one or two interesting twists on passing both member names and just plain old parameters to a MaxL script.

One note – I am not going to go into how to use shelling in MaxL scripts, but am just going to cover the basics and one or two odd things in running MaxL scripts in this post.

What am I trying to run?

Here’s the MaxL script noencrypt.msh:

The username, password, servername, and a single member name must be passed as parameters.

RUNJAVA

When it comes to using the Calc Mgr MaxLFunctions CDF with RUNJAVA the important bit to understand is that it is position dependent.  By that I mean the code that runs encrypted MaxL scripts and the code that runs non-encrypted MaxL scripts has no explicit flag that tells RUNJAVA to run them one way or the other.  Instead, it is the presence of the –D and encrypted keys (or maybe it’s just –D – dunno on that one as I try to have some kind of a life) that forces an encrypted script execute.  

And the absence of that information makes the RUNJAVA invocation of the CDF run the MaxL script as non-encrypted.  Interesting, eh?  I have to look into other commands but again, I sort of have a life, or at least I try to.

Remember this about RUNJAVA – it does absolutely zero syntax checking.  You could stick NowIsTheTimeForAllGoodMenToComeToTheAidOfTheirParty and EAS wouldn’t throw an error.

Non-encrypted MaxL file

If you want to do it the right way, see the below:

One thing that is nice about this is that you do not have to pass the username, password, and servername to the MaxL script unless you wish to as the RUNJAVA parameters you see there are just that – parameters.  And just like running a MaxL script from a command line, what gets passed, if anything, is up to you.

Another thing to note is the double quotes, double backslashes.  I’ve used double quotes and single forward slashes – it never occurred to me that my advice on MaxL scripts quoting and escaping also applied to RUNJAVA.  Thanks, Peter, for pointing that out.

Encrypted MaxL file

This is in contrast to the way an encrypted MaxL script is called via RUNJAVA.  I suppose one could argue that this is not all that unreasonable – when an encrypted MaxL script is called from the command line it must receive the decrypt flag and the public key.  What the @CalcMgrExecute (or its RUNJAVA) equivalent also requires is the username and password in encrypted for even though the called MaxL script has that information.  Weird.

After the encryption information, you can optionally pass more parameters such as the server name and a member name.

@CalcMgrExecuteMaxLFile

If RUNJAVA isn’t your cup of tea, you could always use @CalcMgrExecuteMaxLFile.  The nice thing about this command is that it will syntax check your code.  I actually used this technique first to back into what I would need for the RUNJAVA equivalent.

Remember that you must have a block to run this in (it is, after all, a calc script function), and only select one block in your code unless you want to run this multiple times, once for each block.

Non-encrypted

Note that parameters must be enclosed within a @LIST function.  You will also note that only the member name Inventory has a @NAME function surrounding it.

Encrypted

For a point of contrast to RUNJAVA, note that this approach does NOT force you to pass the encrypted username and password – just the public key.  No, I have no idea why the two are different since they call the same function within the CDF.  Some Things Are Not Meant To Be Understood.

Would you believe?

In both encrypted and non-encrypted MaxL, note this odd bit:

Note the @NAME() around the username.  This will syntax check and run quite nicely.  But remember, hypadmin is a username, not a member.

Alas and alack, but not all that surprisingly, when I remove @NAME() from around the member name Inventory, EAS pukes:

From this, I take it that the CDF doesn’t care if you wrap non-member names in @NAME() but does care if you leave that off of real member names.  Weird but there it is.

And of course RUNJAVA could give a tinker’s damn about @NAME – it takes parameters as you pass them and off it goes.

Addendum

Peter had shown me a technique that ignored the username and password early in our email chain.  For whatever reason, I could not get it to work.  And then, as so often happens in at least my life, I told him that, and then I tried it again, and then…it worked.  Arrrgh.  So here’s the code, thanks to Peter:

 

Just in case you missed the syntax for not passing the username and password, it is:

"",""

That is doublequote-doublequote-comma-doublequote-doublequote.  I swore that I tried exactly that in my tests but given that it failed and then worked, I got something wrong. 

I did ask Peter how he figured that syntax out as it is not documented (well, that is sort of the purpose of this post, but still).  His reply, “I'm lazy? *grin* Wanted to see how little I could put in a piece of code and have it work.”  The sign of a true hacker.  Thanks again, Peter.

Conclusion

The Calc Mgr CDF is really powerful, hardly used (although that seems to be changing), and is still not documented.  Play around with it, look for that essfunc.xml file on your server to give you a few ideas, and have fun.

Be seeing you.

Thanks again to Peter and Tyler on this – always fun, if a bit geeky, to share interests in code.

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.