topic Re: CDC on Source Table without Timestamp in SAS Data Management

CDC on Source Table without Timestamp

VishantBhat — Fri, 17 Jul 2015 01:57:57 GMT

Hi All,

Currently in our DWH we perform a complete extract of all data from the source tables in Staging and perform a SCD on it on a daily basis (I know this is what sucks). But I now want to extract just the changed records from the source table instead of extracting the entire data from the source and performing a complete refresh and re-build the whole DWH on a daily basis. My problem is that I don't have any TIMESTAMP on the source tables (mostly oracle tables) which give me any Idea of the change dates.

Can I perform a CDC on such a source which does not have a TIMESTAMP column on any of the source tables? I have never used a CDC transform before but was looking at DI studio General CDC, does this transform help in my case?

Any help on this would be helpful and appreciated.

Regards,

Vishant

PS: I am using SAS DI Studio v 4.7

Re: CDC on Source Table without Timestamp

LinusH — Tue, 21 Jul 2015 16:59:56 GMT

I haven't used the CDC transformations so this will be a bit theoretical.

DI Studio transformations generates code. It can't do magic. If you have no support in your source data for doing any clever CDC logic, I can't see that the transformations can help you.

Oracle is a capable database. It should be fairly easy to add a timestamp in your source table(s). The same goes for implementing a change table.

Re: CDC on Source Table without Timestamp

VishantBhat — Tue, 21 Jul 2015 23:02:45 GMT

Hi Linus,

Don't we all wish for an ETL tool which can do magic between the Source and Target and give us the required solution I just wanted to explore possibilities on CDC, but I guess there are none for my current requirements. Thanks for your answer though.

I might just go with a PROC COMPARE solution between the Source and current Staging datasets to do a CDC.

Or I will wait if someone else has any answer to this.

Regards,

Vishant

Re: CDC on Source Table without Timestamp

VishantBhat — Fri, 05 Aug 2016 00:58:12 GMT

Added the SAS DI Studio transform code as Solution which was my SAS Global forum 2016 Presentation.

Regards,

Vishant