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>)
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”
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
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.
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.