FSDLM and SCD type 2

Data Modeling

FSDLM and SCD type 2

We are implementing around 400 tables of the FSDLM (insurance extension).  Can anyone give insight into which table types in the FSDLM should be type 2?  Also what are the advantages/disadvantages of making all of them type 2 versus only some of them?

Thanks.

Tags (4)
1 REPLY
Enthusiast

Re: FSDLM and SCD type 2

Hi Team, Would you please help to maintain the SCD type-2 for my Table as this MERGE Statement is being worked for INSERT and UPDATE but not history.

MERAGE statement results as follows:

MERGE INTO TWO_WEEK_SPACE.EQUP_DIM  as tgt
using
(SELECT EID,ENAME,loc FROM TWO_WEEK_SPACE.SRC_TAB group by 1,2,3
) as src
on tgt.eID=src.EID
when not matched then
insert ( EID ,ENAMe,eloc,Effective_Start_Dt ,Effective_End_Dt)
values (src.EID ,src.ENAME ,src.loc,current_date,'2999-12-31')
when matched then
update set Effective_End_Dt=current_date-1
;

Surrogatekey| EID| ENAME| Effective_Date| End_Date

1|101|MAHESH|28-08-2015|2999-12-31--Record Will be gone

2|101|MAHESH|28-08-2015|29-08-2015--(Updated whenever new records comes)

I wanted to maintain the table history as it follows..

SCD-2

Surrogatekey| EID| ENAME| Effective_Date| End_Date

1|101|MAHESH|29-08-2015|30-08-2015--Old recod will be remain

2|101|MAHESH|31-08-2015|2999-12-31-->History will be maintained                

Would you please do the help as I stuck up here?      

Regards,

mahesh