Using Variables in Data Flows
There are times when you want to move data and vary
something in the data flow each time that it runs. You might want
to only pull records for a particular range of dates, for a particular
department, or any criteria that changes from run to run. DataMigrator
lets you use a variable in the data flow to accomplish this.
The most common use of variables is in a filter. When the data
flow is executed, the variable must be set to an appropriate value.
A value could also be set by executing a pre-extract stored procedure.
For example if the value of the variable is read from a flat file
or other external source.
The following data flow extracts data from the DMORD table. The
filter contains a variable for date, &ODATE, so that each time
the flow is run &ODATE can be passed a different value, and
records with different dates can be extracted from the data source
and loaded into the data target.
The process flow calls the data flow passing a value for the
variable as a parameter.
Refer to the sample data flow DMVAR and the sample process flow
DMVARPF for the complete example.
Example: Using Variables in Data Flows
In
the DMC:
- Right-click
an application directory in the navigation pane and choose New,
then Flow. The Data Flow tab opens in the
workspace, with the SQL object displayed.
- Drag the data
source object DMORD from the ibisamp application
directory in the navigation pane into the workspace, to the left
of the SQL object.
- Right-click
the SQL object and select Column Selection.
The
Column Selection window opens.
- Select PLANT, PROD_NUM,
and QUANTITY in the Available Columns list
and click the > arrow to move them into
the Selected Columns list.
- Select T1.PLANT and T1.PRODNUM in
the Selected Columns list, and select Group By from
the Aggregate drop-down menu.
- Select T1.QUANTITY in
the Selected Columns list, and select Sum from
the Aggregate drop-down menu.
- Click OK to
close the Column Selection window.
- Right-click
the SQL object and select Filter.
The Filter
Calculator opens.
- Double-click
ORDER_DATE under Columns/Variables. This adds the column to the Conditions
area.
- Complete the
condition by typing
= '&ODATE'
after
T1.ORDER_DATE in the Conditions field.
The Filter Calculator
should appear as shown in following image.
- Click OK to
close the Filter Calculator.
- Right-click
the workspace to the right of the SQL object and select Add Target,
then New.
- Right-click
Target object and select Properties. Select
the adapter you used for your sample procedures. Then enter the
application directory you are using for your samples dmplant as
the name, for example baseapp/dmplant. Then click the small x to
close the properties panel.
- Right-click
the DMPLANT target object and select Target
Transformations.
The Transformations window opens.
- Check the Key
column for PLANT and PROD_NUM in
the Target Columns list to designate them as key columns in the
data target. Click OK.
- Save the data
flow as DMXVAR.
Example: Prompting for a Variable Value
In
order to test the data flow you need to supply a value for the variable.
In the DMC:
- Click theRun
button
and select Run.
The prompting window opens.
Enter a value to test, for example 2006/02/14.
- Then click Run.
The
data flow is tested using the supplied value. As shown in the following
image, the output from the flow appears in the console log window.
Example: Creating a Process Flow to Pass Parameter to a Data Flow
To
create the process flow that runs both the stored procedure and
the data flow:
- Right-click
an application directory in the navigation pane and choose New,
then Flow.
- Click the Process
Flow tab.
- Drag the
data flow, DMXVAR, from the navigation pane into
the process flow workspace, to the right of the Start object.
- Right-click
the DMXVAR object and select Item Properties.
Click the ellipsis button
for Parameters.
The Parameters editor opens
- Click the Add
Parameter
button.
The
Drill Down Parameter editor window opens.
- Select ODATE from
the Parameter Name drop-down menu. Enter a date value such as 2005/03/06.
- Click OK.
The
parameter appears in the Parameter Editor.
- Click OK.
The
parameter appears in the property pane.
- Save the
flow as DMVARPFX. You can submit the flow now.