How to implement SCD to update and maintain salary and dept of employee

Question

I want to capture change in dept of employee and update salary using plsql implementation of scd. Please suggest a solution

Tried only scd1 but expecting to maintain history of department There should be new record if department changes .also which scd to be implemented if salary changes

Answer 1

Don't use the term SCD (slowly changing dimension) as it isn't a universally understood term, nor is it very exact. There are multiple styles of SCDs or CDC logic out there. You probably are thinking of a type-2 table common in data warehouses which historically versions rows. Even then there are a number of variations on type-2 tables. Whether they have a current flag or not, how an unexpired row's end date is represented, whether the end date is equal to the start date of the next row or 1 second or 1 day behind it, how logical deletes are represented, etc. etc.

You can do type-2 maintenance optimally with a single merge that combines the expiration of old rows and creating of new rows in one operation (logical deletes would require a second merge). But that's a bit advanced. A simpler method better to start with for lower volumes would be row-by-row PL/SQL.

Taking the simplest case and oversimplifying it (this doesn't handle deletes, or nullable columns, etc., and a handful of other details you'd need to consider, but it gives you the general idea):

BEGIN
  FOR rec_data IN (SELECT n.pkcol,
                          n.col1 new$col1,
                          n.col2 new$col2,
                          n.col3 new$col3,
                          o.col1 old$col1,
                          o.col2 old$col2,
                          o.col3 old$col3,
                          o.ROWID row_id
                     FROM newdata n,
                          olddata o
                    WHERE n.keycol = o.keycol(+)) -- outer join old and new together
  LOOP
    IF rec_data.row_id IS NULL
    THEN
      -- the row is new. insert an unexpired row
      INSERT INTO olddata (pkcol,col1,col2,col3,startdate,enddate)
      VALUES (rec_data.pkcol,rec_data.new$col1,rec_data.new$col2,rec_data.new$col3,SYSDATE,NULL);

    -- test for column changes. Any nullable cols should be wrapped in NVL
    ELSIF NOT (rec_data.new$col1 = rec_data.old$col1 AND
               rec_data.new$col2 = rec_data.old$col2 AND
               rec_data.new$col3 = rec_data.old$col3)
    THEN
      -- the row is old but has changed. expire the old row by setting its end date and insert a new unexpired row
      UPDATE olddata
         SET enddate = SYSDATE
       WHERE ROWID = rec_data.row_id;

      INSERT INTO olddata (pkcol,col1,col2,col3,startdate,enddate)
      VALUES (rec_data.pkcol,rec_data.new$col1,rec_data.new$col2,rec_data.new$col3,SYSDATE,NULL);
    END IF;
  END LOOP;
END;