ODI Series - Putting it all together

In one of my last blogs I ended up with a couple of interfaces which I said could be automated with the use of the package, today I am going to go over the basics of packages and bring variables into the equation.

A package lets you execute a sequence of steps in an organised diagram, the steps can be :-
Executing an interface
Executing a procedure
Setting, declaring, evaluating or refreshing a variable,
Executing ODI tools, there are a number of tools provided which perform specific tasks at run-time. These tasks can be such as waiting for data to arrive, ftp’ing information or sending emails. There is a full pdf document available describing each tool and its parameters which can be downloaded from here

Say you have a situation where you receive files on a daily basis, when the files arrive this this information needs to be loaded into an essbase cube, once completed an area of metadata has to be extracted from the cube loaded into planning, planning refreshed, files archived and finally send an email informing that the process has been completed, this can achieved quite easily using a package and I am go through the steps to automate this scenario.

When you create a new package you start with a blank diagram.



Before I start adding objects to the diagram I am going to create some variables. A variable is an object that stores a single value. This value can be a string, a number or a date.
The value is stored in ODI, and can be updated at run-time.

I am going to create a variable that will store the location of the archive directory, a variable that will store the log directory and a variable that hold the current date.

To create a variable it is simply down by right clicking variable in the designer and selecting insert variable.



Datatypes : Alphanumeric (255 characters), Date, Numeric (Maximum 10 digits) or Text (unlimited length).

Action : This parameter shows the length of time the value of a variable is kept for:

Non-persistent : The value of the variable is kept in memory for a whole session.
Last value : ODI stores in its repository the latest value held by the variable.
Historize : ODI keeps a history of all the values held by this variable.

I created another variable just like the one above for the log directory.

To create the variable to hold the date it is exactly the same process, except you need to enter information into the refreshing tab, when you refresh a variable in the package it will perform a query based on what has been entered and the schema chosen.

In the schema dropdown I selected a schema that I had set up in the topology manager to look at SQL Server, I have chosen this, as I want to run a query against SQL server to return the current date.



The SQL to return the date is :- SELECT CONVERT(VARCHAR(10),GETDATE(),105)

This will return the current date using the function GETDATE() and format it using the CONVERT function.

If you are using Oracle then you can use "SELECT sysdate FROM dual" and use something like TO_CHAR to format it.



You can use the refreshing option in various different ways for example you could have values stored in a field in a database table and set the variable value by retrieving the information from the field.

When using variables in a package you declare them and then you can use them as an option value in a KM.
So if you open up one of the interfaces that is going to be used in the package, you use "#variable name", as shown below where I have used the LogDirectory variable.



Once the variable has been declared in the package then it will be passed into the interface, please note though this will not work if you just run the interface out of the package.

Right back to the package, the first step in our scenario is to wait for a for a file, to do this then one of the ODI tools is required – OdiFileWait



All the parameters are explained in full detail in the ODI tools documentation.
The settings that I used:-
Action - Move – this will mean when the file arrives it will be moved.
Filename Mask – any files starting measures*.csv will be checked for.
Target Directory – the file will be moved to this directory.
Target File – the file will be renamed to Measures.csv to align with the naming in the interface.
Overwrite Files – if the file already exists in the target directory it will be overwritten.
Timeout - 0 – This is the time to check for the file, 0 means an infinite time.
Polling Interval – time in milliseconds to check for the file.

It is possible to use variables for any of the parameters, all depends which settings are likely to change and can be controlled easier with the use of variables.

The next stage would be to run the interface that loads the measures into the essbase cube, as the interface uses the log directory variable it will need to be declared first, to do this you drag the variable on to the diagram and set the type to “Declare Variable”.



To create the process between the steps is done in the advanced section, it gives the ability to set what step to perform next depending if the current step is successful or fails.



The interface can be dragged onto the diagram, if the interface fails then I want the process to send a failure email and then stop, this can be achieved by using another tool – OdiSendMail.



If the process fails an email will be sent with the interface log files attached, the variable #LogDirectory will be converted into the correct stored value.

After the essbase load process has completed the next step is to add the interfaces that extract the metadata from essbase and load it into planning, I am using the interfaces from a previous blog.



Now if any of the newly added interfaces fail you could add another email failure object but this can get messy and unmanageable in the end, one way of streamlining could be the use of variables.

If you create a new variable and set it “Not Persistent” as the value is only important in the current session, in the refreshing section we can use one of the ODI API functions <%=odiRef.getPrevStepLog("STEP_NAME")%>, this will retrieve the step name from where the error occurred, you still need to use it as a query so we end up with :-



If you are using Oracle then the query would be :- SELECT '<%=odiRef.getPrevStepLog("MESSAGE")%>' FROM DUAL

In the email body we can use the variable to highlight where the step failed.



In the diagram at each point of failure the process can be updated.



This means whenever a process fails it will refresh the variable with the step name and place that in the body of the email.



Ok, say all the processes have completed we want to archive the metadata file with the current date on it.

First we need to use the date variable and refresh it.



The archive directory value also needs to be dragged onto the diagram and then the use of another ODI tool called OdiFileMove is required.



This will take the file Measures.csv from the E:\FileStore directory (most of the time I would of used a variable for the file store directory and used it throughout) and move it into the archive directory renaming it to Measures(current date).csv e.g. Measures01-01-09.csv

Finally you could use the tool OdiZip to compress the file and then send out the success email.



Another useful function of variables is the ability to evaluate them and execute a step depending on the Boolean result, for example say you have a daily and weekly process, if a variable is set to weekly it would run the weekly process otherwise run the daily process.



You can also use a variable in a DataStore, for example if have a file DataStore and use it in an interface to create an output text file you could use the Select date variable to always output the file with the current date on it.



As long as you set the variable in the package you will be able to pass it to the DataStore.

There are many possibilities with using variables in your packages and with all the provided ODI tools you can be build up complex packages. I only just touched the surface today to give a start to using them and will hopefully give you ideas to achieve your integrations.

Next I will move on to running integrations over the web and putting the invocation API to good use.