ETL stands for Extract Transform and Load. ETL involves the
movement and transformation of data from your sources to your targets. This
lesson will teach you about Warehouse Builder's mappings.
This lesson begins with brief overview of mappings. You will
create a mapping that will use operators to define the ETL operations that move
data from a source object to a data warehouse target object. While defining
mappings you will learn to use the Mapping Editor, Attribute Property sheets,
Expression Builder, Code Editor.
This lesson will take approximately 60 minutes to complete.
Viewing Screenshots
Move your mouse over this icon to show
all screenshots. You can also move your mouse over each individual icon to see
only the screenshot associated with it.
Overview
In this lesson you will learn one of the Warehouse Builders
capabilities, defining mappings and using the Mapping Editor, the interface
for defining what you want to transform and map. The mapping editor uses an
operator for each operation you want to perform, and a series of operations
defines a mapping. By the end of the lesson you will have learned to create
a mapping that loads the WAREHOUSE dimension and the second mapping that loads
the INVENTORIES cube.
A Mapping describes a series of operations that pulls
data from sources, transforms it, and loads it into targets. Mappings provide
a visual representation of the flow of the data from sources to targets and
the operations performed on the data.
In this topic you will examine a simple pre defined
mapping MAP_EMPLOYEES_INITIAL_LOAD_STG. The MAP_EMPLOYEES_INITIAL_LOAD_STG mapping
implements data load from a flat file definition into a relational table. Warehouse
Builder generates a SQL loader control file in this case.
1.
In the OWB10g_DEMO project,
expand Databases, expand the Oracle
node and expand the STG (stands for Staging) module.
Expand the Mappings node and select the MAP_EMPLOYEES_INITIAL_LOAD_STG
mapping.
2.
Double click the MAP_EMPLOYEEES_INITIAL_LOAD_STG
mapping. This opens up the selected mapping in the mapping editor.
This mapping loads data from a flat file definition into a relational
table.
3.
From the Mapping menu, select
Generate>Mapping.
4.
Before the Mapping is generated Warehouse
Builder validates it.
The code viewer displays the SQL loader control file.
Close the code viewer window. Close
the Mapping Editor.
Instead of writing the code for this file manually, you use Warehouse
Builder to graphically design the mappings. After the design you select
generate and Warehouse Builder takes care of the most optimal code.
Defining a Mapping to
load the WAREHOUSE Dimension
In this topic you will learn to create a mapping that loads
the WAREHOUSE dimension. When you create a mapping, you
use operators to define the Extraction, Transformation, and Loading (ETL) operations
that move data from a source object to a data warehouse target object.
The Mapping Editor contains a toolbox that visually represents the operators.
In this topic you will learn to add a Mapping Dimension and
Mapping Table operator in the Mapping Editor. All mappings are based on mapping
operators. A mapping operator is a logical representation of a physical repository
object.
1.
From the Toolbox Drag and drop the Mapping
Dimension icon on to the Mapping canvas.
2.
The Add Mapping Dimension
dialog opens. Select option Select from existing repository Dimension
and bind.
3.
Expand WH node (if not expanded),
select WAREHOUSE dimension and click OK.
4.
The WAREHOUSE Dimension appears on the Mapping
Editor. Right click the title at the top of the Warehouse Dimension and
from the shortcut menu select Resize To Fit.
The Resize to fit option will show all attributes.
5 .
To add a Mapping Table operator, drag and
drop on to the Mapping Editor, the Mapping Table icon
from the toolbox.
The Add Mapping Table dialog opens. Select option Select
from existing Repository table and bind.
6 .
Expand the OE source module
select WAREHOUSES table and click OK.
The next topic will demonstrate how to use Key Lookup operator.
The Key Lookup operator reads data from a lookup table by
using the key input that you supply and finds the matching rows. The mapping
MAP_WAREHOUSE needs lookups to table definitions LOCATIONS and COUNTRIES.
1.
Drag and drop the Key Lookup
operator on to the Mapping Editor.
The Add Mapping Key Lookup dialog opens. Select option
Select from existing Repository Key Lookup and bind.
2.
Select COUNTRIES table from
the HR module, press CTRL on the keyboard
and select LOCATIONS from the HR module
and click OK.
3 .
Maximize the Mapping Editor window. Spread
the operators across the canvas.
4 .
Right click the LOCATIONS
key lookup operator and select Resize to Fit to see full
attribute name and list of attributes
Map attribute LOCATION_ID from the WAREHOUSES table
to the LOCATIONS key lookup operator.
Click on LOCATION_ID under INOUTGRP1
from WAREHOUSES mapping table and drag a line to INGRP1
of LOCATIONS.
5 .
Define the Key Lookup condition.
Right click the LOCATIONS operator and select Operator
Properties....
6 .
The Key Lookup Properties
window appears, Click on the field next to Lookup Condition
and click the three-dotted button on the right (...).
7 .
The Lookup Condition dialog
appears, Click Add to List.
8 .
The Match Key Columns to Inputs for
key LOC_ID_PK dialog appears. Here you create key lookup conditions
by matching source attributes with the lookup table key attributes and
click OK.
.
9 .
The Lookup Condition dialog
reappears review the Key Lookup condition and click OK.
The Key Lookup Properties window is displayed. Close
the Key Lookup Properties.
10 .
Right click the LOCATIONS
Key Lookup operator and select Resize to Fit.
11 .
Map attribute COUNTRY_ID from the LOCATIONS
key lookup to the COUNTRIES key lookup operator.
Drag a line fromCOUNTRY_ID
attribute in OUTGRP1 of LOCATIONS key lookup into INGRP1
of COUNTRIES.
12 .
Define the Key Lookup condition.
Right click the COUNTRIES key lookup operator and select
Operator Properties...
13 .
The Key Lookup Properties
window appears, Click on the field next to Lookup Condition
and click the three-dotted button on the right (...).
14 .
The Lookup Condition dialog
appears, Click Add to List.
15 .
The Match Key Columns to Inputs for
key COUNTRY_C_ID_PK appears. Here you create key lookup conditions
by matching source attributes with the lookup table key attributes and
click OK.
16 .
The Lookup Condition dialog
reappears review the Key Lookup condition and click OK.
The Key Lookup Properties window appears with the condition
displayed. Close the Key Lookup Properties window.
This topic demonstrates an attribute by attribute mapping.
1.
Right click the WAREHOUSES table and select
Resize to Fit. Right click the WAREHOUSE dimension and
select Resize to Fit.
Drag a line from WAREHOUSE_ID
from the WAREHOUSES table to WRE_ID in the WAREHOUSE
dimension.
2.
Drag a line from WAREHOUSE_NAME
from the WAREHOUSES table to WRE_NAME in the WAREHOUSE
dimension.
3 .
Drag a line from LOCATION_ID
from OUTGRP1 of LOCATIONS key lookup operator to LCN_ID
in the WAREHOUSE target dimension.
4 .
Drag a line from CITY
from the LOCATIONS key lookup operator to LCN_CITY
in the WAREHOUSE target dimension.
5 .
Drag a line from STATE_PROVINCE
from the LOCATIONS key lookup operator to LCN_STATE_PROVINCE
in the WAREHOUSE target dimension.
6 .
Right click the COUNTRIES key lookup operator
and select Resize to Fit. Drag
a line from COUNTRY_ID from OUTGRP1 of COUNTRIES key
lookup operator to CTY_ID in the WAREHOUSE target dimension.
7 .
Drag a line from COUNTRY_NAME
from the COUNTRIES key lookup operator to CTY_NAME in
the WAREHOUSE target dimension.
In this topic you will learn how to generate a Mapping.
1.
In the Mapping Editor, right click the INOUTGRP1
on the WAREHOUSE dimension and select Generate
Intermediate Results.
2.
The Code Viewer window pops
up and shows a set based MERGE statement. Before Warehouse Builder generates
the most optimal code for the update/insert on the WAREHOUSE dimension
it will do a validation for the selected object. Close
the Code Viewer window. Close the Mapping Editor.
In this topic you will learn to create a mapping to load the
INVENTORIES cube and use some more mapping operators from the floating toolbox
in the Mapping Editor.
In this topic you will create a mapping MAP_INVENTORIES.
1.
In the OWB10g_DEMO project,
expand Databases, expand the Oracle
node and expand the WH target module. Right click the
Mappings node and select Create Mapping.
2.
The New Mapping Wizard: Name
page appears. Type MAP_INVENTORIES as the name of the
mapping and click OK.
3.
The Mapping Editor:
MAP_INVENTORIES window opens. The Mapping Editor contains
a toolbox that visually represents the operators.
In the next topic you will learn to use some more new operators from
the floating toolbox.
In this topic you will drag and drop the cube icon from the
floating toolbox on to the Mapping canvas.
1.
In the Mapping Editor, from the Toolbox drag
and drop the Mapping Cube operator to the canvas.
2.
The Add Mapping Cube dialog
is displayed. Select option Select from existing repository Cube
and bind. Under the WH target module select
the INVENTORIES cube. Click OK.
3.
The Mapping Editor:
MAP_INVENTORIES window opens.
You will learn how to use some more operators from the Toolbox.
In this topic you will add a Mapping Table operator to the
MAP_INVENTORIES mapping.
1.
In the Mapping Editor window, drag and drop
the Mapping Table operator to the canvas.
2.
TheAdd Mapping Tabledialog appears. Select option Select from existing repository
Table and bind. From the OE source module select
the INVENTORIES table and click OK.
3.
The Mapping Editor:
MAP_INVENTORIES window is displayed with the INVENTORIES
mapping table and INVENTORIES mapping cube.
In this topic you will add a Constant to the MAP_INVENTORIES
mapping.
1.
In the Mapping Editor: MAP_INVENTORIES, drag
and drop a Constant operator on to the canvas.
2.
In the Mapping Editor, right click the CONST
operator and select Edit.
3.
The Constant Editor: CONST window appears.
Select the Output Attributes tab and click Add.
4.
Rename the OUTPUT1 attribute to TODAY,
change the NUMBER datatype to DATE and click OK.
The CONST operator in the Mapping Editor now has the TODAY attribute.
5.
In the Mapping Editor right click on attribute
TODAY and select Attribute Properties.
6.
The Attribute Properties:
TODAY dialog appears. Click on the field next to Expression
and click the three-dotted button (...).
7.
The Expression dialog displays. In the Expression
for: TODAY field type sysdate.
Optionally you can click Validate to validate your expression,
on success the message Validation Successful is displayed in the Validation
results field.
8.
Click OK to close the Expression
dialog. Close the Attribute Properties: TODAY dialog.
The Aggregator operator performs data aggregations, such as
SUM and AVG, and provides an output row set with aggregated data. In this topic
you will learn how to define an aggregator operator.
1.
In the Mapping Editor, from the Toolbox, drag
the Aggregator operator and drop it on the canvas.
2.
(Right click on INVENTORIES_1
mapping table operator and select Resize to Fit to be
able to see all the attributes)
Map attributes fromINVENTORIES_1 mapping Table to AGG
Aggregator:
Drag a line from PRODUCT_ID of INVENTORIES mapping table
to INGRP1 of AGG operator
3.
Map attributes fromINVENTORIES_1
Mapping Table to AGG Aggregator:
Drag a line from WAREHOUSE_ID of INVENTORIES_1 mapping
table to INGRP1 of AGG operator
4 .
Map attributes fromINVENTORIES_1
Mapping Table to AGG Aggregator:
Drag a line from QUANTITY_ON_HAND of INVENTORIES_1 mapping
table to INGRP1 of AGG operator
5.
Map Attributes from EXPR expression to AGG
aggregator operator:
Drag a line from MONTH_NAME of EXPR operator to INGRP1
of AGG operator.
6.
Right click aggregator AGG
and select Operator Properties..
7.
The Aggregator Properties
dialog opens. Click in the field next to Group By Clause and click the
three-dotted button (...)
8.
The Group by Clause dialog
pops up. Here you specify how the rows are grouped to return a single
row for each group.
From the Available Attributes list select INGRP1.PRODUCT_ID
and move to the right-hand side GROUP BY Attributes list using the single
greater than symbol ( > ).
Select INGRP1.WAREHOUSE_ID and move
to the right-hand side list using the single greater than symbol ( >
).
Select INGRP1.MONTH_NAME and move
to the right-hand side list using the single greater than symbol ( >
).
9.
Click OK to close the Group
By Clause dialog. Close the Aggregator Properties dialog.
10.
Right click aggregator AGG,
and select Edit.
11.
The Aggregator Editor: AGG
appears. Click on the Output Attributes tab and click
Add.
12.
Rename the OUTPUT1 attribute with AMOUNT.Click OK to close Aggregator Editor:
AGG dialog.
13.
( Right click on AGG operator
and select Resize to Fit to be able to see all the attributes)
In the Mapping Editor, collapse INGRP1 on aggregator AGG. Right click
the attribute AMOUNT on OUTGRP1 of AGG and select Attribute
Properties.
14.
In the Attribute Properties: AMOUNT
click on the field next to Expression, and click the three-dotted button
(...)
15.
The Expression box opens, from the Function
drop-down list select SUM and from Parameter drop-down
list select QUANTITY_ON_HAND. Click OK.
In this topic you will learn how to create a key Lookup operator.
1.
In the Mapping Editor, from the toolbox, drag
the Key Lookup operator and drop it on the canvas.
2.
The Add Mapping key Lookup
dialog opens. Select option Select from existing repository Key
Lookup and bind. From the WH target module select
T_TIME and click OK.
A key lookup to the T_TIME dimension is needed to have an ID for the
month.
3.
Drag a line from MONTH_NAME
attribute from OUTGRP1 on AGG to INGRP1 of T_TIME Key
Lookup operator.
4.
In the Mapping Editor, right
click T_TIME key lookup operator and select Operator
Properties.
5.
The Key Lookup Properties dialog
is displayed, click on the field next to Lookup Condition and click the
three-dotted button (...)
6.
The Lookup Condition dialog
is displayed, choose L_MONTH_NAME from the Lookup Table
Column or Key drop-down, and click Add to List.
7.
Click OK to close the Lookup
Condition dialog. Close the Key Lookup Properties dialog.
8.
In the Mapping Editor drag a line from L_MONTH_ID
from the T_TIME key lookup into L_MONTH_ID
on cube INVENTORIES.
9.
In the Mapping Editor map WAREHOUSE_ID from
OUTGRP1 on AGG to INOUTGRP1 on cube INVENTORIES:
Drag a line from WAREHOUSE_ID from OUTGRP1 on AGG to
WRE_ID INOUTGRP1 on cube INVENTORIES.
Drag a line from PRODUCT_ID OUTGRP1 on AGG to
PDT_ID INOUTGRP1 on cube INVENTORIES.
Drag a line from AMOUNT OUTGRP1 on AGG to AMOUNT
INOUTGRP1 on cube INVENTORIES.
Use various operators to define the Extraction,
Transformation, and Loading (ETL) operations that move data from a source
object to a data warehouse target object.
Import Definitions from a Database
Define mappings using the Mapping Editor,
Attribute Properties, Expression Builder, and Code Editor.
Generate Intermediate results of a mapping.
Move your mouse over this icon to hide all screenshots
Bookmark
