ODI - Question on loading planning Smart List data directly into essbase

I am writing this blog as there is a unresolved question on otn planning forum, it is one of those questions where it is probably easier to go through the steps than try to explain in the forum. It might also be also be useful for anybody else that has the same sort of requirement in the future.

(I would just like to point out it is possible to load smart list data through the planning IKM though this does require updating the data load driver information in planning, you can read about loading data through the planning layer here in a previous blog)

The question relates to loading data directly into essbase where the source data is a mixture of numerical values and smart list names, not something I encounter very often and I wouldn’t thought you would mix that sort of data in a warehouse but sometimes it is best not to question the logic.

Now as you are probably aware that smart lists are loaded into essbase in numerical format, when you create a smart list it displays the ID that will be used, this means the data will need to be transformed from text to numerical when it is loaded into essbase.



I am not sure what the data looks like from the original question but I just going to base it around the planning sample application again. The aim is to populate the above composite form with data that contains a mixture of numerical and smart lists values.



What you need to be aware of is the data column is non-numerical and the destination essbase data column is numerical so there will be a requirement in the integration to change data types.

The first steps are to reverse the data and essbase database, no need to cover that off as I am sure you are very familiar with it by now.

Now we need to be able to map the data with the smart list values and to do this you need to access the planning applications database tables. Smart List information is stored in two tables

HSP_ENUMERATION


This table stores the information for the smart list properties.



HSP_ENUMERATION_ENTRY


This table stores the details of the smart list entries


The column ENUMERATION_ID links the tables; both the tables will need to be reversed unless you always know the enumeration id and in that case you will just need to reverse the HSP_ENUMERATION_ENTRY table.



In this example I am loading directly into essbase but before I do this I usually create a text file or db table to load the data into, this is just because I find it easier to sense check the data that is going to be loaded first. In this case I just created a near duplicate of the source data table with the exception of having a few extra columns that are not in the source table but are in the essbase target database.



The additional columns are HSP_Rates and Currency; I also created the Data field as numeric to mirror that is of the essbase data column, this table was then reversed in ODI.

Time to create the interface, now this can be done a number of different ways. There are three source DataStores we need to use though all three can not just be dragged on to the source, this is because the join that will be generated will not give the required results. I did try to use all 3 tables joined at the source but even with changing the ordered join numbers I couldn’t get the correct SQL generated, it probably can be done but I didn’t have the time to spend on it and want to keep this as simple as possible.

Ok, I will go through one of the ways it can be done, the Data was dragged on to the source and the HSP_ENUMERATION_ENTRY DataStore



A join was created between the SL_DATA DataStore and the HSP_ENUMERATION_ENTRY DataStore.



The join in its current state is not enough because it will only return the records where the data matches against the smart list name, in the source data there is the numerical values we want to return as well.



By selecting “Left Outer Join” the join is updated to return records in the source datastore where there is no match, which is what we want to achieve.

Though this will look at all Smart List Entry records and we only require a subset, this is where the HSP_ENUMERATION DataStore comes into play, as I stated before if you just drag the DataStore on to the source and join HSP_ENUMERATION_ENTRY and HSP_ENUMERATION then the SQL generated when the interface is executed does not give the desired results.

What I did was update the expression in the join between SL_DATA and HSP_ENUMERATION



So now it will only return the ID of the records where the name of the Smart List is defined as ‘Make’.



I also updated the expression to use an ODI substitution method that returns the full table name instead of hard coding it.

<%=odiRef.getObjectName("L", "HSP_ENUMERATION", "PLANSAMPSQLSCHEMA", "D")%>

The statement is saying return the object name for HSP_ENUMERATION in the logical schema PLANSAMPSQLSCHEMA, which will return PLANSAMP.dbo.HSP_ENUMERATION.

The data check table was dragged on to the target.



The two columns that are not mapped I need to put against members “Local” for currency and “HSP_InputValue” this is the member where planning stores its inputs against for currency applications.
If it is a non-currency planning application then you don’t need to worry about these columns.



The Data mapping has to be updated because it has defaulted to retrieving all the records from the source data table and what is required is a mixture, an expression has to be created to achieve this.



This is basically saying when there is no entry id i.e. there is no match between the source data and the smart list data then just return the source data otherwise return the smart list entry id.



I used the “IKM SQL Control Append”, set “DELETE_ALL” to yes as I want to clear out the source table each time I execute the interface and turned off “Flow Control” as I am not trying to control or check the data.



Executing the interface generates the desired results as the numerical data has been loaded and the smart list data has been converted to the correct numerical id.

As the results were correct the interface was updated by dragging the essbase data Datastore on to the target, the staging area had to be updated so it was done on the SQL database side, as essbase does not have the capabilities to transform the data.



If the interface is run as it currently stands it will generate an error.



I mentioned earlier that a conversion of the data type would be required; this is because the source data column is non-numeric and the target column is numeric.



Depending on what technology you are using will depend on how you update the expression, I was using SQL server so used the CAST function; if you are using Oracle then you could use the TO_NUMBER function.



Once the interface has been executed and loaded the data into essbase you can see the desired results are available in the planning form.

In the next blog I am going to go through exactly the same example but load data through the planning layer using "IKM SQL to Planning" then it is up to you which method you decide on using.