Using ODI to Load an Essbase Exported File to an Oracle Table

Here, we’ll cover the ability to use ODI for one its basic features: extracting data from Essbase and using the exported file to load to a table. It turns out that ODI was not recognizing the file generated by Essbase. Let’s see what we were missing:

We have a calc script exporting level 0 data from an Essbase cube. We were using Dataexport command in the calculation script to export the data from Essbase BSO cube. In ODI, while building the file datastore, we placed a sample file at the desired location. The exported file from Essbase was a comma delimited file. We built a model to the file location and then a datastore. We reverse engineered the columns by using a sample of exported file.

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

Even though we were able to reverse engineer the columns successfully when we tried to pull data / view data from the datastore, the result was a blank.

The view data always resulted in a blank output as shown. It did not generate any error message.

It turns out the record separator in file generated from Essbase uses a Unix record separator which is easy enough for a Windows user like me to miss. (Just mind you – file looks perfectly normal when you open it in Notepad++ or Word or excel since MS office converts the file when you open it. And ODI doesn’t generate any error message – So quite easy to miss)

As soon as we changed the record separator to Unix, Voila- It all started working as expected. The file was pulling data just fine. And we didn’t have to reverse engineer the columns again either.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Nandini Nehru

Nandini Nehru is a Sr. Solutions Architect at Perficient in the Oracle EPM group. She has worked for ten plus years on various projects performing analysis, design, development, configuration and testing of applications. She has implemented Oracle Hyperion planning, HFM and Essbase applications for various industries like Healthcare, Public sector, Manufacturing and Oil and gas. She is a certified Oracle Trainer for Oracle Hyperion Financial reports, Planning Create and manage and Administrator class. She is also Oracle Hyperion Planning 11 Certified Implementation Specialist and Oracle Enterprise Planning and Budgeting Cloud Service 2017 Certified Implementation Specialist. Nandini works closely with the financial planning team at her clients across different industry verticals and implements IT solutions to meet their financial planning and budgeting needs. Nandini holds an honors degree in Bachelor of Engineering in Information technology and an MBA in Finance from Rice University. She was also a recipient of the renowned Jones Scholarship at Rice University, Houston.

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Follow Us