SCD Type 2 Implementation error
Posted: Mon May 24, 2004 9:54 pm
Hi All,
I am trying to implement SCD type 2, like following way. But the problem is, Record gets duplicated and can't add new record
I am doing like this.
1. Datas are comin from sequential file and giving the Surrogate Key by Surrogate Key Generator, then make look up table after reading from oracle table A, "Look up"ing with the sequential stream.
2. Look up keys are only unique in two fields out of 26 fields. If look up is not successful, reject link from look up and insert to the oracle table.
3. If look up successful, ie. two field datas are available in the input sequential stream, I am sending to the transformer. Applying the date conversion. By using oracle enterprise in UPSERT mode, I am updating the existing the end date (which is default one) and Current record flag to show the latest record in the historical storage. And inserting the new record.
My questions are;
a). how better I can capture the Changed Data capture or some other way to effective to apply change in the existing record and enter new one.
b). How can I apply condition for updating record "end date" change and current Flag setting in the Oracle Enterprise. (It won't accepting values other than "Orchestrate.inputvalues")
c). Input date is varchar, I am using "StringtoTimeStamp" conversion function(like StringToTimeStamp(string,format), it's giving error. I am giving the DSLink.Enddate in the place of string, format is same as in the DataStage help. I am getting error in the derivation. Why, Is there anything wrong. or better way to get TimeStamp.
I request anyone throw some light on this doubts.
Thank you very much in advance.
IOWAJAX
I am trying to implement SCD type 2, like following way. But the problem is, Record gets duplicated and can't add new record
I am doing like this.
1. Datas are comin from sequential file and giving the Surrogate Key by Surrogate Key Generator, then make look up table after reading from oracle table A, "Look up"ing with the sequential stream.
2. Look up keys are only unique in two fields out of 26 fields. If look up is not successful, reject link from look up and insert to the oracle table.
3. If look up successful, ie. two field datas are available in the input sequential stream, I am sending to the transformer. Applying the date conversion. By using oracle enterprise in UPSERT mode, I am updating the existing the end date (which is default one) and Current record flag to show the latest record in the historical storage. And inserting the new record.
My questions are;
a). how better I can capture the Changed Data capture or some other way to effective to apply change in the existing record and enter new one.
b). How can I apply condition for updating record "end date" change and current Flag setting in the Oracle Enterprise. (It won't accepting values other than "Orchestrate.inputvalues")
c). Input date is varchar, I am using "StringtoTimeStamp" conversion function(like StringToTimeStamp(string,format), it's giving error. I am giving the DSLink.Enddate in the place of string, format is same as in the DataStage help. I am getting error in the derivation. Why, Is there anything wrong. or better way to get TimeStamp.
I request anyone throw some light on this doubts.
Thank you very much in advance.
IOWAJAX