The two new areas are the ability to manage smart list metadata and load planning unit hierarchies, I am going to concentrate on smart lists for today and in the next blog have a more in-depth look at planning units hierarchies as these are new feature in planning 11.1.2 due to the overhaul of workflow functionality.
As ODI 11G has recently been released I will be using it in my posts from now, the concepts between 10G and 11G have not radically changed so it shouldn’t matter which release you are using. The new planning functionality exists whether you are using ODI 10G or 11G.
I was considering writing up some posts on ODI 11G but I know it is started to be covered by other fellow bloggers out there so I don’t want to step on anybodies toes, I will see what emerges.
Right on to the new planning ODI functionality, if you perform a model reverse against a 11.1.2 planning application you will see an additional datastore.
HSP_SMARTLISTS is now available as a datastore.
The properties for the datastore columns are :-
SmartList Name, Label, Display Order, Missing Label, Use Data Form Setting all relate to the following screen in planning.
Entry ID, Entry Name, Entry Label relate to the Entries screen in the Smart List creation in planning.
So now we know all the properties we can create the source smart list information to be loaded into planning, for this exercise I am going to create a source csv file that will load Grade information as a new Smart List.
The first record defines the name and properties of the Smart List.
Operation is set to “addsmartlist” so a new smart list is created.
Display order is set to 0, this means the smart list entries in planning will be ordered by the ID.
Missing Label is set to “No Grade”, so any data that is #Missing will be displayed as “No Grade”.
Use Data Form Setting is set to “FALSE”, this means any #Missing data will be displayed by what is defined in the Missing Label above (“No Grade”) and not what is defined in the data form settings.
The rest of the records are the entries for the smart list.
If the entry ID is left blank this is the same as ticking “Automatically generate ID” in planning, so in theory I could leave it blank as planning will order them in the same numeric way.
Now I am not going to go through setting up and reversing the file in ODI because I am assuming that if you are wanting to load smart lists you will already understand the process of what you need to do.
You should end up with something like the above image; the GUI will obviously look a little bit different if you are using ODI 10G
Create your interface, drag the file datastore on to your source and the planning HSP_SMARTLIST datastore on to the target.
Now as smart list data is always pretty small in size I am using the memory engine as the staging area, in 11G the technology name has changed slightly, the old name of “Sunopsis Engine” has been replaced by “In-Memory Engine”, the underlying technology of using HSQLDB is exactly the same.
In the flow area LKM File to SQL and IKM SQL to Hyperion Planning are used, the planning KM seems to be exactly the same in 11G though you will notice the look and feel of the flow diagram has gone through a bit of a make-over.
Looking in the operator the interface was successful though only 1 row was processed successfully and the rest were rejected.
It is a shame that in 11G the planning KM has not been updated to correctly report statistic information, it is a pretty simple update to a KM that I went through how to do a while back.
Looking at the output log all the smart list entries failed and only the smart list creation record was loaded successfully.
So why is this, well lets have a look at what was attempted to be loaded into planning, you can look at the SQL generated by opening the step “Load data into planning” in the operator or you can a very useful new functionality in 11G where you can execute in simulation mode.
This will generate a report of each step that will be executed if run normally.
Unfortunately it doesn’t seem to wrap the text by default so it not the best to demo, you can save out the report to XML or HTML format. This new piece of functionality has been on the wish list for a long time and I am glad it has made it into this release.
As I am using the memory engine as the staging area I can’t simply copy and paste the SQL and run it to see the output, well that is actually a lie, it can be done and you may not be aware how to do it so here is a quick overview.
Update the interface to set the option to false for deleting temporary objects for the “LKM File to SQL”.
Execute the interface using “Local (No Agent)”
Create a new model using “In-Memory Engine” technology and set a logical schema.
In the “Selective Reverse-Engineering” area you should be able to select and reverse engineer the temporary table generated by the interface.
A datastore will be created for the temporary table.
Right click the datastore and choose “Data” and the data in the temporary table is displayed, please note that this data will only persist while it is being held in memory so if you restart the studio/designer don't expect it to be around.
You can then click the SQL icon and paste in the SQL generated by the step in the interface.
Running this will produce the records and order that will be loaded into the planning application.
As you can see the record we want to load first is being loaded last, this means all the records before will fail as the smart list does not yet exist, it would be fine if you run the interface again or create the smart list first but that is not the most sensible way of going about it.
What we need to do is order the records being loaded, I am sure I have gone through this is the past but because I am nice even though I have already spent far too much time writing this blog I will quickly go over it again.
First step I usually create a copy of the KM – “IKM SQL to Hyperion Planning” but for today I am just going to edit the original.
Edit the step :- “Load data into planning” and in the command on target window add :-
<%=odiRef.getOption("ORDER_BY")%>
Save the KM, right click the KM and insert option.
Name the option “ORDER_BY”
Go back to the interface and in the flow area for the IKM you should have a new option called ORDER_BY
Now I want the data in column 2 to be in descending so I enter “ORDER BY 2 DESC”
If you execute the interface again you will notice that “ORDER BY 2 DESC” is appended to the end of the SQL.
If the SQL is executed against the temporary table this time you will see the records are now in the correct order for planning.
None of the records have been rejected this time.
In planning the smart list, properties and entries have been created.
So there we have it, how to load smart lists in planning 11.1.2 with a bit of ODI 11G thrown in for good measure.