07-16-2015 09:57 PM
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.
PS: I am using SAS DI Studio v 4.7
07-21-2015 12:59 PM
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.
07-21-2015 07:02 PM
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.
Need further help from the community? Please ask a new question.