Using Lifecycle Management (LCM) with ODI

It has been a while since my last blog, lately I have not had much chance to sit down and dedicate the time required to research and write a detailed blog but here goes.

I have been meaning to write a blog on combining the benefits of LCM (Hyperion version 11) and ODI for a while now, the beauty of LCM is that you can automate loading and extracting of objects from most aspects of the EPM suite of products, this gave me an idea of how to incorporate this with ODI.

If you take for example the loading of cell text into a planning application, in the past this has always been a pain but with LCM you can load cell text easily, say you have a csv template with the cell text and you wish to automate the process of loading this into a planning application, if you want to use LCM then you would have to format it into the correct XML file format. I want to go down the route of just supplying the csv file and not mess around with having to format it into XML; this is where ODI comes into play.

I am going to break this down into sections and provide the usual step by step process, this example is just looking at cell text but as LCM uses the same principle you could easily use the concept for extracting/loading other information.

I am going to use everybody’s favorite sample planning application to add a line of cell text.



We now need to use LCM to export cell text to have a look at the output XML file, this is done by logging into Shared Services, expand Application Groups and then the group you hold the planning application against which in my case is called Planning, select the planning application and this will open the LCM artifact list, expand relational data and select “Cell Texts”



Define the migration and set the destination as file and give it a name.



Once this has been executed it will create an xml file on the server, usually in the directory \hyperion\common\import_export\<account migration run against>\<migration name>\....



If you open the xml file you will see the format LCM uses.



Now what I want to do is provide the following csv file.



Then automatically log it into the sample planning application.

ODI is pretty good at handling XML formats and I have touched on using it in the past, it has the ability to reverse engineer an xml file and generate Datastores based on the xml structure, you can then load information from the file or generate a fresh populated xml file.

The first thing to set up is the connection details to the XML file in the topology manager.

Right click XML and “Insert Data Server”



Within the JDBC section select the “Sunopsis JDBC Driver for XML” driver



In the URL you need to provide details to the XML file, the outputted xml file from LCM was named “Cell Texts.xml”, I moved this file and renamed it to remove the space in the filename, I never like having spaces in file names as it can sometimes cause problems.



The format to point to a file is :- jdbc:snps:xml?f=
You can use the “Test” button to make sure ODI can connect to the file.

By default ODI uses its memory engine to store the relational schema mapping of the XML schema, this is fine in most cases though if you had a large complex xml file then it could run into problems, it is also possible to store this information in an external database, I am going to go down the database route as it is easier to show what is going on behind the scenes.

To use an external database you can specify all the parameters in a properties file, an example of a properties file is in \OraHome_1\oracledi\demo\xml\properties, the properties file will need to exist in a location that ODI can reference so if you are using a local agent then it will be fine in the drivers directory, if you are using an agent service in windows then you will have to update the wrapper configuration file which I will go through shortly.



Basically you can use the four parameters I have highlighted as these are mandatory, you will just need to update the driver/url to match your environment, the schema relates to the Oracle schema which is the same as the username, the password can be encoded by running the following command from the oracle\drivers directory.

java -cp snpsxmlo.jar com.sunopsis.jdbc.driver.xml.SnpsXmlEncoder password

If you want to find descriptions for each of the parameters it is available in the documentation: - OraHome_1\oracledi\doc\webhelp\xml\index.htm

To reference the properties file the filename minus the .properties needs to be added to the url in the data server configuration, the parameter is dp_props



Once you apply the Data Server settings a physical schema window will open and you should be able to select schemas from the dropdown.



Then it is just a matter of applying it to a context and giving it a logical schema name.

As I said earlier the properties file needs to be in a location that ODI can reference e.g. \oracledi\drivers which is fine if you are using a local agent, I am using an agent created as a window service so the file OraHome_1\oracledi\tools\wrapper\conf\snpsagent.conf requires an extra line in the classpath reference



Once the file has been updated the agent windows service requires restarting to pick up the reference to the file.

This completes the configuration and we can move on to the designer to reverse the xml file into a model.



Create a new model, set the technology as XML and pick the logical schema that was created.



In the Reverse section just select the correct context and you are ready to reverse.



The structure of the XML file will be converted into DataStores, you can view how each DataStore relates to each other by expanding Hierarchy.

If you have a look in the repository you will see a number of tables will have been created, most of them relating to the DataStores



The table SNPSRDBIDGEN holds all the table names



The other tables relate to the different elements of XML file, for instance DIMMAPPING holds all the dimension mapping information, if you look at the XML file and the database table you will get an understanding of how it hangs together.



The above table we don’t really have to mess around with as it is going to stay static, the table that we are interested is CELLT_CELLNOTE as this holds the cell text information.

The XML file has the following information



The database table contains



Most of the information is self-explanatory, the extra information are just IDs and they link to the other tables, if you are loading information against one plan type then the IDs will stay pretty static and the only information that needs updating is the dimension intersection for the cell text and the actual cell text value.

If you need to get a better feel of how the tables the tables are populated it might be worth entering more cell text and against different plan types then follow the LCM process of exporting to XML, once you have the XML file in place it is possible to use a command to read the file and load it straight into the repository tables.

This is done by creating an ODI procedure.



Executing the procedure will load the information from the XML file directly into the repository tables.

So basically what I need to do is populate the CELLT_CELLNOTE table with the information from the csv file that holds the cell text information, to achieve this it can be done with a simple interface file to SQL.

I am not going to go over how to set up the file information in ODI as it has been covered in the past, if you are unclear read back into the early days of my blog, quick overview:- set up File Data Server in the topology manager or use an existing one, create a new DataStore against a File Model, reverse the DataStore and you should end up with something like :-



The table CELLT_CELLNOTE will also need to be reversed into a DataStore.



Ok now to create an interface to load the CSV information into the database table.



Mapping the interface is just a matter of comparing the records in the db table CELLT_CELLNOTE and assigning against a dimension in the source. e.g. DIM1 has Actual as the value so this maps to Scenario.

After the dimension columns have been mapped it is just a case of hard coding the numeric values so for instance DIM1ORDER is 0 in the db table and it will stay static so it can be hard coded with that value in the target, this is the case for all the other ORDER columns, the only element that could possible change is the PLANTYPEFK but as in this example it is against one plan type it will always be a static value of 0.



In the flow option truncate is used to clear down the table before loading the new records.



After executing the interface you can see the records from the CSV file have been populated into the CELLT_CELLNOTE database table.

Ok, so now we have the values we want in the cell note table and all the other tables are static so don’t need touching, what we need to do now is recreate the XML file with the new information, this can be done again using an ODI procedure.



This time the command SYNCHRONIZE FROM DATABASE is used which basically dumps the information from the database tables and recreates the XML file based on the information



The XML file has been recreated and populated with the information from the original CSV file, now all that is left is to use LCM again to load it back into planning.

I am going to manually do it for today so that means renaming the xml file back to “Cell Texts.xml” and moving it back to the LCM file directory



In Shared Services it is just a matter of selecting the same migration name under Application Groups > File System



Run through the options and execute the migration.



And there you have it! Cell text that originated from a simple csv file format loaded straight into planning, this concept can be used for any of the areas of LCM you are not restricted just to thinking about cell text.

I must point out if you are going to follow this example use “NoSegment” and not “No Segment” as I have shown as it will fail because you can’t use aliases when using LCM it needs to be the member name, I am too lazy to go back and change the images.

Next time I will go through automating this process and touch on using the LCM API via ODI to execute the LCM import.

I hope you have found this useful, until next time!!