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:

22_1_Doc

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

22_2_Find

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

22_3_CD

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.

22_3_DS

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

22_4_OWP

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

22_5_Add

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.

22_6_DS_2

We also need to update the following sections:

An updated file might look like the image below:

22_7_Updated

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.

22_8_DS_Essbase

Perform a quick test.

22_9_Test_Essbase

22_10_Test_Essbase

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

Avatar

About Vijay Kurian

Known as the Clem Fandango of EPM consulting, Vijay Kurian has been developing enterprise solutions for companies for the last 12 years (increment years if reading post-2015). Having worked with Essbase, Planning, DRM and other assorted technologies during that time, he’s made the frankly, average decision, to write about them. He is, surprisingly, an Oracle ACE Associate. He hopes to contribute frequently to US Weekly, People and Sensible Chuckle magazines on improving reporting solutions, creating master data management systems and zzz…

5 Comments

  1. Thanks for sharing ..very detailed way..Awesome Post..!!!

  2. Hi Vijay,

    Can we have encrypted password in ODBC.ini for connecting Oracle database?

    As giving password in ODBC file is an confidential, can we have any workaround?

    Regards,
    Swetha

  3. Hi,
    I tried using the same steps and updated odbc.ini file, but I am not seeing SQL data sources in EAS. Can you help me with this?
    I get error message: cannot read sql driver name from[/applic………/.odbc.ini]

Leave a Reply

Your email address will not be published. Required fields are marked *