Sunday, 11 January 2009

ODI Series – A few extra tips

Before I move on to the final part of my ODI series I thought I would just point down a couple of things I have come across while looking at ODI, I thought I had better write them down because they hopefully help others that come across the problem and also jog my terrible memory if I am asked again.

Essbase Loading Multiple UDAs

It is simple load multiple UDAs with planning and ODI but it doesn’t seem the functionality has been built in by standard with the current KMs for essbase, saying that it is not difficult to get round it.

By standard reversing an essbase cube into a DataStore creates one column for a UDA, it does the same for planning but you can separate each UDA with the use of a comma in your source, I did try this at first with essbase but it just created one long UDA string with all the commas.

For this integration I am going to be loading multiple UDAs into the product dimension from a flat file.

First all I duplicated an existing reversed essbase DataStore, it is entirely your decision if you want to do this or not.

Next I expanded the product dimension and inserted a new column and named it UDA2.



Now you would carry on as you would usually, create your source file including the UDAs



Reverse the file to create a DataStore



Create the essbase load rule and test with the source file.



Create the interface



Execute.



All done, nice and simple.

This method can be used in other areas such as ASO cubes and trying to load solve order information. By default when you reverse a cube it does not distinguish between a BSO and an ASO cube so you always end up with the same columns., when reversing the Java code uses a file called EssbaseMetadata.xml which holds the DataStore definitions, the XML file is located in the odihapp_essbase.jar



This is fine when it comes to loading solve order but unfortunately if you want to extract solve order information I don’t think with the current code it is possible.

In the Java code is uses one of the API IEssMemberSelection methods called executeQuery



The query does not include Solve Order so that removes the ability to ever return the value, looking at the essbase Java API documentation it doesn’t even look like you can use the executeQuery method with Solve Order anyway and would have to use another way with the API to retrieve the value, so unless you are willing to rewrite the code it looks like it won’t be possible until Oracle add some extra functionality to the KM drivers.

Next a really simple tip for when you are trying to sum data from a source file into Planning, by default when loading data into planning it loads one record at time and is set to overwrite so if you had say the following data.



If you try and use ODI to load the file into planning each record would overwrite the last you would end up loading only the value of 3422 when you wanted the summed value of 7286.
Now if you were using a SQL database as your source you could easily create a view to return the grouped value but we are using a file, so how is it done without modifying the source.

First make sure you source DataStore data field is set to a Numeric Type.



In your interface if you enter the expression editor and use the SUM function on your target data column.



Using the SUM function will automatically create a group by query on the source data so the result is summed.

So there we go a couple of tips, easy and simple if you know how.

3 comments:

Anonymous said...

Hello John,

This is kind of become a bible for Hyperion customers who wants to know every thing about ODI with respect to Pln/Ess usage. Great Work!!

We are currently working to replace HAl with ODI and in the process of putting together a TOPOLOGY but looking to get some thoughts..

The question we have is what's the best approach/architecture to setup ODI. We have DataWarehouse as a source, Planning and Essbase in separate servers.
(ie) DW in Solaris 10 (24CPU)
Planning 9.3.1 (Win2003, 4CPU)
Essbase 9.3.1 (Solaris 10, 12 CPU)

I understand the license of ODI is based on were you install the Master and Work repositories...so the option we have is to create them on DW server with 24CPU's (or) install a Oracle Server DB on the Essbase solaris (12CPU) (Or) introduce a new Win2003 server (4 CPU) and get Oracle Application Server and ODI components with Master/Work repositories installed...

I'm looking at the best approach with faster response and lower license cost.

Any help is greatly appreciated!!!

Anonymous said...

Hi John,

Many thanks for sharing your knowledge on ODI and its implementation views...

Could you please get some guidance on the error handling part of the ODI. Meaning, what is the best way for reporting the error (transactional level) during a batch scenario ?

Looking forward your advice on this. Thanks again!!

Sanjay said...

Hi John

Thanks very much for your blog, it has provided extremely helpful.

One question I do have though is, how can ODI testing be automated?
ie

a) Have a gold set of data
b) Deploy ODI code
c) Run test steps and get report of code if it failed or not?

Do you know of anything that can do that?

sanjay