Code This, Not That: From VBScript to Jython

I recently completed a fairly complex VBScript to Jython import integration script conversion. For those that are unfamiliar, Jython is the preferred scripting language of FDMEE (the successor to FDM). Jython offers numerous advantages over VBScript. Chief among those is that the language is more expressive and offers a lot of syntactic sugar, particularly compared to VBScript.

People that know me know that I have a passion for programming. I love writing code in many languages – Java is by far my strongest language, but I also am quite fond of Objective-C (with Swift on my list of things to play with), Python, and a few others. That said I don’t just like to write good, working code – I challenge myself to write clean, elegant, high-performance, easily maintainable, easy to understand, deceptively simple, and fluid code that reads like prose.

One of the greatest compliments I ever got from someone was on an Essbase automation system I had designed. The compliment was, “You made it look simple. And I know it’s not simple – it’s just that good.” I was beaming for days.

But what does this all have with FDMEE and Jython?

Think about this: code is read many more times than it is written. Therefore, it behooves us to write it with as much expressiveness and conciseness as we can, to add useful comments, to be idiomatic with respect to the language we are writing in. Along these lines, in my recent conversion project I was reminded of all the opportunities for writing better code – more expressive code – that Jython (Python) affords us over VBScript.  So to point out some of those examples, over the coming weeks I thought I would write about come particular code examples between VBScript and Jython that I think are worth pointing out, along with some general code recommendations I might have.

So let’s kick things off.

Checking if a value is one of a list of values

It’s common in an import integration script to check if some value, say, the current member from the Accounts dimension, is a particular account. The most straightforward way to handle this condition is with the following code:

if account == '0170100' or account == '0170200' or account == '0170300':
    print "Matched account!"

Thinking back to the notion of reading code more times than we read it, the English language interpretation of this code might be “If the account is 0170100, or if the account is 0170200, or if the account is 0170300, then do this thing.” You might call this the “brute force” approach – to just literally test each condition we might handle.

Can we come up with something a little more… refined? Python offers a convenient syntax that can make this code a little more readable. In Python we can use tuples. A tuple is like a variable that is a bag of other values or variables. Think of it as a simple collection. Using the “in” operator, we can test for membership in a tuple. The following code works the exact same way as the above code:

if account in ('0170100', '0170200', '0170300'):
    print "Matched account using tuple!"

Now the English interpretation of the code might be “Is the account one of these accounts?” The code is shorter, simpler, and reads a little more easily. While this example is somewhat trivial, it’s a good example of leveraging this great tool we now have to use. Let’s take it one step further. Let’s declare a variable with these values ahead of time:

gross_profit_accounts = ('0170100', '0170200', '0170300')

And then we’ll check if the account is one of these:

if account in gross_profit_accounts:
print "Matched account against account list!"

Again, this code works the exact same way, but we’ve introduced a variable name. Think of the variable as a bit of built-in documentation. We easily could have done this too:

# This is for example purposes, the previous example is the best one (in my opinion)
# Check if the account is one of the gross profit accounts
if account in ('0170100', '0170200', '0170300'):
    print "Matched account using tuple!"

But now without even using a comment, the variable name serves as an expressive explanation of what the code is doing. Again, code is read more than it is written, so I think this is a better solution than using the variable name “gp_accounts” or “gpa” or something that requires a decoder ring.

Think about the English translation of this code now: “If the account is a gross profit account, do this thing.” Also, what if we wanted to test the opposite case (not being a gross profit account)? We could do this:

if account not in gross_profit_accounts:
    print "Account not a gross profit account!"

In English: “If the account is not a gross profit account, do this thing.” It’s like the code documents itself – and that is the best kind of code. Think of code as prose!

Papercut: Calc script verification with FDM tokenization

Here’s a papercut I’d like to present in the context of my thoughts on papercuts in the Essbase ecosystem. I’ve recently been doing a bit more work with FDM. After an FDM data load you need to calc data (related to what you just loaded, although I suppose you could just calc the whole thing if you wanted to) related to the intersections you just loaded. In other words, if you are loading data for a certain time period or location or whatever, you’ll want to roll that data up. Nothing special there. So you have a normal calc script except it has been parameterized for FDM – it searches for tokens in the script, such as in FIX statements, then replaces a template variable with the real variable. So it’s like [T.Location] gets replaced with the actual location. But guess what, when you go to validate the calc script now (and you do always validate your calc scripts, right?), it doesn’t validate.


So, I’m not an FDM expert. Maybe there’s an option to work around this that I don’t know about. Maybe you can stuff these tokenized names into a dummy alias table so that you can at least validate. But it seems like the “right” way to handle this would be to find a solution where you can still validate the calc. I guess one straightforward way to do this might be an option to ignore values with brackets around them that are enclosed in quotes. But it feels wrong that using FDM and tokenizing your calc script leads you down this path. If you have worked with this and have a solution that I don’t know because I’m not an FDM expert and you are, please let me know. But right now it’s just one of those quirky little less than ideal issues that I consider an Essbase Papercut.