How To Change Slowly Changing Dimension Behavour in All Columns at a time other than Column=>Description tab

How To Change Slowly Changing Dimension Behavior in All Columns at a time other than Column=>Description tab


One of my friend asked me this SCD  Behavior changes in ODI 11G.  12C SCD Behavor changes at column level is very easy. We can Open table and change at column level directly. But in 11G We have to go for individual column and Description tab and need to change SCD Behavior.

In He project almost all  Dimension tables having  more than 200 Columns. It is very difficult to go to individual column=>description Tan and changing the SCD Behavior.

Here is an example for SCD Behavor changes at Repository Table Level.

Step1:  All Your Model tables we can find Work Repository Tables.

SNP_TABLE:  All your tables
SNP_COL: All columns

Step2:  Using Below query we can find the all columns for SCD2 table.

Default SCD_COL_TYPE value will be null.  Whenever we are changing the Column SCD behavior it will update the value in this column as below values.

SCD_COL_TYPE	SCD Meaning
SK	Surrogate Key
NK	Natural Key
OC	Overwrite On Change
IR	Add Row On Change
OC	Overwrite On Change
CR	Current Record Flag
ST	Starting Timestamp
ET	Ending Timestamp

SELECT   
SNP_COL.COL_NAME, 
SNP_TABLE.TABLE_NAME, 
SNP_COL.SCD_COL_TYPE
FROM SNP_COL col,
  SNP_TABLE tab
WHERE tab.i_table=col.i_table
AND table_name   ='SCD2';

Step3: SCD2 TABLE Structure:

 CREATE TABLE SCD2
  (
    DIM_EMP_ID        NUMBER,  ---  Surrogate Key                                    
    EMPNO                   NUMBER(4, 0) , ---- Natural Key                           
    ENAME                   VARCHAR2(10 BYTE) , -----Overwrite On Change                            
    JOB                          VARCHAR2(9 BYTE) ,--- Overwrite On Change                                      
    MGR                        NUMBER(4, 0) ,   ---- Overwrite On Change                                       
    HIREDATE             DATE ,                 --  Overwrite On Change                                       
    SAL                          NUMBER(7, 2) ,  ---  Add Row On Change                                       
    COMM                    NUMBER(7, 2) ,   ---   Add Row On Change                                    
    DEPTNO                 NUMBER(2, 0) ,  ----  Overwrite On Change                                        
    FLAG_STATUS     VARCHAR2(1 BYTE) ,---Current Record Flag                                            
    START_DATE        DATE , ---      Starting Timestamp                                        
    END_DATE            DATE ---       Ending Timestamp                        
  );


Using Below Query We can Change the Above SCD Behavior.

UPDATE SNP_COL
SET SCD_COL_TYPE='OC'
WHERE COL_NAME in ('ENAME','JOB','MGR','HIREDATE','DEPTNO')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');

COMMIT;

 UPDATE SNP_COL
SET SCD_COL_TYPE='IR'
WHERE COL_NAME in ('COMM','SAL')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');

COMMIT;

 UPDATE SNP_COL
SET SCD_COL_TYPE='SK'
WHERE COL_NAME in ('DIM_EMP_ID')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');

COMMIT;


 UPDATE SNP_COL
SET SCD_COL_TYPE='NK'
WHERE COL_NAME in ('EMPNO')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');

COMMIT;


 UPDATE SNP_COL
SET SCD_COL_TYPE='CR'
WHERE COL_NAME in ('FLAG_STATUS')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');

COMMIT;



UPDATE SNP_COL
SET SCD_COL_TYPE='ST'
WHERE COL_NAME in ('START_DATE')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');

COMMIT;

UPDATE SNP_COL
SET SCD_COL_TYPE='ET'
WHERE COL_NAME in ('END_DATE')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');

COMMIT;

Compare to 11G, 12C 12.1.2 or 12.1.3 very easy for changing SCD Behavior