Essbase Java API Load from FTP quick followup

I was accused of being a tease for not knowing when the apparently “load from FTP” functionality got slipped in to the IEssCube loadData() function. I did a little digging around and it looks like it shows up no later than 11.1.2.2 and is definitely not in 9.2.1 (based on Javadoc). I’m not sure about 11.1.1.3, 11.1.1.4, and 11.1.2.1 – I couldn’t quickly find their Javadoc pages (11.1.2.1 seems to just redirect to 11.1.2.2 on Oracle’s site now, others just tell you to check out you local APS folder…). I hope you’re happy (happier) now, Tim German. :-)

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).

There are actually a few loadData() methods – in programming parlance, the method is overloaded. That is, there are multiple functions with the same name, but they differ with their calling argument types, so behind the scenes, Java is intelligently able to figure out which one to call. Method overloading is frequently done for programming convenience.

For example, an object might have a method getGreeting(String name) that takes a name and returns “Hello ” plus that name. Another method with the same name might be getGreeting(String name, Date time) and this returns a greeting that is customized by the time of day.

The Essbase cube object in the Java API contains three loadData methods. One of them caught my eye. There’s a loadData() method that takes a username and password. According to the docs, you can actually load a file over FTP (in which case the file name would be an FTP path, presumably with a server and maybe even prefixed with ftp://), and the username/password are at the FTP username/password.

I thought this was kind of cool because it’s not something that’s ostensibly visible in EAS. So it could be something that’s buried in the API that is used behind the scenes, or maybe it was some plumbing done for PBCS. Maybe it has even been there forever. Like I said, I thought this was interesting… there are also a few other fun tidbits I’ve seen over the years in the API so I’ll try and point those out in the future. If you know of some please send them my way!

Issue starting Drillbridge on Windows 2008

I’m still tracing this through, but there’s an occasional issue I’m seeing on Windows 2008 servers when it comes to starting Drillbridge. If you get an error when you try and start the service, check your logs and try to find the cause, as you normally would. If you see an error along these lines:

INFO   | jvm 1    | 2015/04/07 10:09:39 | org.springframework.context.ApplicationContextException: Unable to start embedded container; nested exception is org.springframework.boot.context.embedded.EmbeddedServletContainerException: Unable to create Tomcat tempdir

It could be a permissions problem such that the account running Drillbridge does not have high enough privileges to create a temporary folder for some web server files. If this is the case, you can actually specify the temporary folder yourself.

Open up /config/wrapper.conf and search for the text “additional”. You should find a commented our line like this:

#wrapper.java.additional.1=

You can leave that uncommented but just add in the following line:

wrapper.java.additional.1=-Djava.io.tmpdir=D:/Drillbridge/tmp

Then go make sure that you create the /tmp folder so that the folder path exists. You don’t have to use this folder name, drive, path, or anything. It just has to be a folder that exists and that the Drillbridge user has the ability to write to. Save the file and then start (or restart if Drillbridge was already running) Drillbridge. Everything should now start fine.

I’m still tracing down the root cause of this to try and make it so that users would never have to apply this fix themselves but until then, this fix works well enough. I haven’t seen this issue on Linux editions of Drillbridge – just Windows Server 2008 in environments that are relatively locked down.

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:

#removeStarting("FY", #Years)

And the value returned by the function will be 14, 15, or whatever the proper year is. The newest version of Drillbridge now supports adding custom functions by way of dropping in a Java file that contains them. This way if you have a complex mapping that needs to happen, you can use the full power of the Java language to accomplish it. For example, consider a function that needs to do special processing with respect to a combination of the year and month, in order to get a particular time value stored in a GL system.

public static int getJulianLastFromYear(String fiscalYear, String shortMonthName) {
  Integer year = Integer.valueOf("20" + DrFunctions.removeStarting("FY", fiscalYear));
  Integer month = Integer.valueOf(DrFunctions.monthToTwoDigits(shortMonthName, "en"));
  return getJulianLastOfMonth(year, month);
}

Then just compile this class as normal, drop it in the Drillbridge drivers folder, restart Drillbridge, and you can now use this function inside of your own expressions, just by calling it with whatever name you give it (in this case, it might be #getJulian).

This is a really exciting feature that will offer tremendous flexibility, particularly in cases where complex mapping is needed that might be difficult or impossible to perform with the relational database. Stay tuned – there are a few more very interesting features coming down the pike…

Running ODI Studio on OS X

ODI Studio running on Mac 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.

So the trick was how to leave my system default JDK at 1.8 but then force ODI to use the 1.6 JDK that’s also installed (just not default).

Normally you can use a SetJavaHome option in a conf file, but this didn’t seem to change anything. I think the ODI launcher tool (which is based on a generic Oracle IDE launcher system) just kept quickly seeing that I have a JAVA_HOME set to a 1.8 JDK, so it was just running with that and not trying anything else. So I dug through the files and found that the launcher will respect a special Java home variable named OIDE_JAVA_HOME, and use that first. So without making any other change, this following can be used on a Mac to force the use of a 1.6 JDK when there is a higher version installed as the default:

export OIDE_JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home

Then just run ODI as normal:

./odi.sh

After that, ODI Studio launched like a champ with no error messages:

ODI Studio running on Mac OS XI’m not sure how many people will find this useful (not many), but if this post is like many of my others, I’ll be stuck on this in a couple of years, Google around for a solution, then find my own article and be amazed that I forgot that I solved it…

 

 

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:

1 Jason 
2 Cameron 
3 Celvin 
4 Michael 
5 Opal 
6 Amy

Here’s the colors file:

1 Orange
2 Pink
3 Purple
4 Red
5 Blue
6 Hot Pink

As a very simple case, we can run join with just the names of these two files:

join people colors

And we’ll get this output:

1 Jason Orange
2 Cameron Pink
3 Celvin Purple
4 Michael Red
5 Opal Blue
6 Amy Hot Pink

In this case, the join program just assumed that the the first columns of each file were the same thing, and join accordingly. Again by default, the output was all of the columns from each file.

Let’s change things up and use a different people file, this time named people-small and containing fewer entries, like this:

1 Jason 
4 Michael 
5 Opal

Now if we run join with this file but the same colors file, like this:

join people-small colors

This will be our output:

1 Jason Orange
4 Michael Red
5 Opal Blue

If you come from the SQL world at all, you will notice that by default, join performed an inner join with respect to the data – that is, the three rows in the “left” data store (people-small) were paired up with the matching rows from the colors file, but the colors that didn’t match anything were just discarded. Similarly, if we run the full people file with a smaller colors file (named colors-small), we’ll see something similar. Consider this colors-small file:

2 Pink
3 Purple
6 Hot Pink

And running this command:

join people color-small

Then we’ll get this:

2 Cameron Pink
3 Celvin Purple
6 Amy Hot Pink

Again, an inner join was performed, in that only matched up rows (from one side or another) were paired up and put into the output.

Note that this behavior (assuming the first column is the ID to match, also, performing an inner join) is just the default behavior for join. As with most Unix tools, join has a plethora of command-line switches that define its behavior. You can choose different join methods, fields, and more.

The Hyperion Connection

I wanted to mention this tool specifically because I think for many of us working with ostensibly relational data (in text files), it can come in handy every now and then. I actually had a perfect use case for this the other day were I just wanted to quickly load some data but I didn’t want to mess with ODI to load it up or deal with importing it to a database. I had to sort the records in the input file first, but after that the join program worked like a charm and generated a file that I could easily use with a load rule to load up to a cube.

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:

SELECT * FROM Transactions WHERE Month IN ('01', '02', '03')

In this case, the tokens ’01’, ’02’, and ’03’ were perhaps generated because they were transformed with the #monthToTwoDigits function and processed the member names that are the children of Quarter 1 (“Jan”, “Feb”, “Mar”).

Earlier versions of Drillbridge required you to supply the starting an ending parentheses, such that your query text looked something like this:

SELECT * FROM Transactions WHERE Month IN ({{token definition}})

This changed back around version 1.5.0, however. Now Drillbridge by default will supply the starting and ending parentheses. This change was made to accommodate a few use cases where for performance reasons you might need or want to generate the query a little bit differently (such as using a sub-select or something fancy).

Given that current and future versions of Drillbridge supply these parentheses, you would instead write the query before more like this:

SELECT * FROM Transactions WHERE Month IN {{token definition}}

If you really want to supply the parentheses yourself for some reason, you can set the “suppressParentheses” parameter on your token to “true” and Drillbridge won’t supply them – meaning that you will write the query more like the first style. While I’m on the subject, also note that by default, Drillbridge surrounds your member values with single quotes (see the first example in this post where 01, 02, and 03 are all surrounded by single quotes). You can also turn this off if you need to (for example, if the items in the IN clause must be numeric) by setting the “quoteMembers” token parameter to false.

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!).

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…).

Another such project that otherwise makes Unix tools available on Windows is the Cygwin project, which you might want to check out too if you need to get this functionality on a Windows machine.

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.

There are other ways to achieve this, but a pretty simple and robust one is the SimpleDateFormat class. The SimpleDateFormat class allows us to specify a formatting code, then supply it a Date object, and it’ll generate a string for us.

For example, the formatting code for the abbreviation for the month of a date object is “MMM”. Therefore, we can create a SimpleDateFormat object with this code, pass it a date, and the resulting string is Mar or Apr or whatever the current month is:

import java.text.SimpleDateFormat as Sdf
import java.lang.System as System
import java.sql.Date as Date

date = Date(System.currentTimeMillis())

monthShortName = Sdf('MMM')
print monthShortName.format(date)
# prints "Mar" (or whatever the current month is)

There are plenty of other codes that take care of formatting the minutes, hours, seconds, year, and more for us. We can even combine codes in a single string, like the following:

dateFolder = Sdf('yyyyMMdd')
print dateFolder.format(date)

In this case, the format is the four digit year, followed immediately by the two digit month (including a leading zero if it’s less than 10), and the two digit day. You can refer to Oracle’s documentation for more codes over here. You can also put in other characters that might be ignored in the formatting string, such as hyphens between codes, colons, spaces, and more.

Also keep in mind that you create the formatting object and use it as many times as you want to format dates into strings. In other words, if for some reason you need to format many dates into strings using the same format, you just have to create one SimpleDateFormat object and then just use it over and over again.

As I mentioned, there are plenty of ways to go about formatting dates in Java (Jython) but this is a particularly flexible and useful approach. With just a few lines of code you can generate all sorts of formats that you might need. Consider this an FDMEE/Jython pattern – and anti-patterns to this might include the following code situations you can cleanup and make more readable:

  • Treating dates like strings and extracting data via substrings
  • Prefixing “20” on a two-digit year to get a four digit year
  • Keeping a table of month names in an array and using the month number to get the proper month name
  • Keeping a table of month names and then taking the first three characters to get the “short” month name

Lastly, note that the fdmContext["PERIODKEY"] object (which is where you are likely to be doing some date processing) comes in as a java Date object (in other words, if you are treating it as a string object, you are actually relying on the Jython interpreter silently casting it to a string for you, when you could just be working with it directly). For example, to get the numeric four digit year from the PERIODKEY object, you could do this:

# Note the explicit conversion to an integer
year = int(Sdf('yyyy').format(fdmContext["PERIODKEY"]))

Happy coding!