ODI Business Rules

Document: Oracle Data Integrator 11g (11.1.1.5) ODI Business Rules Implementation

Description: (Thiss Do (Thi Docu cume ment nt ex expl plai ains ns th the e ba basi sicc ET ETL L Ru Rule le im impl plem emen enta tati tion on us usin ing g sequences and User functions with mapping tables.)

History: Version

Description Change

Author

Publish Date

0.1

Initial Draft

Gourav Atalkar

24-Aug-2011

0.1

Review 1st

Amit Sharma

24-Aug-2011

Busi Busine ness ss Inte Intell llig igen ence ce Solu Soluti tion onss Prov Provid ider erss

|

ODI ODI Busi Busine ness ss Rule Rule impl implem emen enta tati tion onss

1

Table of Contents 1)

List of busin business ess rules to implemen implementt 2)

3

Introduction Introdu ction to source databa database se Table

9

3)

Target Table detail detailss

4

4)

Target to Source table Mapping wit h bus busine iness ss rule ruless

6

5)

Creating Creati ng sequenc sequences es for generat generating ing identi identity ty column

10

6)

Creating User Function for generating City Type code, Weekend Flag, Fiscal Quarter and Fisca Fiscall Month

15

7)

Creating Creati ng interfa interface ce for loadin loading g the Time dimens dimension ion data

19

8)

Creating Creati ng interfa interface ce for loadin loading g the Store dimens dimension ion data

24

9)

Creating Creati ng interfa interface ce for loadin loading g the Product dimens dimension ion data

30

Creating Creatin g interfac interface e for loadin loading g the fact table data

34

10)


|


2

1) List of business rules to implement : 

Split record and store in different table.



Creating Sequences for adding key column to all dimension d imension tables.



In All dimension insert following fields 

Created By



Created Date



Update By



Update Date



Add weekend flag to time dimension table : Sat/Sun/Others



Add other Date column to target table time dimension



Add fiscal year, Fiscal month and fiscal quarter to time dimension



Create custom store code [city(3) + store code]



Order the store dimension by postal code.

For implementing these all requirement we have one source database table inside MS SQL server 2000 and target tables we have created inside the oracle 11g according distribution of the columns from the source. Below is the description about the source and target tables

2) Introduction to source database Table:


|


3

2)

Target Table details :

Time Dimension:

Store Dimension:


|


4

Product Dimension:

Fact Table:


|


5

3)

Target to Source table Mapping with business rules:

Time Dimension:

Target Table: Time Dimension

Source Table: All_ records S No .

S No .

Column Name

1 2

Time_id year

VARCHAR2(35 BYTE)

Sequence Direct Mapping

1 2

incremental year

3

Quarter

VARCHAR2(35 BYTE)

Direct Mapping

3

Quarter

4 5

Month_number Month_name

NUMBER(35,0) VARCHAR2(35 BYTE)

Direct Mapping Direct Mapping

4 5

Month_number Month_name

6

Day_name

VARCHAR2(35 BYTE)

Direct Mapping

6

Day_name

7

Created_by

VARCHAR2(35 BYTE)

7

Genrated

8 9

Created_date Updated_by

VARCHAR2(35 BYTE)

8 9

Genrated Genrated

10 11

Updated_date Other_date

10 11

Genrated Genrated

Data Type Number(38,0)

DATE

DATE

Derivation Logic

SYSDATE

SYSDATE

DATE


|

Column Name


Data Type Number(38,0) VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) NUMBER(35,0) VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) DATE VARCHAR2(35 BYTE) DATE DATE

6

12

Weekend_Flag

VARCHAR2(35 BYTE)

13

Fiscal_year

VARCHAR2(35 BYTE)

14

Fiscal_Qtr

VARCHAR2(35 BYTE)

CASE WHEN Day_name='Sunday' then 'SUN' WHEN Day_name Day_name='Satu ='Saturday' rday' then 'SAT' 'SAT' ELSE 'OTHERS' END

CASE WHEN Month_name=’Apr' then 'Q1' WHEN Month_name=’May' then 'Q1' WHEN Month_name=’Jun' then 'Q1'

12

Genrated

VARCHAR2(35 BYTE)

13

Genrated

14

Genrated

VARCHAR2(35 BYTE) VARCHAR2(35 BYTE)

15

Genrated

VARCHAR2(35 BYTE)

16

Day_code

NUMBER(35,0)

WHEN Month_name=’Jul' then 'Q2' WHEN Month_name=’Aug' then 'Q2' WHEN Month_name=’Sep ' then 'Q2' WHEN Month_name=’Oct' then 'Q3' WHEN Month_name=’Nov' then 'Q3' WHEN Month_name=’Dec' then 'Q3'

15

Fiscal_month

VARCHAR2(35 BYTE)

WHEN Month_name=’Jan' then 'Q4' WHEN Month_name=’Feb' then 'Q4' WHEN Month_name=’Mar' then 'Q4' ELSE 'NULL' END CASE WHEN Month_name=’Apr' then 'month 1' WHEN Month_name=’May' then 'Month 2' WHEN Month_name=’Jun' then 'Month 3' WHEN Month_name=’Jul' then 'Month 4' WHEN Month_name=’Aug' then ' Month 5' WHEN Month_name=’Sep ' then then ' Month 6' WHEN Month_name=’Oct' then ' Month 7' WHEN Month_name=’Nov' then ' Month 8' WHEN Month_name=’Dec' then then ' Month 9' WHEN Month_name=’Jan' then 'Month 10' WHEN Month_name=’Feb' then 'Month 11' WHEN Month_name=’Mar' then 'Month 12' ELSE 'NULL' END

16

Day_code

NUMBER(35,0)

Direct Mapping

Store Dimension:

Target Table: Store Dimension S No .

Column Name

Source Table: All_ records


Derivation Logic

S No .

Column Name

VARCHAR2(35 BYTE)


1 2

incremental Region

Territory

VARCHAR2(35 BYTE)

Direct Mapping

3

Territory

4 5

Country Store_type



4 5

Country Store_type

6

Store_name

VARCHAR2(35 BYTE)

Direct Mapping

6

Store_name

7

Street address

VARCHAR2(35 BYTE)

Direct Mapping

7

Street address

8

City

DATE

Direct Mapping

8

City

9

State

VARCHAR2(35 BYTE)

Direct Mapping

9

State

10

Postal Code

VARCHAR2(35 BYTE)

Direct Mapping

10

Postal Code

1 2

Store_id Region

3


|


Data Type Number(38,0) VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) NUMBER(35,0) VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) DATE VARCHAR2(35 BYTE) DATE

7

11 12

Created_By Created_date

VARCHAR2(35 BYTE)

name

DATE

13

Update_by

14

SYSDATE

11 12

Genrated Genrated

VARCHAR2(35 BYTE)

name

13

Genrated

Updated_date

DATE

SYSDATE

14

Genrated

15

Custo ustom m_sto store_c e_code

VARCHA VARCHAR2( R2(35 35 BYTE BYTE))

CONCAT CONCAT(SU (SUBST BSTR(F R(FRI. RI."Co "Count untry" ry",1, ,1,3), 3),FRI FRI." ." Store Name")

15

Genrated

16

Store_code

NUMBER(35,0)

Direct Mapping

16

Store_code

DATE VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) NUMBER(35,0)

Product Dimension:

Target Table: Product Dimension S No .

Column Name

1 2

Product_id Product_Line

3



Derivation Logic

S No.

Column Name


VARCHAR2(35 BYTE)


1 2

incremental Product_Line

Product Family

VARCHAR2(35 BYTE)

Direct Mapping

3

Product Family

4 5

Product Category Product Name



4 5

Product Category Product Name

6

Product Pu Publisher

VARCHAR2(35 BYTE)

Direct Mapping

6

Product Pu Publisher

7

Created By

VARCHAR2(35 BYTE)

Direct Mapping

7

Created By

8

Created Date

DATE

Direct Mapping

8

Created Date

9

Updated By

VARCHAR2(35 BYTE)

Direct Mapping

9

Updated By

10

Updated Date

DATE

Direct Mapping

10

Updated Da Date

VARCHAR2(35 BYTE) DATE

11

Product_code

Number

Direct Mapping

11

Product_code

Number

VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) NUMBER(35,0) VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) DATE

Fact Table:

Target Table: Product Dimension S No .

Column Name


Data Type

Derivation Logic

S No.

Column Name

Data Type

1

Product_code

Number(38,0)

Sequence

1

incremental

Number(38,0)

2

Day_code

VARCHAR2(35 BYTE)

Direct Mapping

2

Product_Line

3

Store_code

VARCHAR2(35 BYTE)

Direct Mapping

3

Product Family

4

Unit Sales

NUMBER(35,0)

Direct Mapping

4

Product Category

VARCHAR2(35 BYTE) VARCHAR2(35 BYTE) NUMBER(35,0)

5

Amount Sales

VARCHAR2(35 BYTE)

Direct Mapping

5

Product Name

VARCHAR2(35 BYTE)

Creating Sequence for adding unique id to time, store and product dimension :

4)


|


8

Step: 1) Create and execute an ODI procedure that creates an Oracle native sequence.

If not connected, connect to the Work Repository work repository (User: SUPERVISOR, Password: SUNOPSIS). Click the Designer tab.

Step: 2) In the Projects tab, expand: Project > DATA WAREHOUSE PROJECT. Right-click Procedures and and select select New New Proce Procedur dure. e. Name Name the new proced procedure ure:: Genera Generate_ te_id_ id_pro produc duct. t. Set the Targe Target t technology to Oracle, as shown next. Click the Detail tab.

Step Step:: 3) In the the Deta Detail ilss tab, tab, sele select ct the the Add Add comm comman and d butt button on.. In the the Name Name fiel field, d, ente enter: r: Generate_id_product. In the Command on target tab, set the Technology field to Oracle. In the Schema drop-down list, select Orders. In the Command panel, enter the command, which creates the following sequence. Click the Save icon to save the procedure. Close the tab.


|


9

Step: 4) Run the procedure Generate_id_product Generate_id_product and verify the result of the execution in Operator.


|


10

Step: 5) Open SQL Developer and expand: Administrator > Other Users > Orders > Sequences. Verify that your sequence SEQ_ PRODUCT_ID was created in RDBMS.

Step: 6) create a new Native Sequence in ODI.


|


11

Open Designer. Click the Projects tab and expand: Project > DATA WAREHOUSE PROJECT. Rightclick Sequences and then select New Sequence.

Step: 7) Name the new sequence PRODUCT_SEQ. Select the Native Sequence check box. In the schema filed, select ORDERS. Click the browse icon. In the window that follows, select the GLOBAL context, and then select the native sequence SEQ_Product_ ID. Click OK. Your screen should look as shown next. Save the sequence, and then close the t he tab.


|


12

Same way we have to create other to sequences also.

Creating Creati ng Use User r Fun Functi ction on for gen genera eratin ting g Cit City y Typ Type e cod code, e, Wee Weeken kend d Fla Flag, g, Fiscal Quarter and Fiscal Month :

5)


|


13

Step: 1) Select project tab right click on user function and select new user function. In definition tab

name box specify the name of user function group box specify the group of user function in syntax box specify the user function and parameter.

Step: 2) now go to the implementation tab select add button. In the implementation syntax box insert

the logic for converting the country in different types. Select the technology to which you want to implement this code. CASE WHEN $1 ='USA' then 'Type A' WHEN $1='Australia' then 'Type B' WHEN $1= 'JAPAN' 'JAPAN' then 'Type B' WHEN $1= 'Germany' then 'Type B' WHEN $1= 'Ireland' then 'Type B' WHEN $1= 'France' then 'Type B' WHEN $1='United Kingdom' then 'Type C' WHEN $1='Brazil' then 'Type C' WHEN $1= 'Norway' then then 'Type C' WHEN $1= 'Sweden' then then 'Type C' WHEN $1= 'Canada' then then 'Type C' ELSE NULL END

Step: 3) Now we will create one more user function for adding weekend flag


|


14


the logic for generating weekend flag from the days. CASE WHEN $1 ='Sunday' then 'SUN' WHEN $1=‘Saturday’ then 'SAT' ELSE 'OTHERS' END

Step: 5) Creating Fiscal Quarter User function


|


15

Step: Step: 6) now go to the implementation tab select add button. In the implementation syntax box insert

the logic for converting the Month in different Quarters. CASE WHEN $1 ='April' then 'Qtr1' WHEN $1='May' $1='May' then 'Qtr1' WHEN $1='June' $1='June' then 'Qtr1' 'Qtr1' WHEN $1='July' $1='July' then 'Qtr2' 'Qtr2' WHEN $1='August' $1='August' then 'Qtr2' 'Qtr2' WHEN $1='September' $1='September' then 'Qtr2' WHEN $1='October' then 'Qtr3' WHEN $1='November' $1='November' then 'Qtr3' WHEN $1='December' $1='December' then 'Qtr3' WHEN $1='Januar $1='January' y' then 'Qtr4' WHEN $1='February' $1='February' then 'Qtr4' 'Qtr4' WHEN $1='March' $1='March' then 'Qtr4' ELSE 'OTHERS' END

Step: 6) Creating Fiscal Month User function


|


16


the logic for converting the Month in different Fiscal month. CASE WHEN $1 ='April' then 'month1' WHEN $1='May' $1='May' then 'month2' 'month2' WHEN $1='June' $1='June' then 'month3' 'month3' WHEN $1='July' $1='July' then 'month4' 'month4' WHEN $1='August' $1='August' then 'month5' 'month5' WHEN $1='September' $1='September' then 'month6' 'month6' WHEN $1='October' then 'month7' WHEN $1='November' $1='November' then 'month8' WHEN $1='December' $1='December' then 'month9' 'month9' WHEN $1='January' $1='January' then 'month10' 'month10' WHEN $1='February' $1='February' then 'month11' 'month11' WHEN $1='March' $1='March' then 'month12' 'month12' ELSE 'OTHERS' END

6)

Creating Interface for loading Time dimension data:

Step: 1) Select interface tab in project and right click and new interface specify the name of the interface in definition tab and move to mapping tab.


|


17

Step: 2) drag and drop the source and target tables from the model tab.

We need to map source and target column according mapping tables.

Select the first column in target table and select Expression editor button open it and specify the sequence for generating primary key column.


|


18

Need to map other target column with user functions


|


19

Similarly we need to place other user function and parameter in the target column.

Specify the proper IKM and set up the property according requirement requirement


|


20

Now we will execute it and load the data to target table time.

Result:


|


21

7)

Creating Interface for loading Store dimension data:


|


22




Select the first column in target table and select Expression editor button open it and specify the sequence for generating primary key column.


|


23

Need to map other target column with user functions first we will map custom store code where we have the first three character of country and store name.


|


24

Now we will map Country type column by previously created user function col_type


|


25

After After mappin mapping g all column columnss succes successfu sfully lly we will will move move to flow flow tab for specif specifyin ying g the loadin loading g knowledge module and integrating knowledge module.

Specify the proper IKM and set up the property according requirement requirement

Now we will execute it and load the data to target table time


|


26

Result:


|


27

8)

Creating Interface for loading Product dimension data:




|


28


Select the first column in target table and select Expression editor button open it and specify the sequence for generating primary key column


|


29

Change the execution location and the key check box and not null box need to check.

Need to map other target column with source according to mapping table.


|


30

Go to flow tab and specify the proper knowledge modules

Now we will execute it


|


31

Result:

9)Creating Interface for loading Fact table data:


|


32





|


33

Go to flow tab and specify the proper knowledge modules

Now we will execute it


|


34

Result:


|


35

ODI Business Rules

Recommend Documents