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.