ODI Series Part 5 - SQL to Planning

The ODI series continues, we are already up to part 5 and still lots to cover. In the last part we looked at loading hierarchies into planning from flat files but there are many occasions when the metadata can be sourced from database warehouses. Today I am going to cover loading metadata directly from db tables into planning.

In part 3 I set up a connection to SQL server and to a database named hypdata, if your warehouse is Oracle then it is pretty much the same set up just using different JDBC drivers.

For this exercise I have set up two simple db tables one containing the parent/child entity information and the other containing the member properties.





Now there are a few methods of getting them loaded into planning, the first and simplest is to create a view in SQL server or Oracle to join the tables by the child/members fields.



I need to insert a new model in the ODI designer to point the SQL server technology.



You have the option of manually inserting the datastores to point to the db tables/views or you can reverse everything from the Model Reverse tab.



For some reason it didn’t reverse the View so I just checked View, unchecked Table and reversed again, this time it reversed the view.



Ok nice and easy, we just need to create an interface to load the data from the view into the planning entity dimension.

Just before we do this we will need an extra Knowledge Module, we will need a LKM (Loading KM), we previously used “LKM File to SQL” but this time we are loading using SQL.

Import KM from the Project window in the Designer and choose “LKM SQL to SQL”



And it should appear in your LKM list in the designer.

Insert a new interface



Name it; set the context and the staging area, which once again we going to be using the “Sunopsis memory engine”.



In the Diagram drag the View as the source and the planning entity dimension as the target, map the fields and you should end up with something like the image above.

In the HIER_MEMBERS table and HIER_VIEW view there is a dimension field, currently there is only Entity information in there but say we started adding extra dimension member information then the above interface would try and load data that is not required for entities.

This can be easily resolved without having to go back and change the view and to be honest changing the view wouldn’t be a good idea because we would end up with a view for each dimension.

This is where filters come into play, in the diagram source window drag the dimension field off the window and this will create a filter, then you just need to set the filter to value in our case it will be entities.



To check the filter is correct you can click the “Check the expression in the DBMS” button that is on the right hand side of the implementation window. You can also create filters on the view in the model tab.

Your flow diagram should look like this.



I manually added information for the log details in the Options window, this time I have also set the REFRESH_DATABASE to Yes so after the metadata has been loaded into planning it will push it down to essbase.



Executing the interface produced no errors in the log and opening the outline in EAS displayed the new members so the refresh worked fine.

As I said early there were a few methods of loading from two tables one being with the use of a view, now say you didn’t have access to the SQL repository to create a view or for some reason it wasn’t deemed the right solution then you can achieve the same results by creating the join from within ODI.

To achieve this create a new interface and fill in the definition tab in the same way as before, in the diagram tab drag and drop both datastores HIER_MEMBERS & HIER_PROPERTIES into the source window.

Now in the source window drag CHILD from HIER_MEMBER onto MEMBER in HIER_PROPERTIES, this will create a join between the datastores. You can then just drag the planning entity dimension into the target and map all the fields.




And the final flow diagram should look like



I know this is quite a simple example but hopefully it does start to show what can be done with ODI and generate a few ideas how you can incorporate it into your own solutions.

I am going to leave it there for Planning and next time I will move on to using ODI with Essbase, I will start to look at reverse engineering a database outline and output to different formats.