SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

CDC on Source Table without Timestamp

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

CDC on Source Table without Timestamp

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


Accepted Solutions
Solution
‎08-04-2016 08:58 PM
New Contributor
Posts: 4

Re: CDC on Source Table without Timestamp

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

 

Regards,

Vishant

View solution in original post

Attachment

All Replies
Super User
Posts: 5,257

Re: CDC on Source Table without Timestamp

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.

Data never sleeps
New Contributor
Posts: 4

Re: CDC on Source Table without Timestamp

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 Smiley Happy 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

Solution
‎08-04-2016 08:58 PM
New Contributor
Posts: 4

Re: CDC on Source Table without Timestamp

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

 

Regards,

Vishant

Attachment
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 367 views
  • 1 like
  • 2 in conversation