Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Require comprehensive test cases for SCD Type2

Go down

Require comprehensive test cases for SCD Type2 Empty Require comprehensive test cases for SCD Type2

Post  abhijit.singh2 Mon Jun 21, 2010 5:37 am

Hi, I have written the following code in Oracle for SCD Type2. SCD2 table name is employee_tgt and source table is employee_src. It maintains history of column emp_name. The primary key is emp_id and the surrogate key is emp_sk. The load is assumed to run daily.

I wish to use it as template for any dimension of Type2. However, I require someone to provide me with a comprehensive test data set so that I be sure that the code is correct.

Code:

CREATE SEQUENCE seq_employee_tgt MINVALUE 1 START WITH 1 INCREMENT BY 1 NOCACHE;

CREATE TABLE employee_tgt(
emp_sk NUMBER NOT NULL,
emp_id NUMBER not null,
emp_name VARCHAR2(10) not null,
active_flag CHAR(1),
start_date DATE DEFAULT SYSDATE not null,
end_date DATE not null
);

ALTER TABLE employee_tgt ADD CONSTRAINT PK_employee_tgt PRIMARY KEY (emp_sk);
ALTER TABLE employee_tgt ADD CONSTRAINT UK_employee_tgt UNIQUE (emp_id, start_date);

CREATE TABLE employee_src(
emp_id NUMBER not null,
emp_name VARCHAR2(10) not null,
record_date DATE
);

ALTER TABLE employee_src ADD CONSTRAINT PK_employee_src PRIMARY KEY (emp_id,record_date);

INSERT INTO employee_src (emp_id, emp_name, record_date) VALUES(100,'AKS','31-DEC-2009');
INSERT INTO employee_src (emp_id, emp_name, record_date) VALUES(101,'Singh','31-DEC-2009');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(99,'Kumar','1-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(100,'Abhijit','1-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(101,'Singh','1-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(102,'Abhishek','2-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(100,'Abhijit','2-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(101,'Si.','2-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(104,'Sharma','3-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(100,'Abhi.','3-JAN-2010');
INSERT INTO employee_src(emp_id, emp_name, record_date) VALUES(101,'Singh','3-JAN-2010');

SELECT * FROM employee_src;

SELECT * FROM employee_tgt;


CREATE OR REPLACE PROCEDURE sp_emp_scd2 (p_in_run_date IN DATE, p_out_retc_cd OUT NUMBER)
AS
BEGIN
--insert new employees and employees whose name has changed
INSERT INTO employee_tgt (emp_sk,emp_id, emp_name, active_flag, start_date, end_date)
SELECT seq_employee_tgt.nextval, emp_id, emp_name, 'Y', p_in_run_date, '9-SEP-9999'
FROM employee_src SS
WHERE ss.record_date=p_in_run_date
  AND (EXISTS (SELECT 1 FROM employee_tgt ST
                WHERE ST.emp_id=SS.emp_id
                      AND (ST.emp_name<>SS.emp_name /*OR any other field whose history is to be maintained*/)
                    AND st.active_flag='Y'
                    )
        OR NOT EXISTS (SELECT 1 FROM employee_tgt ST WHERE ST.emp_id=SS.emp_id)
      );

--old row of employees whose name has changed should be disabled
UPDATE employee_tgt ST SET active_flag = 'N', end_date = p_in_run_date - 1
WHERE EXISTS
(SELECT 1
FROM employee_src SS
WHERE ST.emp_id=SS.emp_id
AND (ST.emp_name<>SS.emp_name)
AND ss.record_date=p_in_run_date)
AND st.active_flag='Y';

COMMIT;
p_out_retc_cd := 0;

EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK;
  p_out_retc_cd := 1;
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

abhijit.singh2

Posts : 1
Join date : 2010-06-21

Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum