ODI Series - Planning and multiple UDAs

I thought I would post a quick blog about loading multiple UDAs in planning with ODI, if you have used HAL in the past then you probably already know but I have seen the question asked a few times, I am also going to demonstrate how to load UDAs from a multi column source.



In the example above I have two UDAs that I can use and say I want to apply them both to one member using ODI then the answer is very simple, in your source all you need to do is separate each UDA with a comma.



The thing to remember if you are using a csv flat file as the source is that the UDA details will be enclosed in quotations as they contain a comma



This means that in your flat file source DataStore you will need to add in the text delimiter into the configuration.



In your interface just map the UDA and you are done.



And in planning it should of applied both UDAs to the member properties.



So what if you have a source that has separate fields for each UDA, the fields for each record will not always be populated e.g.



Now because you need to separate each UDA with a comma you can’t apply the standard logic and just concatenate the fields, if you did then you would have something like.

Record 1 – UDA = “ODI_UDA1,ODI_UDA2”
Record 2 – UDA = “ODI_UDA1,”
Record 3 – UDA = “,ODI_UDA2”

If you tried to load them into planning records 2 & 3 would fail.

To get around this is in ODI you need to create an expression on the target.



Click the “Launch Expression Editor” icon.



The following functions are going to be used

CASEWHEN(<condition>, <truepart>, <falsepart>)

LENGTH(<string>)

CONCAT(<string1>, <string2>)

To try and put it into words what we are trying to achieve is :-

Case 1 - If the length of the value in UDA1 is greater than zero and the length of the value in UDA2 is greater than zero then concatenate UDA1 with a comma and UDA2.

e.g. ODI_UDA1, ODI_UDA2 will equal “ODI_UDA1,ODI_UDA2”

CASEWHEN(<condition>,

The condition equals LENGTH(ACCOUNT.UDACOL1)>0 AND LENGTH(ACCOUNT,UDACOL2)>0

So now we have CASEWHEN(LENGTH(ACCOUNT.UDACOL1)>0 AND LENGTH(ACCOUNT,UDACOL2)>>0,

The <truepart> can be expressed by

CONCAT(CONCAT(ACCOUNT.UDACOL1,','), ACCOUNT.UDACOL2),

The <falsepart> is defined by looking at the next possible combination of UDAs.

Case 2 - If the length of the value in UDA1 is equal to zero and the length of the value in UDA2 is greater than zero then use the value in UDA2.

e.g. “<blank>,ODI_UDA2” will equal “ODI_UDA2”

We will need to use CASEWHEN( again there are more possible combinations

<condition> is LENGTH(ACCOUNT.UDACOL1)=0 AND LENGTH(ACCOUNT.UDACOL2)!=0,

<truepart> equals ACCOUNT.UDACOL2

Once again the <falsepart> is defined by looking at the next possible combination of UDAs

Case 3 - If the length of value in UDA1 is greater than zero and the length of the value in UDA2 is zero then use the value in UDA1.

e.g. “ODI_UDA1,” will equal “ODI_UDA1”

Final time to use CASEWHEN(

<condition> is LENGTH(ACCOUNT.UDACOL2)=0 AND LENGTH(ACCOUNT.UDACOL1)!=0

<truepart> equals ACCOUNT.UDACOL1

<falsepart> will be defined by

Case 4 - Don’t use any value, which can easily be represented by ‘’

If we put it all together this produces



And that’s it, this works quite well in situations where you only have a few UDAs to play with though if you have quite a lot I would recommend trying to find an alternative solutions

Next time I will be looking at loading data into essbase using ODI.