I have to modify my table PRODUCT_DIM so that I can be able to store historical information according to SCD2
A Type 2 SCD retains the full history of values. When the value of a chosen attribute changes, the current record is closed. A new record is created with the changed data values and this new record becomes the current record. Each record contains the effective time and expiration time to identify the time period between which the record was active.
So in my original script that I created I have:
CREATE TABLE PRODUCT_DIM ( PRODUCTKEY integer NOT NULL, PRODUCTID integer, PRODUCTDESCRIPTION VARCHAR2(50 BYTE), PRODUCTLINEID integer, PRODUCTLINENAME VARCHAR2(50 BYTE), CONSTRAINT PRODUCT_DIM_PK PRIMARY KEY (PRODUCTKEY) ); CREATE SEQUENCE PRODUCT_KEY_SEQ MINVALUE 1001 START WITH 1001 INCREMENT BY 1 CACHE 25; INSERT INTO PRODUCT_DIM (PRODUCTKEY, PRODUCTID, PRODUCTDESCRIPTION, PRODUCTLINEID, PRODUCTLINENAME) SELECT PRODUCT_KEY_SEQ.NEXTVAL, nvl(to_char(p.PRODUCTID), 'Undefined'), nvl(to_char(p.PRODUCTDESCRIPTION), 'Undefined'), nvl(to_char(p.PRODUCTLINEID), 'Undefined'), nvl(to_char(pl.PRODUCTLINENAME), 'Undefined') FROM PRODUCTLINE_T pl, PRODUCT_T p WHERE p.PRODUCTLINEID = pl.PRODUCTLINEID;
Then I know I have to create a new date for when the data was stored:
ALTER TABLE PRODUCT_DIM ADD DATE_FROM DATE DEFAULT '21-NOV-17';
And then I created a new column with a new date for when the data was updated/deleted:
ALTER TABLE PRODUCT_DIM ADD DATE_THRU DATE;
But then I don’t know what to do next or if I even started correctly?