CDC on Source Table without Timestamp

[VishantBhat]

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

[LinusH]

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.

[VishantBhat]

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

[VishantBhat · Accepted solution]

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

Regards,

Vishant