All Forums Data Modeling
Learneveryday 1 post Joined 08/15
13 Aug 2015
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.

MaheshJessy 26 posts Joined 12/10
30 Aug 2015

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          

You must sign in to leave a comment.