ETL Using Pentaho(Spoon)

Introduction

This type will create a duplicate records with necessary changes without disturbing the history. For example if you update any record information, it will keep both old record information plus old record information.

It maintains full history in the target. It maintains history by inserting the new record and updating for each change.  It keep version number, date from and date to changes occurred

Step 1: Create table in oracle-xe

create table student_scd2(sid number(5) ,sname varchar2(15),mark1 number(5));

insert into student_scd2 values(11,'Ambu',89);

insert into student_scd2 values(12,'Meenu',75);

insert into student_scd2 values(13,'Jasica',45);

select * from student_scd2;

create_table_student_scd2

Step 2: create Student_dim_scd2 dimension table

create table student_dim_scd2(stu_sur_key number(5),date_from date, date_to date, version number(4), dim_sid number(5), sname varchar2(15),mark1 number(5));

Step 3:  Open the Spoon and start the New transformation

Pentaho transformation

Step 3: Insert the "table Input" 

Table Input

Step 4: Edit Table Input

Edit table input

Step 5: Connect the Database "XE"

Connect database XE

Step 6: Execute the table input in the Spoon

Table exeuction

Step 7: Include the Dimension lookup/update

insert dimension update

Step 8:Edit the Dimension Lookup/Update

Edit dimension lookup

Step 9:  In the Edit, select keys and Fields option

Select key field

Step 10: Run the transformation and check the student_dim_scd2 dimension in the database all the rows are updated or inserted.

Check the oracle Xe dimension table

Step 11:  Update the sid 11, mark as “95”

student_scd2

Step 12:  Execute the transformation and check the updation in the student_scd2 table

Update Execution Result

Step 13: Check the updated history in student_dim_scd2 dimension table in oracle xe

Updated history in the dimension table

Exercise

Create table employ2(eid number(5),ename  varchar2(15),salary number(5));

insert into employ2 values(1001,'Alaa',1800);

 insert into employ2 values(1002,'Ravi',1500);

select * from employ2;

create table emp_dim_scd2(emp_sur_key number(5),date_from date,date_to date,version number(6),dim_eid number(5),ename varchar2(15),salary number(5));

Instruction 1:

•Create employ2, emp_dim_scd2 table and insert two records in oracle_xe.

• Using Spoon, include “table input” connect employ2 table,

•include “dimension lookup/update”  tag connect emp_dim_scd2 dimension table, run the transformation and execute the result

• Check the emp_dim_scd2 records in the Oracle xe

•  update the employ2 table eid 1001 salary is 2500, again execute the result in spoon

•Display emp_dim_scd2 dimension table with updated history details are included.

instruction 2:

• Insert the below given record in the employ2 table

insert into employ2 values(1003,'Suha',1900);

•   execute the result in spoon

•Display emp_dim_scd2 dimension table with updated history details are included.