Monday Apr 20, 2009

Using Variables in ODI: Creating a Loop in a Package

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Project, Interface and Package are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

In our last post in this series, we looked into passing parameters to dynamically set the name of the file that we want to process. An alternative may be to parse a list of file names that would be stored in a table in your database and to loop over this list to process all files.

To perform this, we will need to perform the following operations:
- Create a variable to store the file name, and a second variable that we will use as a cursor to point in our table
- Define a package and use the variables to loop over the different values in the table

If you need help with the creation of variables please refer to our post on the usage of variables in ODI Interfaces and Packages.

1. DEFINITION OF THE VARIABLES

1.1 The Counter Variable

We will start here with the definition of a variable that we will use to loop through your list of files. Let’s call it Counter. I usually use an alphanumeric variable and define the SQL query as being

select #Counter+1 from dual

(Note: the code would vary for non Oracle databases). I find alphanumeric variables easier to process than using numerics as numerics come with decimal values and may require conversions based on where and how you use them. One advantage of numeric variables though is that you can use ODI to increment their values when you select the Assign action on the variable.

Create Counter Variable Counter Variable Query


1.2 The FileName Variable

For this example, we assume that the file names are stored into a table called ODI_FILES_TABLE. This table has only one column with the file names, called FILE_NAME. And we store the table in an Oracle database. You can easily adapt the code for different table structures or databases. Here we will parse the table taking advantage of the ROWNUM returned by the Oracle database.

Create a Variable in the same project, and call it FileName. Define the following query for the FileName variable:


select FILE_NAME
from (select FILE_NAME, ROWNUM COUNTER from ODI_FILES_TABLE)
where COUNTER=#Counter

Note that we are using our fist variable to retrieve one record only. To run this statement, remember that the Counter variable MUST have a value – otherwise the where clause would not be valid.

Create FileName Variable FileName Variable Query


2. DEFINITION OF THE PACKAGE AND DESIGN OF THE LOOP

We will now create a package to design our loop. In the package, drag and drop the Counter variable and set the action to Set Variable. Assign the value 1 to the variable (select Assign in the properties window and enter the value 1 in the text box below the Assign selection).

Drag and drop the FileName variable in the package after the Counter variable. Set the action on the variable to Refresh Variable. In case of success after the refresh, execute any procedures or interfaces where the FileName is used. In case of failure of the refresh, execute a last step to indicate you are done: a failure here would indicate that you have exhausted all files listed in the table. In our example we are sending an email, but you could execute another branch of your process.

After the execution of the procedures and interfaces, add the Counter variable again, but this time set the action to Refresh Variable: this will increment the value of the variable. (If you are using a numeric variable, you can replace this with a Set Variable action and set the operation to Increment).

Then loop back to the refresh step of the FileName variable: this will either select the next file in the list… or fail and exit the loop.

Loop Package


3. BEYOND THE FIRST EXAMPLE

3.1 A Cleaner Exit

For a cleaner exit than the one described here, you may want to use a third variable where keep track of the number of files in your table with the following query:

select count(*) from ODI_FILES_TABLE

Refresh this new variable at the beginning of the package. You can then compare your Counter variable (using the action Evaluate Variable) to that value and exit the loop when you reach this value.

3.2 Guarantying that the tables are found in all Contexts

Instead of hard-coding schema names in your variable refresh queries, remember to use the ODI Substitution Method that will automatically rebuild the schema name in all contexts: odiRef.getObjectName. For instance:

select FILE_NAME
from (select FILE_NAME, ROWNUM COUNTER from < % = odiRef.getObjectName("ODI_FILES_TABLE") % >)
where COUNTER=#Counter

MORE ON VARIABLES IN ODI

For more information on ODI variables, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "Creating and Using Variables"

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

 


Thursday Apr 09, 2009

Using Parameters in ODI: The Dynamic File Name Example

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Project, Interface and Package are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

In a previous post we have seen how to use variables in the ODI interfaces and packages.
We used two methods to assign a value these variables: a direct assignment of a value, or a "refresh" statement that was running a SQL query and storing the resulting value in the variable.
Today, we will see how to set the value dynamically as we start our process. The value to be stored in our variable will be passed as a parameter to our process.

The example we will work on is a fairly common one. When dealing with flat files, it is not rare for the actual name of the file to vary over time. An external process may know the file name and pass that name over to ODI for further processing.

For our example to work, we will need to perform two actions:
- Reference the file name dynamically (In the ODI Metadata, we cannot hard-code the file name anymore)
- Pass the file name as a parameter to our process.

1. MAKING THE FILENAME DYNAMIC

1.1 Creation of a Variable

Our first step is to create a variable to store the file name. Variables are defined in projects, even for variables that will be used in the metadata. Select the project where you will use the variable (typically the project that will contain the Interface where the file is used as a source or as a target).
We will create a variable called FileName.

Filename Variable Creation

Defining a default value for the variable is not required, though it is good practice. We do not need an associated query in the refresh tab of the variable object, as we will receive this value as a parameter.
Click Ok to save the definition of the variable.

1.2 Using the variable in the metadata

Now that we have created a variable, we can use it in place of the file name.
Note: If you are not familiar with the definition of Files in the ODI Models (where the metadata are defined) please refer to the ODI Tutorial where you will find a couple of examples.

I usually recommend starting with a hard-coded version of your file name. Copy a version of the file locally, and use it to define the file structure. This will allow you to validate the file format, make sure that the data can be viewed with the ODI GUI, validate alignment of the columns, etc. Pay particularly attention to date columns (unless you define them as strings – after all, this being a file, you can choose the most convenient format for you): dates require the definition of the date format. The ODI documentation gives you the necessary details on how to describe this format (See the Appendix on Date Formats in the Reference Manual).

In our example, we will use the file SRC_SALES_PERSON.txt that is provided as an example with ODI (look under your ODI installation directory under oracledi/demo/file).

Once we have validated that the file format is correct, we will edit the file definition. Double click on the file name in the Model tree, so that we can change the value of the Resource Name. We can keep the Name as is, to have a meaningful description of the nature of the file, and replace the Resource Name with our variable. Whenever using a variable outside of a project, keep in mind that you always have to prefix the variable name with the project CODE. In case of doubt, double click on your project Name in the Project tree: the definition window of the project will show you both the name of the project and the code of the project.

Project Code

In the example above, the project code is SALES.

Back to the Definition of our file, we now change the Resource Name from SRC_SALES_PERSON.txt to #SALES.FileName.

Dynamic File Name

When you click Ok to save this new definition, you can see in the Model tree that ODI will show you both the Name (SRC_SALES_PERSON.txt) and the Resource Name (#SALES.FileName) to indicate that this value will be set dynamically.

File Name In Tree

One important note is that once you use the variable as part of the resource name, you cannot see the data from your file from the GUI as the variable will only be resolved at run-time. From that perspective, it may be useful to have two definitions for the same file: one using the variable, and one that points to a specific file so that debugging is made easier.


2. PASSING THE FILE NAME AS A PARAMETER

2.1 Create a Package and Declare the Variable

You will have to create an interface where you use the file (as you would do for any other file) so that you can really see the operations in action. Then create a package where you drag and drop the FileName variable and the interface. Make sure that the variable is your first step (right-click on the variable icon in the package and select First Step if it is not the case. The first step of your package is identified by a little green triangle over the step name).

Parameters Package


Click on the icon representing the FileName variable and set the action on this variable to Declare Variable. This will authorize the passage of the value as a parameter at run time.

Parameter Declare Variable


2.2 Create a Scenario

You will only be able to pass parameters to a "production ready" object: the scenario. The creation of a scenario is trivial: right-click on the package name, select "generate scenario", edit the scenario name and version if needed and click ok.

Generate Scenario

Set Scenario Name and Version

If you have "declare variable" steps though, you will be prompted to define whether these will be parameters or not.

Scenario Parameters

When this window prompts you, if you do not change anything, all declared variables become placeholders for potential parameter values. Note that in the screenshot above, we have changed the selection from "Use All" to "Selective Use" simply to highlight the variable name. Either selection would have had the same value in our example.

At this point, we now have a scenario that is ready to receive a value for our FileName variable.

2.3 Different ways to start the scenario and pass the parameter

There are many ways to start a scenario. Each one of those will allow you to set the values for your parameters.

2.3.1 Starting the Scenario from Designer or Operator

In both cases, the GUI will prompt you for the value of the parameters after you select in which context and with which agent to run the scenario:

Set Scenario Params Value

ODI will by default select the last value assigned to the variable. You can uncheck the "Last Value" check box and change the value for the parameter. Hit the "Enter" key or the "Tab" key to validate your entry and run the scenario.

2.3.2 Starting the Scenario from a Command Line

You can also start a scenario from a command line interface. The script startscen (.bat for Windows or .sh for Unix-like environments) will take the scenario name as a parameter... along with the name of the variables that you are setting along with their values. Enclose the variable name and its value in double quotes, and prefix the project code with a minus sign (-). Remember to prefix the variable name with the project CODE as usual:

starscen DYNAMICFILENAME 001 "-SALES.FileName=c:/Incoming/Sales09092009.dat"

2.3.3 Starting the Scenario through a Web Service

If you start the scenario from a web service, you are invoking the service OdiInvoke. When you look at the WSDL for this Web Service, you will notice that beyond the scenario name, version and the execution context, you can specify pairs of variable names and values. Again, remember to specify the project code

WSDL to Start Scenario

2.3.4 Starting the Scenario from Another Scenario

If you start a scenario from another ODI package, you can either drag and drop the scenario in the parent package or use the ODI tool OdiStartScen and enter the information manually. When you look at the property window for that step, you will notice an Additional Variables tab. In this tab, you will be able to select your project, variable and to set the value for the variable. Remember to use the Enter or the Tab key to validate your choices as you make them.

Scenario Call From Package


MORE ON VARIABLES IN ODI

For more information on ODI variables, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "Creating and Using Variables"

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

Monday Mar 30, 2009

Using Variables in ODI: The Timestamp Example

The posts in this series assume that you have some level of familiarity with ODI. The concepts of Model, Project, Interface and Package are used here assuming that you understand these concepts in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information on these concepts.

This is the first post of a series that will explore how variables can be used in ODI for more flexibility in code generation.
In this discussion, we will use a Timestamp to extract changes from a source table. This will help us illustrate the usage of variables in Interfaces and Packages.
Subsequent posts will explain the usage of variables in more advanced cases:
- using variables to pass parameters to a process
- bind variables
- using variables in Topology.

But first, let's get started with the general usage of variables with our Timestamp extract example.

INTRODUCTION

Detailed instructions will be provided throughout this post to describe how to reproduce this case. We will create 2 variables in ODI for this example. Then we will implement the following logic:
- Store current date and time in variable1 as we start our process
- Filter out the data from the table on using variable2 that contains the data and time of the last operation
- Update variable2 with the value of variable1
- Run again!

The examples given below assume that we are using an Oracle database, but can easily be adapted to work on any other database.

1. VARIABLES SETUP IN ODI

1.1 Variables Definition

1.1.1 General Information
The first step is to create two variables in ODI. The first variable will be used to store the date and time at which we started the process – only for the duration of the process. The second variable will be used to save that date for the next iteration of the package.

To create a variable in ODI, expand your project and right-click on the ‘Variables’ folder. Then select ‘Insert Variable’

Create New Variable


You will then see the Variable definition window:

New Variable Definition

1.1.2. First Variable: StartTime
We will name our first variable StartTime.
Set the data type to ‘Alphanumeric’, simply because it is generally easier to handle than dates.
Define a default value, for instance
’29-MAR-2009 10:08:12’.
Note the single quotes around the text for the default value. It is usually better to define a default value, if only to make sure that if you forget to set a value for your variable, you will not be left with a NULL...
Fill in the ‘Description’ field to explain what the variable will be used for. This is important as this field will be retrieved by ODI when the documentation is generated from the “Print” menu in your project:
This variable will be used to store the time at which the extraction process starts so that we can remember this value for the next run of this same process

Now click on the ‘Refreshing’ tab to see the following window:

Variable Refresh SQL Query

Select any logical schema on Oracle and type the following SQL query:
Select SYSDATE from DUAL

Note:: You can run the query to make sure it is valid. Click the ‘refresh’ button in the above window. Then close the variable definition window, and check in the ODI Logs that the query runs successfully. You can then re-open the variable definition window, and check the resulting value in the ‘History’ tab of the variable.

Click Ok to save your first variable.

1.1.3 Second Variable: LastUpdate
We will name our second variable LastUpdate.
Set the data type to ‘Alphanumeric’. The default value will be the original start date (basically the date for the oldest recors in our source file. (be careful here with the date format).
’01-JAN-1997 00:00:01’
No need to put a refresh value here. (Though an alternative may have been to put here a query to retrieve the oldest date in the source system!)

Click Ok to save your second variable

Both variables can be seen in the Project Tree if you expand the Variables entry.


2. USING THE VARIABLES IN INTERFACES AND PACKAGES

2.1. Use the ‘LastUpdate’ variable to filter incoming records

When you are designing your interface, you now want to filter data based on the timestamp. This will be done with the LastUpdate variable
1. In the interface, create a filter: drag and drop a date column in on the dark gray area to create the filter. ODI will display a little funnel and open a property box for you to specify the filter expression. That box is pre-filled with the column name
2. Edit the filter expression and add:
>=#LastUpdate
(We only want the records where the timestamp is greater than the value of the variable)

Variable In Interface

2.2 Create a package to handle the timestamp values

Now you can create a package (in your project, right click on Packages and select Insert Package), in which you will do the following:
1. Drag and drop the LastUpdate variable from the Project Tree into the package. Then click on the icon representing the variable in the package and set the type to declare variable. Set the step name to Declare LastUpdate
2. Drag and drop the StartTime variable from the Project Tree into the package. Then click on the icon and set the type to declare variable. Set the step name to Declare StartTime.
3. Drag and drop the StartTime variable from the Project Tree into the package again. Then click on this icon and check that the type is set to refresh variable. This will run the associated query and store the returned value in our variable.
4. Drag and drop your interface in the package
5.Drag and drop the variable LastUpdate again, but this time set the type to Set Variable, click the assign bullet and put the entire name of the StartTime variable (including the project code)

Note: in the screen shot below, replace SALES with the CODE of the project where the variables have been defined. To retrieve the project code, double-click on the project name in the objects tree

6. Then click on the PackageOK.PNG button (available in the Package upper toolbar), and connect the icons sequentially with green arrows to define the execution order for these steps. Once this is done, save your package. This package will now process incremental loads of your sales.

The screen shot below shows the sequential ordering of the steps, as well as the value set for our LastUpdate variable after processing the interface.

Variables In Package

3. UNDERSTANDING POTENTIAL ERRORS

3.1 Project CODE vs. project NAME

Be careful when you use a variable to prefix it with a # sign, and with the project CODE, not the project NAME.

3.2 Mistyped variable names

Mistyped variable names (in the variable affectation step, or in the interface) might have unexpected results. Pay attention in particular to the case used for the variable names and when you reference them: variables are case sensitive!

3.3 Understanding Variable Values

Variables values are not visible in the generated code that you can review in the Operator interface. For instance, the code we have described above for the interface would appear in the Operator as
Select (…) from (…) where HIRE_DATE>=#SALES.LastUpdate

Even though the variable value is not visible here, we know that ODI has recognized the variable as it has inserted the project code. This is good news and guaranties that the value will properly be substituted at runtime.

If you want to see the value taken by the variable at runtime, look at the History tab in the variable definition, or look at the “Variables” folder in the operator (under the session ID) and then check out the history tab of all variables used in this execution.

 

MORE ON VARIABLES IN ODI

For more information on ODI variables, please refer to the ODI Users Guide (part of the Documentation Library that comes with the ODI installation), in particular the entry "Creating and Using Variables"

All Screenshots were taken using version 10.1.3.5 of ODI. Actual icons and graphical representations may vary with other versions of ODI.

About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
5
6
7
8
9
10
12
13
14
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today