ODBC Setup Essbase Unix

This one’s an oldie and applies to those of us who use Unix/Linux as the operating system for Essbase. If we need to set up SQL loads to Essbase cubes, we have a couple of options, either use OCI connections, or use old school ODBC connections. In most cases, we would use OCI, but as far as I know, OCI does not support write back, to tables; something you would need to do for relational data exports. I ran into this recently, and I couldn’t remember how to set up an ODBC connection. So, this one’s mainly to remind myself how to do this, next time.

Assuming you have Putty, an account which has enough privileges to perform these actions on the Linux box and sufficient intestinal fortitude (minimal amounts will do), let’s begin. Once you login, we need to find the ‘odbc.ini’ file and update it in order to make this work. The Oracle documentation tells us we can find the file in the following location:

If you find it hard, you can execute the following command to find the file for you:

Since we are using 64-bit Essbase (surely!), we navigate to the location of the odbc file.

We can use a trusty text editor like vi to open the file. The beginning section of the file contains the names of the various data sources that are set up in the file. The one we care about, is the Oracle Wire Protocol data source.

If you scroll down a bit further, you will see the details of the Oracle Wire Protocol data source.

First off, we need to update the ODBC Data Sources section with a new entry for our Oracle data source.

Now, we need to copy the lines from the Oracle Wire Protocol section and paste it to the end of the file. Since the name of our source is “UNLK”, we need to update the name accordingly.

We also need to update the following sections:

An updated file might look like the image below:

Note: if using host name, comment out the “ServerName” entry. Similarly, comment out #ServiceName, if using an SID. Commenting can be done with the “#” symbol.

And that’s it. Fire up EAS, and you should see the new data source on the drop down list of SQL data sources, when setting up SQL load rules.

Perform a quick test.

And there we have it, a simple update to the ODBC file, and we can use SQL to our heart’s content.