If you don’t use ODI within your company then I wouldn’t start to promote using it just for the sole purpose of loading planning unit hierarchies, one of the other methods I have been through would suite you more.
Today I am going to give a simple example scenario to hopefully make it more interesting and take you through the steps of achieving it.
Here is the brief, you have been set a task of automating the management of a planning hierarchy, a business user will provide a flat file to a shared drive, this file will provide most of the information except the user will only supply the alias names for the primary members, as all the methods for loading planning hierarchies do not accept aliases the member name will need to retrieved from planning.
Once the flat file has been placed in the designated location the process should automatically start, once completed an email should be sent out to the business user attaching any errors from the planning unit hierarchy load.
So lets go through the process in trying to complete the given task. The concept of loading the planning unit hierarchies is the same as the other methods, I am going to assume you have a basic understanding of using ODI and planning, if you don’t there are plenty of blogs I have written in the past of you can have a read of.
I am will be using ODI11G but it is all achievable using ODI10G.
First of all you have to make sure a default planning hierarchy has been created in planning.
Once you have created the hierarchy, in ODI you can reverse engineer your planning application.
If you take a look at the reversed model you will notice a Datastore has been created with the same name as the planning unit hierarchy.
Within the Datastore the columns that have been generated are exactly the same as the columns that have been used in all the previous methods (you will need to go back to the first part if you want to understand what each of the column means and how it relates to the planning unit hierarchy)
The user has produced the following planning hierarchy flat file to be uploaded to planning :-
The primary members are all alias names.
Next step is to reverse engineer the flat file, remember that the agent you run the integration against will need to be able to access the file, otherwise you will need to look at alternatives of accessing the file such as one of the ODI ftp tools.
Remember that you can’t reverse engineer flat files through the model; you need to create a new flat file Datastore and reverse it within the columns tab.
As the source file only has alias names we will have to join it to the planning applications underlying tables to be able to retrieve the member name, this means the next stage is to create a connection to the planning application relational database in the topology manager, create a model and reverse the required tables.
The only tables we are interested in and need reversing are HSP_ALIAS and HSP_OBJECT
Now that the source and targets are in place we can move on to creating the interface to load the planning unit hierarchy.
As the target technology is going to be planning and it has no JDBC capabilities a staging area has to be selected.
The planning unit Datastore was dragged on to the target.
The planning unit flat file datastore was dragged on to the source then the planning application tables HSP_OBJECT, HSP_ALIAS and again HSP_OBJECT.
The HSP_OBJECT table is the core table in the planning application and contains the member names and alias names that we are interested in. The Planning unit hierarchy Datastore joins to the HSP_OBJECT DataStore, the join is
PLANNING_UNIT_HIER.Primary_Member > HSP_OBJECT.OBJECT_NAME
The HSP_ALIAS table contains IDs for the member name and alias and these IDs join to the HSP_OBJECT table.
HSP_OBJECT.OBJECT_ID > HSP_ALIAS.ALIAS_ID
HSP_ALIAS.MEMBER_ID > HSP_OBJECT.OBJECT_ID
The joins between the Datastores were created by dragging the required columns from one Datastore to another.
A filter was added to HSP_OBJECT_OBJECT_TYPE with a value of 10, this means it will only return records that are alias related. If you want to understand the numbers that relate to object types then you can view them in table HSP_OBJECT_TYPE.
The target Datastore columns were then mapped to the sources.
Basically the flat file will join by alias to the planning tables to return a member name that relates to the alias.
In the flow diagram "LKM File to SQL" was used to load the Planning unit hierarchy flat file to the Staging area. The "IKM SQL to Hyperion Planning" was used to load from the Staging area to Planning.
In the IKM option both logs were enabled and a location and filename provided, the logs will be used in the final stage of the integration when the completion email is sent out.
Now that we have the main part of the integration built we can put it all together in a package.
The first step is to use the OdiFileWait tool, this will wait for the planning hierarchy flat file, it will check every 60 seconds and once the file exists in the specified location it will move on to the next step.
The next step is the interface that loads the planning unit hierarchy into planning.
And finally the step to send out the email confirming the completion of the planning unit hierarchy load and attaching the error log file generated.
Obviously you can make it a much more sophisticated package with failure handling, file archiving and executing the scenario once the package has completed but you get the idea.