You are going to add two data targets and specify the
options you want to use when loading data into them.
- The first data
target is a pre-existing table of sales reports that we designed just
for this purpose. You are going to add data from last year to it.
- The second
is a target table that you will create to provide additional analytic
data that will be useful to the line managers.
The newly created target will be updated by other flows.
Map Columns in an Existing Data Target
To specify the first data target:
- From the navigation
pane, drag the synonym dmrpts from the ibisamp
directory into the workspace, to the right of the SQL object. (The position
to the right of the SQL object makes it a data target.)
- Once you have
added the data target to the data flow, you can specify how incoming data
should be handled during the loading process.
Right-click the dmrpts target
object and click Properties. For this exercise,
you will accept the default Load Type, Insert/Update, as shown in
the following image.
you intend to run a flow more than once, it is advisable to clear
the target before starting the run. This technique generally enhances
performance in subsequent runs. In the Prior to Load
Option in the Target Properties window, select the clearing
option that suits the adapter type being used for your data targets.
For example, for a relational table, the option is Truncate table.
For a FOCUS/FDS or XFOCUS table, the option is Drop table.
- Click X to
close the Properties window.
The next step is to map the data
source columns you are extracting to the data target columns.
- Right-click dmrpts and
this time, click Target Transformations.
The Transformations window opens.
- Click the Automap button.
five columns with identical names and data types are mapped and
moved to the Expression tab. Note that the mapped columns are also
selected in green in the Target Columns list, as shown in the following
- Two target
column names are deselected, so you need to create mappings or transformations
for them. In the Target Columns list, YRMTH contains year and month data.
You will map it to the YEARMONTH column in the Selected Columns
- Under Target
Columns, click YRMTH.
- Under Selected
Columns, click YEARMONTH.
- Click the equal
sign (=) to move the mapping into the grid in the Expressions tab.
- For the PROFIT
column, you will build a transformation based on the definition
of PROFIT as the difference between LINEPRICE and LINECOGS. As you
can see, the data target is another place where we can apply transformations.
Target Columns, double-click PROFIT to move
it to the Target columns list in the Expressions tab, as shown in
the following image.
- To open the
Transformations Calculator, double-click PROFIT in
the Expressions tab . To create the expression in the expression
box of the calculator:
- In the tree,
- On the calculator
pad, click the minus sign (-.
- In the Source
Columns list, double-click LINECOGS.
Transformation Calculator should look like the following image:
- Click OK to
complete the expression and close the calculator.
window now looks like the following image:
- Scroll to the
right to see the last column. It may be useful to validate the incoming data.
To do that, load only those records with a quantity greater than
or equal to ten. Records that do not meet this validation criterion
can be logged to a file, for additional processing, or for review
at a later date.
the Validates tab in the Transformation window.
Then click the Insert Transforms button to
open the Transformation Calculator.
- To build the
validation expression, double-click QUANTITY in
the tree, and then select GE from the Relation
drop-down menu. Input 10 into the Value field.
Click OK. Your goal is to see which rows
pass the validation test.
- Now, let us
test the validation and transformations to ensure that they are syntactically
correct and performing the desired calculations. The test retrieves
some rows from the server, applies the transformations, and displays
Click the Test Transforms button
in the upper-right corner, above the Validates tab.
of the transformation test appears, as shown in the following image.
number of records retrieved will depend on the Run Options set from Tools and Options,
and on the database you choose for your sample data.
Test1 column, the number 1 represents rows that will be accepted
based on the validation test. The 0s represent rows that will be
rejected because QUANTITY is less than 10.
you have been working in the Transformations window, testing from
there is the simplest method, but you can also test the transformation
by right-clicking the Target object, selecting Toggle, and clicking
the Test Transforms tab.
- Notice that
Profits are positive in the answer set, which means that the transformation
is working. It also satisfies the test condition, so you can move
on to the next data target, which you will create from scratch.
- Close the Test
Transformation window, and click OK to close
the Transformations window.
DataMigrator can load multiple data targets in a single
data flow. Let us add another target object into the data flow.
This time, the data target you need does not exist, so you will
create it using the columns in the SQL SELECT statement that you
defined for the SQL Select Columns object.
This target table will be used by line managers for competitive
analysis. You will create the base table here, but other flows can
update it with additional information.
in the workspace to the right of the SQL object, select Add
target, and then click New. A
new data target appears to the right of the SQL object.
the new target and click Properties.
Target Properties window opens.
- To define target
- Choose the
same type of Adapter you used for the data sources in our example.
In this case, it was MS SQL Server.
- Change the
- Name the synonym
for the new data target linerpts.
- Retain the
default Load Type of Insert/Update.
The target properties window
should look like the following image:
- Close the panel.
the new data target and click Target Transformations.
- In the table
you are creating, PROD_NUM, STORE_CODE, and YEARMONTH are all keys.
Click each one to identify them, and those above it, as keys. The
Transformations window looks like the following image:
- Click OK to
return to the Data Flow tab, which now contains two data sources
and two data targets, as shown in the following image.
Next Step: You are ready to save the data flow. Both data
targets, dmrpts and linerpts, will be loaded based on your specifications.
Save and Name the Data Flow
Click the Save button in the
Quick Access Toolbar.
The Save Procedure As window opens. Select which application
directory to save the flow to using the Save In drop-down menu,
and enter a flow name in the Name field. For this example, we will
save our flow in the salesdemo application directory, and name it salesdb.
Next Step: You are ready to run the data flow.