etl: transformations & other operators

ETL: Transformations & Other

OperatorsOperators-Prof. Zarna Barai

-Unit 5, chap 9

2/11/2014

2

Private & Confidential

To Start With:

• Create a new mapping called STORE_MAP.

• Add the source and target operators to the STORE_MAP.

2/11/2014

3


STORE_MAP.

▫ Source : POS_TRANS_STAGE

▫ TARGET : STORE_DIM (store dimension)

• Thereafter, consider the data elements that we will need for our target & where to get it from the source.

2/11/2014

4


Transformations to be Preformed

Attributes of sourcePOS_TRANS_STAGE

Trim () transformation

Upper() transformation

2/11/2014

5


Source data contains trailing blanks & some of the fields should be made all uppercases for consistency.(NOTE: description field will include store, region & country names respectively without any trailing spaces.)

STORE_NAME Yes Yes

STORE_NUMBER Yes Yes

STORE_ADDRESS1 Yes

STORE_ADDRESS2 Yes

CITY Yes

STATE Yes Yes

ZIP_POSTALCODE Yes

STORE_REGION Yes Yes

STORE_COUNTRY Yes Yes

2/11/2014

6


2/11/2014

7


2/11/2014

8


2/11/2014

9


2/11/2014

10


Using The KEY LOOKUP Operator

• Key lookup operator is used for looking up information from other sources based on some key attributes in a mapping.

2/11/2014

11


key attributes in a mapping.

• Only tables, views, dimension & cubes can be used as the source for this operator.

• So, we will create an external table to represent the counties.csv file

Creating An External Table

• External tables are created under the Oracle | ACME_DWH |External Tables.

• Right click on External Table, select new.

2/11/2014

12


• Right click on External Table, select new.

• This will launch the external table wizard

2/11/2014

13


2/11/2014

14


2/11/2014

15


2/11/2014

16


2/11/2014

17


Creating & Loading a Lookup Table

• Create a new mapping COUNTIES_LOOKUP_MAP

• This will launch the Mapping Editor window.

2/11/2014

18


• This will launch the Mapping Editor window.

• In this window, drag and drop the external table operator from the palette into the canvas area.

• This will launch the add external table window.

2/11/2014

19


Creating & Loading a Lookup Table

• COUNTIES external table in the mapping window

2/11/2014Private & Confidential

20

Creating &Loading Lookup Table

• From the external table, we have to load the data into a regular table in the database.

• So next, we will drag the table operator from the


21

• So next, we will drag the table operator from the palette into our mapping area.

2/11/2014

22



• Mapping area now consists of external table operator (COUNTIES) & Table operator (COUNTIES_LOOKUP)


23

(COUNTIES_LOOKUP)


• Drag and drop a connection from the OUTGRP1 of external table operator to the INOUTGRP1 of table operator

2/11/2014

24


table operator


• We have created our table operator as unbound operator, which means it is not associated with any object i.e. there is no table named


25

any object i.e. there is no table named “COUNTIES_LOOKUP”.

• Steps to create a new table object and to bind this operator to it are as follows:

▫ Right click on ‘COUNTIES_LOOKUP’ table

▫ Choose ‘create & bind’

2/11/2014

26



2/11/2014

27


2/11/2014

28



• We now have the table definition created and a mapping completed that can load the table to use to lookup the county name.

• We need a key that will uniquely identify a record in


29

• We need a key that will uniquely identify a record in the table and with which we can lookup a county.

• Steps to create the primary key for COUNTIES_LOOKUP table:▫ Open the COUNTIES_LOOKUP table in the object

editor window.▫ Select Constraints Tab, add Constraint & thereafter

add local column

2/11/2014

30


Retrieving The Key To Use For A Lookup

Operator• The County ID is only a portion of the

STORE_NUMBER field.• It starts from the 3rd position & the size of county ID

is 4


31

is 4• So we need to extract the ID number, convert it to

number and direct this number to the key lookup operator.

• We will 2 transformation operators ▫ SUBSTR transformation operator

� For SUBSTR operator we need to specify starting position and Length of the string to be extract

� This will be provided by CONSTANT operator▫ TO_NUMBER transformation operator

Adding the Constant Operator To

STORE_MAP• Drag and Drop the Constant operator to the

STORE_MAP Mapping are.

• Double click on the Constant Operator table in


32

• Double click on the Constant Operator table in the mapping area.

• This will launch the Constant editor window.

• In this window select “output Attributes” tab.

▫ Add the following 2 new output attributes

� POSITON - number (0,0)

� LENGTH - number(0,0)


33


STORE_MAP• To set the constant value for POSITON

▫ Select POSITON attribute from Constant operator table


34

table

▫ Once it is selected, select “Expression” property from the Properties Window.

▫ Enter the value for Expression as 3

• To set the constant value for LENGTH

▫ Repeat the same steps as above

▫ Enter the value for Expression as 4


STORE_MAP


35

Adding the SUBSTR Transformation To

STORE_MAP• Drag & Drop the Transformation operator to the

mapping window from the palette.

• Choose the SUBSTR function


36

• Choose the SUBSTR function

• Map the output attributes, POSITION & LENGTH from the CONSTANT operator to SUBSTR operator appropriately.


37

Adding the TO_NUMBER Transformation

STORE_MAP• Drag & Drop the Transformation operator to the

mapping window from the palette.

• Choose the TO_NUMBER function


38

• Choose the TO_NUMBER function

• Map the value attribute of SUBSTR to the EXPR attribute of To_NUMBER.

Adding a Key Lookup Operator to

STORE_MAP• Drag The Key lookup operator from the palette

window and drop it to the right of TO_NUMBER transformation into the mapping window.


39

transformation into the mapping window.

• This will launch the KEY_LOOKUP wizard


40


41


42


43


44


45


46


47


48


49


50


51


52

To Start With:

• Create a new mapping called PRODUCT_MAP.

• Add the source and target operators to the PRODUCT_MAP.

2/11/2014

53


PRODUCT_MAP.


▫ TARGET : PRODUCT_DIM (product dimension)


2/11/2014

54


Transformations to be Preformed

Source data contains trailing blanks & some of the fields should be made all uppercases for consistency.(NOTE: description field will include product, brand, category & department names respectively without any trailing spaces.)

2/11/2014

55





PRODUCT_NAME Yes Yes

PRODUCT_SKU Yes Yes

PRODUCT_PRICE No transformation needed

PRODUCT_BRAND Yes Yes

PRODUCT_CATEGORY Yes Yes

PRODUCT_DEPARTMENT Yes Yes


56


57

To Start With:

• Create a new mapping called SALES_MAP.

• Add the source and target operators to the SALES_MAP.

2/11/2014

58


SALES_MAP.


▫ TARGET : SALES cube



59




Mapping Measures, Product & Store

Dimension Values to CubeSource data contains trailing blanks & some of the fields should be made all uppercases for consistency.

2/11/2014

60


POS_TRANS_STAGE transformation transformation

STORE_NAME Yes Yes

STORE_NUMBER Yes Yes

PRODUCT_NAME Yes Yes

PRODUCT_SKU Yes Yes

SALE_QUANTITY No transformation needed

SALE_DOLLOR_AMOUNT No transformation needed


61

Mapping Date dimension Values To

Cube• Drag & Drop the Expression operator from the

palette window into the mapping editor between source & target operators.

• Drag the SALE_DATE from the


62

• Drag the SALE_DATE from the POS_TRANS_STAGE mapping table to the INGRP1 of the EXPRESSION operator.

• Create an output attribute ‘DAY_CODE, number (0,0) ‘ for expression operator.

• Drag a line from the DAY_CODE of our EXPRESSION to the DATE_DIM_DAY_CODE attribute of our SALES cube.

Mapping Date dimension Values To

Cube• Set the properties for the DAY_CODE attribute :

▫ Select the DAY_CODE attribute

▫ From the properties window, choose the


63

▫ From the properties window, choose the ‘Expression’ property and lunch the expression editor window.

▫ Type the following expression in the expression editor window

� TO_NUMBER(TO_CHAR( INGRP1.SALE_DATE ,'YYYYMMDD'))


64


65


66

etl: transformations & other operators

Documents