I expected the planning adaptors for ODI to equal the functionality of the outline loader utility in version 11 but sadly this was not to be, the adaptors were more in line with HAL where you could only load metadata information for attributes, account, entity and custom dimensions.
In case you were not aware in release 11.1.1.3 the new functionality has now been included for use with ODI, I am not sure if there any mention of in the documentation, though I may have missed it. You don’t even have to update any of the planning ODI adaptors as the functionality has been added to the core planning Java classes.
Prior to release 11.1.1.3 if you reversed a planning application (I am using the sample planning app as an example) then you would end up with a model similar to :-
Repeat the same reverse on 11.1.1.3 then the model will look like :-
This means you can now use ODI to maintain all your dimensions in a planning application; all you need is the starting shell of a planning app.
If you maintain multi-currency applications then the added functionality of being able to manage FX rate information through ODI is a huge bonus, I know in the past I had to build a custom application to upload FX data from excel to the planning repository tables, the client required to populate 60 years of FX data and the usual method of entering it through planning web was just not feasible, the ability to use ODI would have been much more of a robust solution.
Expanding the HSP_FX_RATES Datastore shows that you can pretty much do everything in ODI that you can through planning web, I will let you decide which method is going to be more efficient in the long run but I know where my money is on.
While we are on the subject I might as well take you through how to use it, now as ever your sources could be flat files, database tables or even pulled in via a web service, the data would not have to come in the exact format required by ODI, that is where the power of transforming data in ODI could be used. In this example I just going to use a simple formatted csv file.
I am going to populate the equivalent of above but using a csv file, I will let you type it manually and I will create the integration and lets see who is the quickest ☺
Ok, the CSV file contains small meaningless data but this is just a simple example,
First create and reverse the file Datastore.
Create the interface; drag the csv Datastore to the source and the HSP_FX_RATES DataStore to the target.
I manually added in the Table name and To_Currency to the target mapping but this could easily be picked up from a file or populated using ODI variables.
If the exchange rate table does not exist then it is created for you so even less manual intervention, the only issue I could see was if you use the Description column as it didn’t seem to populate planning, I don’t see this a big deal though and probably will be fixed in the future. If you are really fussed about the description just create the table first in planning web.
Are you still manually entering as I am finished ☺
This is a really beneficial new feature and with the power of ODI you can now provide a worthy solution for managing planning exchange rate data.
In the next blog I am going to continue with the new functionality and provide a solution to a question that has been asked many times in the past.
Stay Tuned!!!