topic Re: DWH SCD2 update in New SAS User

DWH SCD2 update

dhruvakumar — Wed, 23 Oct 2019 07:02:49 GMT

Dears,

As part of SCDTYPE2 implementation, we requried to update and delete the records.

For instance given attachement, :

Need to delete highlight one , please can you help on this request.

Thanks

Re: DWH SCD2 update

dhruvakumar — Wed, 23 Oct 2019 07:03:08 GMT

Re: DWH SCD2 update

PeterClemmensen — Wed, 23 Oct 2019 07:06:17 GMT

I don't see any attachment?

Re: DWH SCD2 update

dhruvakumar — Wed, 23 Oct 2019 07:09:12 GMT

Dears,

As part of SCDTYPE2 implementation, we requried to update and delete the records.

For instance given attachement, :

id	name	age	sex	O_obs	C_obs	DFINOBS
1	John	10	M	01Feb2019	01Jan2019	31Jan2019
1	John	10	M	01Feb2019	01Feb2019	31Dec9999
2	Mary	10	F	01Feb2018	01Jan2018	31Jan2018
2	Mary	10	F	01Feb2018	01Feb2018	31Dec9999
2	Mary	10	F	01Mar2018	01Feb2018	28Feb2018
2	Mary	10	F	01Mar2018	01Mar2018	31Dec9999

Need to delete highlight one , please can you help on this request.

Thanks

Re: DWH SCD2 update

dhruvakumar — Wed, 23 Oct 2019 07:09:48 GMT

sorry jst attached. Thanks

Re: DWH SCD2 update

PeterClemmensen — Wed, 23 Oct 2019 07:11:11 GMT

What is logic behind highlighting this obs? And what do you mean by 'highlight'? Is this for reporting purposes?

Please be more specific

Re: DWH SCD2 update

dhruvakumar — Wed, 23 Oct 2019 07:16:32 GMT

The highlighting obs should be deleted.

1-Feb-18

31-Dec-99

because we the same ID updated in 1-Mar -18.

id	name	age	sex	O_obs	C_obs	DFINOBS
1	John	10	M	1-Feb-19	1-Jan-19	31-Jan-19
1	John	10	M	1-Feb-19	1-Feb-19	31-Dec-99
2	Mary	10	F	1-Feb-18	1-Jan-18	31-Jan-18
2	Mary	10	F	1-Feb-18	1-Feb-18	31-Dec-99
2	Mary	10	F	1-Mar-18	1-Feb-18	28-Feb-18
2	Mary	10	F	1-Mar-18	1-Mar-18	31-Dec-99

Re: DWH SCD2 update

Kurt_Bremser — Wed, 23 Oct 2019 09:12:14 GMT

@dhruvakumar wrote:

The highlighting obs should be deleted.

1-Feb-18 1-Feb-18 31-Dec-99

because we the same ID updated in 1-Mar -18.

id name age sex O_obs C_obs DFINOBS

1 John 10 M 1-Feb-19 1-Jan-19 31-Jan-19

1 John 10 M 1-Feb-19 1-Feb-19 31-Dec-99

2 Mary 10 F 1-Feb-18 1-Jan-18 31-Jan-18

2 Mary 10 F 1-Feb-18 1-Feb-18 31-Dec-99

2 Mary 10 F 1-Mar-18 1-Feb-18 28-Feb-18

2 Mary 10 F 1-Mar-18 1-Mar-18 31-Dec-99

And what is the logical rule for that?

Re: DWH SCD2 update

Patrick — Wed, 23 Oct 2019 12:38:36 GMT

@dhruvakumar

With SCD2 you NEVER delete a record but you expire it.

Assuming ID is your generated key and Name is your business key: I can't even see a reason in the sample data you've posted why there should be two records. All the other columns which potentially could be under change tracking are the same. With SCD2 there should be only a single row in such a case.

With SCD2 you need to have either a VERSION column and for every single row with a change to columns under change tracking for the same business key you add a new version - or you have start and end date columns and if you add a new row (an update) you also change the end date of the previous version to the start date of the new version (or current start date -1 day if you wish).

So: You first need to fix your table and clearly define the cases for your load logic (new business key, change for existing business key, expiration of current record for a business key, reopening an previously expired record for a business key) before even asking HOW to technically implement things.