etl: putting it together

ETL: Putting It TogetherETL: Putting It Together-Prof. Zarna Barai

-Unit 4, Chap 8

2/4/2014Private & Confidential

2

Designing The Staging Area Contents

• The staging area will hold the data extracted directly from the ACME_POS.

• Staging area can be in the same database as the

2/4/2014Private & Confidential

3

• Staging area can be in the same database as the target, or in different database, depending on various factors such as the size & space issues & availability of the database.

• For Acme Toys & Gizmos organization, We will create a staging area as single table in target database schema.

Target Structure

2/4/2014Private & Confidential

4

SALES PRODUCT STORES DATE

Quantity SKU Name Date of SaleQuantity SKU Name Date of Sale

Sales Amount NAME Number

List Price Address1

Department Address2

Category City

Brand State

Zip Postal Code

Country

Region

Building The Staging Area Table With

Data Object Editor• Login into the Design Center.

• Navigate to Databases | Oracle | ACME_DWH | Tables node , Right click & select new.

2/4/2014Private & Confidential

5

Tables node , Right click & select new.

• Object Editor Window appears.

• Create a new table with name POS_TRANS_STAGE

2/4/2014Private & Confidential

6

2/4/2014Private & Confidential

7

Tabs Data Object Editor for Table

• Name• Columns• Constraints

▫ Check constraint

2/4/2014Private & Confidential

8

▫ Check constraint▫ Foreign key▫ Primary key▫ Unique key

• Indexes• Partitions• Attribute Sets• Data Rules• Data Viewer

2/4/2014Private & Confidential

9

Creating a Mapping

• Login into the design center, navigate to the ACME_DW_PROJECT | Databases | Oracle | ACME_DWH | Mappings node.

2/4/2014Private & Confidential

10

ACME_DWH | Mappings node.

• Right click on the Mappings node and select new.

• This will launch the “Create Mapping” window

• After providing the name and description of the mapping Editor window appears.

Creating a Mapping

2/4/2014Private & Confidential

11

To start with• Layout

▫ Sources on the left

▫ Final targets on the right

2/4/2014Private & Confidential

12

▫ Transformation in between the source and target.

• Can we directly connect source to target ?

• What transformations to be included ?

▫ Joiner operator

▫ Aggregation operator

▫ Transformation operator

Adding Source

Tables

• 2 ways to add source tables in mapping window of mapping editor:

2/4/2014Private & Confidential

13

mapping editor:• Using Explorer

window

• Using Palette

• Similarly add the target stage table

2/4/2014Private & Confidential

14

Joiner Operator

• Drop joiner operator from the palette window to the canvas area.

• Define the properties for joiner operator.

2/4/2014Private & Confidential

15

• Define the properties for joiner operator.

▫ Double click on joiner operator in the mapping window, joiner editor window appears.

▫ Select “Groups” tab & add one input group for each source table selected.

• Connect the source table operators to the joiner operator table

2/4/2014Private & Confidential

16

2/4/2014Private & Confidential

17

2/4/2014Private & Confidential

18

Defining The Properties for Joining

• Select the Joiner Operator from the canvas area.

• Verify that the, the properties window will now display the properties of joiner operator.

2/4/2014Private & Confidential

19

display the properties of joiner operator.

• Select the property “join condition” and click the button which displays three dots on it.

• The Expression builder window appears.

2/4/2014Private & Confidential

20

2/4/2014Private & Confidential

21

Adding The Aggregator Operator

• We need aggregation to add up sales quantities and dollar amounts for every product.

• Steps:

2/4/2014Private & Confidential

22

• Steps:▫ Drag the aggregator operator into the canvas to

the right of the joiner operator.▫ Connect output attributes from the joiner operator

to the input of the aggregator operator.▫ Define properties for aggregator operator.▫ Connect the output of the aggregator operator to

the POS_TRANS_STAGE.

2/4/2014Private & Confidential

23

2/4/2014Private & Confidential

24

Applying Transformation to Date

• Drag the Transformation operator from the palette window and drop it on the canvas between the joiner operator and aggregator

2/4/2014Private & Confidential

25

between the joiner operator and aggregator operator, near DATE_SOLD.

• Add Transformation window appears

2/4/2014Private & Confidential

26

2/4/2014Private & Confidential

27

2/4/2014Private & Confidential

28

2/4/2014Private & Confidential

29

2/4/2014Private & Confidential

30

Setting The Properties Of Aggregator

• Select the Aggregator operator from the canvas area• Select the group by clause property of the aggregator

from mapping properties window.• Click on the button which shows three dots on it.

2/4/2014Private & Confidential

31

• Click on the button which shows three dots on it.• Expression builder window appears.

▫ Enter the grouping details

• Add 2 new output attributes to the aggregator▫ Sales quantity &▫ Amount

2/4/2014Private & Confidential

32

2/4/2014Private & Confidential

33

Right-click on the OUTGRP1 attribute group of the AGGREGATOR

2/4/2014Private & Confidential

34

AGGREGATOR operator and select Open Details... from the pop up.

2/4/2014Private & Confidential

35

• select SALES_QUANTITY attributes from the OUTGRP1 group of the Aggregator.

• In the Properties window of the Mapping Editor, select expression property & click on the button which has three dots.

2/4/2014Private & Confidential

36

has three dots.

• in the Expression window, select the SUM( ) function, ALL from All/distinct menu & click on “Use Above Values “.

• Repeat the same procedure for attribute AMOUNT

Connect To the Target Stage Table

• Connect the output attributes of the aggregator operator to the target input

2/4/2014Private & Confidential

37

2/4/2014Private & Confidential

38

2/4/2014Private & Confidential

39