ODI Series - Loading Smart List data into planning

Last time I went through how to load planning smart list data straight into essbase, it is my preferred method due its flexibility but it is not the official way of doing it. Just to make sure there is an even balance I will go through exactly the same example but use the planning layer and KM to load this information.



So we have exactly the same set of data but the first thing we need to decide is how we are going to load, when loading data through the planning layer you have to decide on a “Data Load Dimension” and a “Driver Dimension” with a list of members.

If you are going to be loading smart list data then the dimension that holds the smart list information has to be the driver dimensions and the smart list members have to be the selected members.

This means the “Driver Dimension” is going to be the account dimension as these contain the members with the smart lists.

The Data Load dimension is limited as it cannot be the Year, Period, Scenario, Version or Currency dimensions, so as I am using the Sample application this leaves the Account, Entity and Segments dimensions, the Account dimension is going to be the Driver Dimension so it can only be the Entity or Segments dimensions. In my example either will do so I have chosen the Segments dimension.

With the decisions been made the next step is to set up the data load information in planning, unfortunately at the moment this is a manual process, one of the reasons this is not my preferred choice because when it comes to automation the last thing you want to do is start having to manual select information.

I know that the outline loader that is part of the planning utilities in Version 11 you can set up the data load information from a flat file, hopefully in the future the ODI KM API will be updated so it can be set through ODI.



This is accessed from Planning > Administration > Data Load Administration.

Select the Data Load Dimension and click Go.



This will give a list of available Driver dimensions from the drop down.



The list of members you are going to load data for with the driver dimension have to be manually selected, luckily in my example I am just going to be loading against two account members, lots of fun if you are loading data for many large number of members.

Once you click “Save” it will update the planning database table with the information, if you are interested it populates table “HSP_DRIVER_MEMBER”, this table is cached by planning so you can’t just update it with information to try and automate the process.

The planning application will have to be reversed again in ODI, this is because there are currently no columns available to load data and driver information.



The reversing will create extra columns in the “Data Load Dimension”, these are “Data Load Cube Name” which relates to the essbase database you are going to load the data into, “Point-of-View” this is used to hold a comma separated list of the remaining dimensions, finally columns for each Driver member so in my example members “MakeDriver” and “Unit Sales”.
Remember if you add any extra driver members and want to load data against them you will have to repeat this process.

The interface can now be created as we have the source and target information ready.



The Segments dimension is directly mapped, as this is the “Data Load Dimension”. The “Data Load Cube Name” is mapped to ‘Consol’ as this is the name of the plan type (essbase database).



The remaining dimensions are put against the “Point-of-View” column, these have to be comma separated, in this example I am using SQL Server as the staging area so I am using the + operator to concatenate, if you are using Oracle you can use || to concatenate. It is also possible to use the Concat function in the expression editor.

As there is only one column for the Data in the source and the target requires being split into a column for each driver member then an expression can be used on each of the driver member columns.



The expression is basically saying when the account member being loaded equals “MakeDriver” then use the value in the Data field.
The same expression was used in the mapping of “Unit Sales” and updating it from “MakeDriver” to “Unit Sales”

Running the interface will populate the essbase database with the correct smart list id value and data value. It is your choice which method you choose, if your data is not going to change much and you are happy with the configuration then this method is perfectly acceptable, if your metadata changes frequently and there are additional smart list members then it will require extra manual changes.