jason w. jones

software from the trenches

Essbase Java API digging: data load from FTP?

I spend a lot of time working with the Essbase Java API, so I have become pretty familiar with it. I have used it to build a middle tier for a mobile reporting solution, a core part of the innovative Drillbridge drill-through solution for Essbase, Planning, and Financial Reporting, and countless other little apps. (In fact, my experience with Essbase, Java, ODI, and Jython are all at the confluence of one of my upcoming endeavors, but more on that later…)

In any case, much like a trip to Costco, going through the Essbase Java API can be a bit of a treasure hunt at times. One such thing I came across recently had to do with the loadData() method on a cube object (IEssCube).

Continue reading Essbase Java API digging: data load from FTP? →

Custom Functions in Drillbridge

The upcoming version of Drillbridge has some really exciting features in it. I’d like to talk about one of those features right now. Drillbridge now has support for custom functions. Drillbridge has a very powerful and flexible mapping system for easily translating between the members in your cube and related detailed data in a relational table. This is a big part of what makes Drillbridge such a compelling solution for quickly implementing drill-through.

One of the more popular functions is #removeStarting. This function strips a prefix off of a string (member name) if it has a certain prefix. For example, let’s say that the names of members in the Years dimension are FY14, FY15 and so on. You need just the numeric value in order to perform a query for the detailed data. You can use #removeStarting like this:

Continue reading Custom Functions in Drillbridge →

Running ODI Studio on OS X

I usually run ODI Studio in a Windows VM or just in an RDP session on a Windows server. But, I run a Mac (a lovely MacBook Pro) and am embarking on something of a local development project, and decided to run ODI Studio natively.

I’m hardly the first one to the party. In fact, I found some great instructions that got me 95% of the way up and running over on a blog article from 2011. I couldn’t quite get ODI to run, however. My default Java JDK is 1.8, but the version of ODI Studio I was trying to run is 11.1.1.7 (yeah yeah, it’s older, I’ll get 12c up and running soon enough…). In short, this version of ODI Studio wants a 1.6 JDK.

Continue reading Running ODI Studio on OS X →

Fun with Unix join (on Windows too!)

Last week I mentioned that I was going to take a look at some interesting Unix utilities that you can also run on Windows by way of GnuWin (or some similar GNU on Windows library).

The first item that I want to look at is the “join” program. Join can perform operations very similar to a SQL join, except that it works with text files. Let’s say I have two text files, one named people, and one named colors. The contents of the people file is a unique ID followed by a name, and the colors file is everyone’s favorite color – where the columns are the person’s ID and then their favorite color.

Here’s the people file:

Continue reading Fun with Unix join (on Windows too!) →

Drillbridge drill-to-bottom token parentheses note

Just a quick note on the syntax for Drillbridge tokens that use drillToBottom. One of the more compelling Drillbridge features is the ability to “drill to bottom”. What this means is that a user can drill on an upper-level member such as “Quarter 1”, then Drillbridge will open up the outline, get the level-0 descendants, transform them according to the token expression, and then replace the whole token with the list of members combined into a string.

The string that Drillbridge generates in this case is almost always used in a SQL “IN” clause, like this:

Continue reading Drillbridge drill-to-bottom token parentheses note →

Cool tool for generating data

I came across a really useful tool the other day for generating sample relational data. It’s a web service called genereatedata.com (inventively enough). It lets you define the columns, data types, how to generate the data (such as random names, states, numbers, numbers in a range, and more), then generate the data. You can get data in a delimited form or even directly as INSERT statements for your database of choice. It looks like it generates 100 rows of your data for you for free, but that you need to pay for a premium service that is able to generate more rows, if you need it. Fortunately in my case I was good with 100 rows.

Of course, if you need to generate data for your cubes, you might want to use a tool designed specifically for doing so, such as the Cubedata tool I built some time ago (version 2 is in the works!).

Continue reading Cool tool for generating data →

GNU CoreUtils for Windows

Did you know that many Unix core programs are available as Windows executables? Because the code for these utilities is open source, there are a couple of different organizations that have taken the liberty of compiling them for the Windows platform and making them available. One such project is GnuWin32. If you want to, say, use common Unix text processing programs on Windows as part of your Hyperion automation, you definitely can. You would just install the programs on your server, probably add their folder to your system PATH, and then be able to call them like any other program.

Why would you want to do this? There are some really great programs available on Unix platforms that might be useful for some one-off processing you do as part of your automation. In the coming days I’m going to post a few examples of some interesting text processing examples you might do with these utilities that are inspired by Essbase/Hyperion. Of course, for those of you already running Linux/AIX, you should already be set (and of course, as a smug Mac user myself, I am good to go at least with my development machine…).

Continue reading GNU CoreUtils for Windows →

Code This, Not That: Python date string formatting

Continuing on in the Code This, Not That series, I’m going to point out some convenient functions for formatting dates. Since Jython runs in the JVM, we get easy access to all of the libraries that the rich Java programming language provides.

Working with FDMEE and custom scripting, it’s likely that you’re going to need to do some work with dates. This will often involve the Java “Date” object. You might find that you need to do something with the date – get the year, get the name of the month, create a string based on the day of the month, and so on. First off, there are a few things you definitely don’t want to do.

One, you don’t want to convert the date object to a string and then start doing string manipulation on them to get the data you want. If you are converting some VBScript code you might be tempted to do something like Year = RIGHT(someDate, 4). This can work but it’s a bit of a hack. What you want to do is work with the date object directly and get some other tried and true code to do the heavy lifting for you.

Continue reading Code This, Not That: Python date string formatting →

Code This, Not That: Python string interpolation/concatentation

That title is a bit of a mouthful, ain’t it?

I often see VB code in FDM jobs that is creating SQL statements – concatenating things together, adding in parameters from variables, lots of quotes, single quotes, plus signs, underscores, and more all over the place. It’s hideous.

For example, check some of this garbage out:

Continue reading Code This, Not That: Python string interpolation/concatentation →