ETL Using Pentaho(Spoon)

Created Feb. 7, 2024 by Girija Narasimhan

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
f2-1_create_table_student_scd2.jpgcreate_table_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
fig1-3_pentaho_transformation_biIvTXR.jpgPentaho transformation

Pentaho transformation

Step 3: Insert the "table Input" 

Table Input
fig1-4_table_input_1uVqEYs.jpgTable Input

Table Input


Step 4: Edit Table Input

Edit table input
fig1-5_edit_step_Sn9wZsL.jpgEdit table input

Edit table input


Step 5: Connect the Database "XE"

Connect database XE
fig1-6_database_connection_CfP7Kog.jpgConnect database XE

Connect database XE

Step 6: Execute the table input in the Spoon

Table exeuction
f2-2_table_execution.jpgTable exeuction

Table exeuction


Step 7: Include the Dimension lookup/update

insert dimension update
f2-3_insert_dimension_update.pnginsert dimension update

insert dimension update


Step 8:Edit the Dimension Lookup/Update

Edit dimension lookup
fig2-8_edit_dimension.jpgEdit dimension lookup

Edit dimension lookup

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


Select key field
fig2-9_select_key_field.jpgSelect key field

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
fig2-10_check_the_oracle_xe_dimension_table.jpgCheck the oracle Xe dimension table

Check the oracle Xe dimension table

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


student_scd2
fig2-11_update_the_base_table_student_scd2.jpgstudent_scd2

student_scd2

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

Update Execution Result
fig2-12_execute_result.jpgUpdate Execution Result

Update Execution Result

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

Updated history in the dimension table
fig2-13-update_history_storing_in_student_dim_scd2.jpgUpdated history in the dimension table

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.











Return to top