« Using Variables in ODI: The Timestamp Example | Main
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.
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.
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.
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.
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).
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.
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.
If you have "declare variable" steps though, you will be prompted to define whether these will be parameters or not.
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:
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
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.
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.
출처 : http://blogs.oracle.com/dataintegration/2009/04/using_parameters_in_odi_the_dy_1.html
파일 모델을 생성하고 데이터저장소를 생성해 놓은 다음에
resource를 변수로 지정하면 Dynamic하게 Filename을 변경하면서 서로다른 데이터 모델을 사용할 수 잇는 것으로 확인되었다.
변수를 글로벌로 정의 할 필요는 없을 것 같고,파일이 없거나 포멧이 다른 경우에는 당연히 Error가 발생하겠지?
