SCD Type 2 Update - CRTE_TSMP and UPDT_TSMP

View previous topic View next topic Go down

SCD Type 2 Update - CRTE_TSMP and UPDT_TSMP

Post  sachinh4u on Wed Sep 01, 2010 4:10 am

Data Loaded as below (CMPNY_ID & EFF_DT are PK)

CMPNY_ID EFF_DT CMPNY_NM STAT_C UPDT_TSMP CRTE_TSMP
2 2008-11-07 ABC D 2010-08-26-00.17.34.189507 2010-06-30-01.32.08.000000
34 2009-11-15 PQR D 2010-08-13-07.05.54.563109 2010-07-25-01.55.17.000000
993 2010-02-28 MNO A 2010-08-12-09.03.51.000000 2010-08-12-09.03.51.000000
993 2010-08-25 MNO1 A 2010-08-25-02.02.27.000000 2010-08-25-02.02.27.000000
2664 2009-10-07 XYZ A 2010-08-12-09.03.51.000000 2010-08-12-09.03.51.000000
2664 2010-08-25 XYZ1 A 2010-08-25-02.02.27.000000 2010-08-25-02.02.27.000000

Current Rules: following SCD type 2
RUn Date: 2010-08-25
Rule1: When first time record will come , will have EFF_DT same as value from soruce.
and CRTE_TSMP and UPDT_TSMP will be current job run time stamp.
Rule2: When CMPNY_NM changes(Updated record) , new record will be inserted with EFF_DT as 2010-08-25 and UPDT_TSMP & CRTE_TSMP will be value of current job run time stamp.
Rule3: For Delete Case, existing record STAT_C will be updated as 'D' and only UPDT_TSMP will update as Current time stamp. CRTE_TSMP will remain same as existing value.


My Query:
1) When update record will come, a new record should be inserted by updating only updt_TSMP as Current time stamp and CRTE_TSMP remain same as existing records value?
means :

CMPNY_ID EFF_DT CMPNY_NM STAT_C UPDT_TSMP CRTE_TSMP
2664 2009-10-07 XYZ A 2010-08-12-09.03.51.000000 2010-08-12-09.03.51.000000
2664 2010-08-25 XYZ1 A 2010-08-25-02.02.27.000000 2010-08-12-09.03.51.000000

OR above process is correct ?

Kindly adivce


Thanks in advance.
Sachin H
avatar
sachinh4u

Posts : 5
Join date : 2010-08-30

View user profile

Back to top Go down

Re: SCD Type 2 Update - CRTE_TSMP and UPDT_TSMP

Post  ngalemmo on Wed Sep 01, 2010 1:45 pm

This is not the way a type 2 dimension is implemented. The PK is a single unique surrogate key value, not a compound key.

As for how to update the dates, that depends on what the dates are supposed to represent. For example, if UPDT_TMSP is the timestamp when the row is updated, then whenever you update the row, you set the value...
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD Type 2 Update - CRTE_TSMP and UPDT_TSMP

Post  sachinh4u on Thu Sep 02, 2010 7:03 am

Thanks for Reply.

In My case CUST_ID and EFF_DT , both are part of primary key(Compund Key).
Requerst for advice on
1) CRTE_TSMP and UPDT_TSMP values when record comes first time to warehouse.
2) CRTE_TSMP and UPDT_TSMP values when udpated record comes to warehouse

Thanks in advance
avatar
sachinh4u

Posts : 5
Join date : 2010-08-30

View user profile

Back to top Go down

Re: SCD Type 2 Update - CRTE_TSMP and UPDT_TSMP

Post  LAndrews on Thu Sep 02, 2010 1:08 pm


As ngalemmo said, the primary key of your dimension should be a surrogate key.

I'm assuming Cust_ID and EFF_DT are the primary key from the source system?

As for the date question:

CRTE_TSMP : Contains the timestamp when the dimension record is created by the ETL process (either new or type-2 change).
UPDT_TSMP : Contains the timestamp when the dimension record is updated by the ETL process (e.g. type 1 change)

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: SCD Type 2 Update - CRTE_TSMP and UPDT_TSMP

Post  sachinh4u on Fri Sep 03, 2010 2:35 am

Thanks for extened information
In this case. We are using Type2. Concern is about only CRTE_TSMP and UPDT_TSMP
Current scenario.
1) When first time record comes to warehouse, CRTE_TSMP and UPDT_TSMP will have same value.
2) When Update record comes to warehouse, CRTE_TSMP and UPDT_TSMP will have same value.
(No difference between CRTE_TSMP adn UPDT_TSMP value in updated record)
Ex:
CMPNY_ID EFF_DT |CMPNY_NM |STAT_C |UPDT_TSMP| CRTE_TSMP
993|2010-02-28| MNO |A |2010-08-12-09.03.51.000000| 2010-08-12-09.03.51.000000
993|2010-08-25|MNO1| A |2010-08-25-02.02.27.000000| 2010-08-25-02.02.27.000000

My point of view is
When record comes as updated record to warehouse. Only UPDT_TSMP need to be changed as Job run time stamp but CRTE_TSMP should remain same as old value(when record come very first time to warehouse)
Ex:
CMPNY_ID| EFF_DT| CMPNY_NM |STAT_C |UPDT_TSMP| CRTE_TSMP
993 |2010-02-28 |MNO |A |2010-08-12-09.03.51.000000 |2010-08-12-09.03.51.000000
993 |2010-08-25 |MNO1 |A |2010-08-25-02.02.27.000000 |2010-08-12-09.03.51.000000
993 |2010-08-30 |MNO2 |A |2010-08-30-12.12.10.000000 |2010-08-12-09.03.51.000000

According to me
CRTE_TS represents time stamp when record is created in warehosue(functionally, not db perspective).
UPDT_TS represents time stamp when update record entered in warehouse.

kindly advice me, my openion is correct or wrong.

Thansk in advance
Sachin
avatar
sachinh4u

Posts : 5
Join date : 2010-08-30

View user profile

Back to top Go down

Re: SCD Type 2 Update - CRTE_TSMP and UPDT_TSMP

Post  ngalemmo on Fri Sep 03, 2010 11:48 am

I guess you could use the create timestamp that way, but it doesn't give you much information.

But the point LAndrews and myself are trying to make is, what you propose is not how you implement a type 2 dimension. You do not implement dimensions with compound primary keys. Also, all you have is an effective date... how are you supposed to efficiently join to the dimension? Are you carrying the both columns as FKs in the fact? And if you want the current dimension row, how do you find it? With a correlated sub-query in the WHERE clause?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD Type 2 Update - CRTE_TSMP and UPDT_TSMP

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum