ODI Series - Loading Essbase Metadata

On one of my previous blogs I went through the steps to extract metadata from essbase, so today I will cover the opposite and load metadata into essbase. Essentially it is the same sort of process except we are swapping over the source and target and changing the Knowledge Modules we need to use, though I thought that you always need to add a little more information into each blog so I am going to bring attributes into the equation as well.

If you have not read my blog on extracting essbase metadata then I advise you have a read through it if you are not familiar with ODI as I am going to assume you are. I am also going to use some of the same models and configurations I have used in the past.

I am going to be using the Data Server and Physical schema I have set up previously connecting to the Sample.Basic database.



The basic database has five attribute dimensions.



The first thing to do is to create a model to hold the reversed essbase database; if you already have a model you can use the same one and run the reverse again, the important difference this time is that we are going to set an additional option for the RKM.



If you set EXTRACT_ATTRIBUTE_MEMBERS to “Yes” when the reverse is initiated it creates extra columns in the dimensions which are associated with attributes, e.g Product will have columns for Caffeinated, Intro Date, Ounces, Pkg Type.



So now we have our reversed model we need some source metadata to try and load into the essbase outline.



I created a CSV file that will load a new branch to the hierarchy and set an attribute for each new level 0 member, the file was saved into the location where the file technology is pointing to (in my case E:\FileStore)

A DataStore was created using the flat file model, the file format was set to Delimited, heading set to 1 and field seperator set to comma.



The DataStore was then reversed to create the column information for the flat file.



So that’s the source and target done, time to create the interface.

The interface was names and the staging area set as “SUNOPSIS MEMORY ENGINE’.
In the diagram area the flat file DataStore was dragged on to the source and the essbase DataStore dragged onto the target, most of the columns were automapped except for the columns with spaces in such as “Pkg Type” and “Intro Date” so these were manually set up.



In the flow area, the LKM was set to “LKM File to SQL” as we are loading from a flat file.

The IKM was set to “IKM SQL to Hyperion Essbase (MetaData)”



There are a few options to be aware of which have not appeared in previous ODI blogs

RESTRUCTURE_DATABASE has four settings and is very much the same options you have when you make changes to an outline in EAS, most of the time I would think this is likely to stay as the default : KEEP_ALL_DATA.

PRE_LOAD_MAXL_SCRIPT & POST_LOAD_MAXL_SCRIPT gives you the ability to run maxl scripts before and after executing the interface.

At this point you may think that it is alright to execute the interface, unfortunately not so fast, the current IKM is not aware of many options for loading metadata such as your dimension build properties Allow Moves, Allow property changes to name a few, this means you have to create a load rule the same as you would if you were loading metadata into essbase.

I can understand some people saying well what’s the point of using ODI if I need to use a load rule, well ODI is all about integrations and not just doing one thing like a metadata load, saying that though I do hope that in future IKMs for essbase they start to add in more options and have the choice of using a load rule or not.



A load rule was created and the name added to the options of the interface, if the load rule exists in the essbase app directory then the name is only required, if the rule file is elsewhere you need to enter the full path details to it)



No problems executing the interface.



In the essbase outline all the new members were added and the attribute information correctly associated.

Well that’s metadata loading and extracting now covered off so next I will be moving on to data, until then enjoy.