Sunday, 2 November 2008

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.

8 comments:

paolo said...

Did you try to load metadata also from an rdbms source?

Briareus said...

hi,
thanx for the blog at first. it's very informative blog.
i want to ask something. is it possible to create an Essbase database by using ODI ? I mean, at the examples we have an existing database. what if, we create a new app and database(just as a name.no outlines or others...) and try to load metadata and data? Can we do that or we must have a defined database at essbase? if it is possible can u make an example?

thanx a lot...

John Goodwin said...

Hi,

It is not possible to create an essbase database, ODI is more of an ELT tool. The only way you could do it would be to write some JAVA or maybe maxl and then run that from a package.

Briareus said...

Hi again John,

I have another question if you don't mind...

i am practicing loading metadata and data via odi to essbase. i use Sample-Basic database that comes with essbase. What I want to do is laoding metadata and data to a new database as my previous question. The new outline is empty. So i cant reverse anything for target. I create datastores manually. To load outline i need a rules files. to create rules file i try to use essbase but when i try to "validate" them it says "outline is empty u cant validate". but i am trying to fill the outline. To fill outline i need rules file but to create a rules file i need a complete outline. Where am i doing wrong?

I hope i can make it clear

Thank you

John Goodwin said...

Hi Briareus,
Sorry for the delay I did not notice your comment.
As I said previously ODI is not intended to create new dimensions, the dimensions need to exist and ODI will be able to populate them.
You could use maxl or an API to create the dimensions first and then run an integration to populate them with metadata.

Anonymous said...

First of all, I would like to thank you John, I have been learned a lot with your blog. Regarding Attribute dimension using Hyperion Planning Adapter, I cannot see inside the RKM Model options to define extract my attributes dimensions. Do you know these options are available only to Essbase adapter or also to Planning one?

Wallace Galvao
Brazil

Anonymous said...

Hi,

I have a problem that I hope you can help me with.

I have a project structure which holds different amount of children, e.g
Main Level Project
Level Projcect
Sub level project
and
Main Level Project
Level Project

I use two different rules for this, the first one rejects those that have Sub level project put as none, and the latter the ones where sub level is none, hence no sub level.

Addning the attributes to the sub level projects is not an issue. However, adding the attributes to the project level, on the ones where no sub project level exists is not working. Is there something I should think extra about when loading on different generations? I use level 0, so it shouldn't be a problem?

I'd be so grateful if you could help me with this!

Thanks
Kristin, Sweden

Albert said...

Hi.

Thank you so much for your blog. I have a question. Could I have a legal issue or warranty with Oracle for decompile a jar?.

Albert
Mexico