Slowly Changing Dimension Type 1 (SCD 1) exercise 2 solution insert and update
Download as PPTX, PDF
0 likes275 views
The document describes steps to perform an extract, transform, load (ETL) process using Pentaho Spoon to handle a slowly changing dimension (SCD) type 1 scenario. It involves: 1) Creating and populating source and dimension tables in an Oracle database 2) Using Pentaho Spoon to extract from the source table, transform by inserting/updating the dimension table 3) Testing the ETL process by inserting a new record into the source and updating a record
Convert to study guideBETA
Transform any presentation into a summarized study guide, highlighting the most important points and key insights.
Slowly Changing Dimension Type 1 (SCD 1) exercise 2 solution insert and update
- 1. SLOWLY CHANGIN DIMENSION 1 (SCD1)- Exercise 2 Insert & Update Dr.Girija Narasimhan5/19/2018 1 https://www.oercommons.org/authoring/28693-etl-using-pentaho-spoon-lecture-notes-ppt-exercise
- 2. Dr.Girija Narasimhan5/19/2018 2 Create staff_s1 base table , staff_dim_scd1 table in oracle xe and insert two records in both the table. Using Spoon, include table input connect staff_s1 table insert/update tag include staff_dim_scd1 dimension table, check the execution result. Insert into staff_s1(13,Rahma,2800); record in the staff_s1 table, check the result in spoon Display staff_dim_scd1 dimension table newly inserted row details are included in oracle xe. Update the record update staff_s1 set bonus=2200 where sid=11; and execute the transformation Display the staff_dim_scd1 and staff_s1 table in oracle xe. Exercise 2 Slowly Changing Dimension (SCD1)
- 3. Dr.Girija Narasimhan5/19/2018 3 Create staff_s1, staff_dim_scd1 table in oracle_xe insert two records in both the table. Step 1: create table staff_s1(sid number(5) not null,sname varchar2(15),bonus number(5)); insert into staff_s1 values(11,'Abeer',2000); insert into staff_s1 values(12,'Sohiab',2500); select * from staff_s1; Ans:
- 5. create table staff_dim_scd1(dim_sid number(5) not null, sname varchar2(15),bonus number(5)); insert into staff_dim_scd1 values(11,'Abeer',2000); insert into staff_dim_scd1 values(12,'Sohiab',2500); select * from staff_dim_scd1; Dr.Girija Narasimhan5/19/2018 5
- 6. Dr.Girija Narasimhan5/19/2018 6 Using Spoon, include table input connect staff_s1 table Step 2:
- 8. Dr.Girija Narasimhan5/19/2018 8 Execute the Transformation
- 9. Dr.Girija Narasimhan5/19/2018 9 Step 3: insert/update tag include staff_dim_scd1 dimension table, check the execution result.
- 10. Dr.Girija Narasimhan5/19/2018 10 Step 4: Execute the Result
- 12. Dr.Girija Narasimhan5/19/2018 12 SLOWLY CHANGIN DIMENSION 1 SCD1 Insert
- 13. Dr.Girija Narasimhan5/19/2018 13 insert into staff_s1 values(13,'Rahma',2800); Step 5: Insert below given record in the staff_s1 table, check the result in spoon
- 14. Dr.Girija Narasimhan5/19/2018 14 Step 6: Display staff_dim_scd1 dimension table newly inserted row details are included in oracle xe.
- 16. Dr.Girija Narasimhan5/19/2018 16 Check inserted newly record is available in staff_dim_scd1 in Oracle_xe
- 17. Dr.Girija Narasimhan5/19/2018 17 SLOWLY CHANGIN DIMENSION 1 SCD1 UPDATE
- 18. Dr.Girija Narasimhan5/19/2018 18 Step 7 : update staff_s1 set bonus=2200 where sid=11;
- 19. Dr.Girija Narasimhan5/19/2018 19 Step 8: In the lookup up the value assign only key fields and other field of the table to be assign in the update fields
- 21. Dr.Girija Narasimhan5/19/2018 21 Check the updated record information in the staff_dim_scd1 table in oracle_xe