jason w. jones

software from the trenches

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.

Continue reading Code This, Not That: From VBScript to Jython →

Advanced Drillbridge tokens: Protecting against unexpected members

Here’s another quick tip for setting up Drillbridge tokens. I recently created a Drillbridge report that needs to map a member from the Cost Centers dimension over to multiple columns in a relational database table. For example, a cost center may have the format 111-222-333-44, and the code 222 corresponds to a column in a table, 333 corresponds to a column in a table, and 44 corresponds to a column in a table (these are all in the same table, by the way).

Because the incoming member on the POV comes across as a Java string, we can perform normal Java string operations on it. In this case we can easily extract the “222” by doing the following (assuming the cost center dimension is named “Cost Centers”:

Continue reading Advanced Drillbridge tokens: Protecting against unexpected members →

Drillbridge White Paper now available

I am excited to say that the official Drillbridge white paper is now generally available. It is titled “Drillbridge: Easy Hyperion Drill-through with No Redevelopment”. In my first official white paper, I have hoped to concisely capture the essence of this useful software tool that is now amazingly  approaching 20 production deployments.

In other news, the next version of Drillbridge is still in progress, slowly but surely. The focus continues to be on polish, performance, and incremental feature additions. I have a few small but exciting tidbits that I will hopefully have a chance to post later this week.

Continue reading Drillbridge White Paper now available →

ODI journal table performance tweak with an index

The other day I provided a crash course on simple journalization in ODI with an Oracle database. I wanted to set the stage for this performance tweak that I put in awhile ago on a few tables.

I mentioned that ODI stores journalized rows in a special table it creates that tracks the subscriber, processing status, and date of each row. The possible performance issue has to do with what ODI does during the processing of rows out of the table. If you have two different systems (subscribers) for a given table, when an interface runs, it is going to process data for just one subscriber at a time (usually). During processing ODI needs to mark these rows that it’s working with by changing the JRN_CONSUMED column to 1 for each matching subscriber.

Continue reading ODI journal table performance tweak with an index →

ODI journalization crash course

I do seem to love me some ODI journalization. It has been a really great feature that I have implemented countless times to great effect. For those not familiar, journalization in ODI is where you tell ODI that you want to record what rows get updated and inserted in a particular table, such that when you load data out of that table, you can choose to work with just those updated rows.

This is useful for many obvious reasons: only processing data that’s new or changed is usually faster than processing a whole table. When you are dealing with millions and billions of rows, it’s a necessity. Journalization is also nice for instances where it replaces processing data based on time.

Continue reading ODI journalization crash course →

ODI Oracle Merge Incremental Update burns sequence values

I use Oracle Data Integrator for quite a bit of ETL work. Most of it tends to be with Oracle databases, but also a fair bit with MySQL, SQL Server, and others. A common integration is to update data in a table based on a primary key, and insert new data if existing data doesn’t exist.

It’s also common to insert a sequence value into a row that you populate in a table somewhere. Sequences in Oracle are this mechanism where you basically ask them “Hey, give me the number in something we’re counting!” and then plug that value in. They are commonly used to generate unique values for rows that get inserted somewhere.

Sequences aren’t random numbers – they start off at 1 (or whatever you configure them to) and then count up, and up, and up. Like up to the number 999999999999999999999999999 (that’s huge!). In fact, there’s so many possible sequence values that if you happen to use a few by mistake, it’s not that big of deal. Just use a new sequence value! It’s not uncommon to see gaps in a table or rows that have a sequence value – this just happens due to the way data is processed and how the Oracle server will return blocks of sequences to be used in a given operation, then discard any that don’t get used (this might seem wasteful, but it’s not).

Continue reading ODI Oracle Merge Incremental Update burns sequence values →

Glitch with a non-printing Unicode character in member name

This is an Essbase bug, kind of. I’ve been working on a project lately that uses the relatively new MaxL Essbase outline export command (yes, Pete, I called it relatively new again, even though according to you it’s not… well, it’s relatively NEW TO ME SO THAT’S WHAT MATTERS… :-). Anyway, I wrote a quick XML parser for the output, using Java.

The nice thing about the parser is that it uses something in the Java language called JAXB. It’s a really nice way of modeling the contents of an XML file using Java classes, so that you don’t have to write your own parsing code, which is tedious and error prone. There are reasons you might use either approach, but overall I have been very happy over the last few years with the ability to write XML readers in Java with JAXB.

Continue reading Glitch with a non-printing Unicode character in member name →

Drillbridge White Paper now available

I’m crossing another bucket list to-do item off of my list: writing my first white paper. And what’s it on? Drillbridge, of course. I’ll be making it available to a wider audience next week, but if anyone is interested in a concise, succinct paper on how Drillbridge works, and how it can be used to quickly implement drill-through with a minimum of resources, please email me and I’ll send it right over. I’d love to get your thoughts.

Continue reading Drillbridge White Paper now available →

cubus outperform EV Analytics Review: Position in the Enterprise

Welcome to the fourth installment of my increasingly inaccurate EV three-part review. If you missed the first three parts, you can check out the EV background, using EV, and using EV continued parts to catch up!

I hope you enjoyed this little mini-series on a really interesting tool so far. As I mentioned in the first article, this tool has a special place in my heart owing to how critical it was to my Essbase life going back as far as 2005. I was quite the EV enthusiast back in the day within my company, but when I talked to people about trying it out or using it, it quite often fell flat. I’d often hear, “Why don’t I use the Excel add-in?” or “Why do I need another tool for that, I already have [whatever]” or “isn’t the enterprise standard [some enterprisey thing]?”

Continue reading cubus outperform EV Analytics Review: Position in the Enterprise →

Drillbridge Token Parameter Overview

Tokens in Drillbridge are a large part of the secret sauce that makes everything work so well. They are simple, flexible, and powerful. If you haven’t noticed, they are essentially JSON strings that have a range of allowable values. The most common of these are “name” and “expression”, but there are actually a handful of other keys that are less well known but incredibly useful. I’m going to cover the current state of Drillbridge tokens in this post.

As of the current version of Drillbridge (version 1.5.2), the following parameters on tokens are recognized:

Continue reading Drillbridge Token Parameter Overview →