How ODI IKM SQL to Hyperion Essbase (METADATA) influences data loading

a woman in eyeglasses smiling beside her colleagues while looking at the laptop

Oracle Data Integrator integrates with Essbase for metadata as well as data loading using different Knowledge Modules. Each of the KMs provides a range of options for us to customize the loading as we want. The IKM SQL to Hyperion Essbase (METADATA) is usually the starting point when we begin our activities, since first we will load the metadata and get the outline ready, then we can load the actual data.

ODI IKM SQL to Hyperion Essbase (METADATA) Loading
ODI IKM SQL to Hyperion Essbase (METADATA) Loading

The standard practice of the using the IKM SQL to Hyperion Essbase (METADATA) is to create an ODI interface for each dimension using this KM, and provide the values of the parameters in each of the interfaces as applicable. As observed below, we need to create a rule file for the different dimensions in Essbase and provide those rule file names as the value for the parameter RULES_FILE. In case we need the data to be loaded in a specific order, we can use the ORDER_BY clause accordingly.

IKM SQL to Hyperion Essbase (METADATA) Details

So we can create an ODI package with 6 different interfaces, each with it’s own rule file and loading the corresponding dimension hierarchy in the outline. The value for RULE_SEPARATOR need to be set correctly here, else the outline will not reflect any updates and the interface will not work. The value for RESTRUCTURE_DATABASE defines what to do after the metadata loading. If we are clearing out all the data prior to our metadata loading via MaxL, then the default value KEEP_ALL_DATA does not make a difference. Else we can use the values KEEP_INPUT_DATA or KEEP_LEVEL0_DATA or DISCARD_ALL_DATA as per our requirement.

The Essbase cube outline refresh plays a very important role in the daily life of the cube. If the loading process or parent child loading sequence is not set correctly, the members can fall off during the loading phase. For example, if we try to load the child before it’s parent, the child will fail to get loaded. And then the parent will stay without it’s child at the end of the load. It becomes even more difficult to track such scenarios if we have LOG_ENABLED as the default false value.

The LOG_FILE_NAME and the ERROR_LOG_FILENAME can prove to be very beneficial in such cases. It is always recommended to generate a log during our loading phases, unless we have a strong data quality control check in place. Incorrect outline build can drastically affect the subsequent data loading process, leading to multiple records rejections due to missing members in the outline. This can lead to confusion and data mismatches across different data sources, thus causing a nightmare for developers, more so if insufficient logging is in place.

a woman in eyeglasses smiling beside her colleagues while looking at the laptop

While loading the dimension members, we can also use the ODI interfaces to load the Alias values of each of the members. If we have more than one Alias, we can accordingly use multiple interfaces and rule files to populate the values accordingly. Only thing is we have to make sure each of the rule files point to the correct Alias in the path below:

    Rule File → Dimension Build Settings → Global Settings → Global Properties → Update Alias Table → <>

So we can use identical ODI interfaces with different values in the Alias field in the Target and different rule files values in the flow properties to load them.

The ODI interfaces during meta data loading sometimes gives the error ‘Cannot open cube outline’. This is often caused by a parallel running job which is in incomplete status, thus preventing our interface to have a lock on the outline. Or it can be due to a developer who has locked the outline in edit mode but forgotten to unlock it – thus again preventing our ODI interface to get access to the Essbase outline in Edit mode. In such cases we need to identify the lock and then release it, then restart our ODI process. Subscribe and Download the IKM SQL to Hyperion Essbase (METADATA) guide below, then read page 4 for more details on each of the configuration parameters.

Subscribe for $12 per year. Get live stories and updates.

Already a member? Log in.

  • Premium Articles & Downloads
  • In-depth comprehensive analysis
  • Monthly price of only a movie ticket
  • 3000+ Practice Questions included
  • Advanced Aptitude & Coding Questions
  • Real-time feedback in every question
  • Continuously updated database
  • Wide coverage of industry news
  • Career & IT Consultation, Contact Me

Thanks for reading
Insight Crunch.

Create your account or
log in to continue reading.


  • Join thousands of subscribers
  • Get instant access to this article
  • Silicon Valley latest events coverage
  • Unlimited access to Premium Articles
  • Wide coverage of industry news
  • Mock Tests for Strategic Preparation
  • 3000+ problems on variety of topics
  • Real-time feedback in every question

Support independent journalism.
Already a member? Login.

This IKM is pretty peaceful otherwise and keeps doing it’s tasks quietly over time. How do you use your IKMs to refresh the Essbase metadata?

Author: Rahul Bhattacharya

Rahul is a journalist with expertise in researching a variety of topics and writing engaging contents. He is also a data analyst and an expert in visualizing business scenarios using data science. Rahul is skilled in a number of programming languages and data analysis tools. When he is not busy writing, Rahul can be found somewhere in the Appalachian trails or in an ethnic restaurant in Chicago.

3 thoughts on “How ODI IKM SQL to Hyperion Essbase (METADATA) influences data loading

  1. Hello,Can you help me with one issue i have during Essbase metadata load using ODI.The load is going fine as per logs but there is no change in the outline when we look at the cube.Thanks in advance

Leave a Reply