Reversing XML file in ODI

"XML files can be used as tables, yes, that is an old story.
But have we tried doing that in ODI ? no, not until now"

Probably the best use of xml files are that they can be used to store relational table information. I tried reversing xml file which is basically a table in ODI and thus this blog post was born.

Tables in xml format have two files associated with them

• The data contained in the table is stored in the .xml file
• The metadata or column information of that table is contained in another file with the extension '.xsd' or '.dtd'

More information about how .xsd and .xml are created can be found here.

http://www.w3schools.com/schema/schema_example.asp
I will be using a simple xml file which is basically stolen from the internet, here are the contents of the file (note that it is only half of the picture that I have posted, you will have to complete the elements 'book' and 'catalog')

What this file above tells is that there is some table which contains information about books and we need to represent the same in ODI. Lets proceed.

Step 1:

Creating Data Server and Physical schema:

When specifying the JDBC url, we need to mention three things which are required for a successful reverse.
1. xml file name and location
2. dtd or xsd file name and location
3. Schema which will represent the table

In the pciture above I havent mentioned Schema yet (I will come to that later). And where did I get the book.dtd file from ? Well, ODI automatically creates a dtd file (if not specified) when you reverse the model.
All you have to do is specify only the f=../**.xml parameter initially, then create a model in designer and reverse it, this will only generate the dtd file and wont give the tables. You have to come back to topology manager and specify the details of dtd with the d=../**.dtd file

The Schema:
As ODI always does things in a proper DBMS way, it always needs a schema , that is the place holder of all tables, to be specified. When creating a Physical schema after this step, You will see that ODI has automatically created a schema as BOOK based on the DataServer name 'BOOK_TRIAL'

Now, go back to the DataServer details and enter the schema parameter like this

Test the connection after saving, if all goes well, its time to switch to the designer window

Step 2:

Creating Model :

Just like we create a model for any other source, create a model ... when reversing, use Standard reverse and and go to Selective Reverse option and check 'Objects to reverse engineer', it will give you the details of tables available

Check the desired tables and click reverse engineer button, you will soon see the two tables created under that Model

Voila !!! as you can see the contents of BOOK table are those that I showed in the xml file.
Best of luck with your integration !!!

Next, I am going to try converting Relational tables to xml using ODI. Will keep everyone posted

Get used to Calc Manager !!!

While working on preparing a training plan of Hyperion Planning 11.1.2.2, the very first question came to my mind "I don't know how to use the calc manager yet, how do I write rules in Planning now ??"

Since Business Rule component has been dumped completely by EPM system, people will now have to use Calc Manager only, even in Classic Planning Applications.
The most hilarious workaround people use is - build the rule in EAS then copy the script and paste it in Calc manager window. Though it works all the time, having tried a hand at the graphical interface made things even simple !!!

Getting my first tutorial on using the graphical interface from this link

http://ranzal.wordpress.com/2010/06/11/oracle-hyperion-calc-manager-%E2%80%93-part-1/

try it ... its very simple !

Direct Data Load - using outlineload utility to load data

Although I am not sure if this was available in Planning 11.1.2.1, but Hyperion Planning 11.1.2.2's outlineload utility does come with a functionality of loading data (You read it right !!)

There are two options Planning provides :
1. Either use the .csv data file to load the data using Outlineload.sh, this means that data will be loaded immediately and Administrators will have to ensure that Planning and Essbase metadata are in sync (refresh has been done)
2. Use the .csv file along with the utility which will create a .txt file of the same and a .rul (rule) file which can be loaded using the Essbase administration console. This means that you can create a data file when you have it ready and load it when the database has been refreshed at a fixed interval.

Needless to say, you can automate the whole damn thing !!!

Some basic points you should remember when using the data load utility

DIRECT_DATA_LOAD : This is the parameter you can either set to TRUE or FALSE in the System Properties of the application.
if TRUE :  .csv file is loaded directly, see option 1 written above
if FALSE : .csv file is converted to a text file and a corresponding rule file is created, the path where these two files are created is specified in the DATA_LOAD_FILE_PATH system property

DATA_LOAD_FILE_PATH  : This is the file location you need to specify if you choose set  DIRECT_DATA_LOAD to false


The csv file format : Imagine pushing data in Essbase using Excel addin having a Rows of a Dimension, Columns of another dimension and Point of view dimensions above it, the header of csv would be

DimensionInRows,MembersInColumnsSeparatedByComma,Point-of-View,Data Load Cube Name

Account,Jan,Point-of-View,Data Load Cube Name
acct1,12,”Local,ent1,Current,Ver1,FY08”,Plan1

or you can specify the Operation and data load cube name by putting it in between the first two column header

DimensionInRows,Operation,Data Load Cube Name, MemberNamesInColumns,Point-of-View

Entity,Operation,Data Load Cube Name,aUnspec,aSmart,aDate,aText,Point-of-View
e1, ,Plan1,77,smart1,12-22-2008,textValue,"USD,Jan,Current, BUVersion_1, FY07"

If you dont want the 'row' column at all, like a simple 1 dimensional table where you have 'value' column you will have to specify the /TR option in the OutlineLoad command, the Header would then be

Value,Driver Member,Point-of-View,Data Load Cube Name

Value,Driver Member,Point-of-View,Data Load Cube Name
14,a1,"Jan,Local,e1,Current,Version1,FY08",Plan1
sl1_value2,a2,"Jan,Local,e1,Current,Version1,FY08",Plan1 

and the command would be 

OutlineLoad /A:acpt1 /U:admin /M /I:c:\outline1data.csv /TR /L:c:/OutlineLogs/outlineLoad.log /X:c:/OutlineLogs/outlineLoad.exc 

 Note how PoV members are enclosed in double quotes and use exact same syntax when creating the file

How to do ??
For policy reasons, can't put the screenshots :(
Will see if I can do it locally on another machine.

For now I will paste the reference link
Search 'Loading Data' in it
http://docs.oracle.com/cd/E17236_01/epm.1112/hp_admin_11122/frameset.htm?launch.html.

Cheers !!