Transactional Data and Change Capture
Posted: Tue Oct 04, 2011 5:09 pm
We are building a type 2 data warehouse where we expect multiple transactional updates from multiple source systems combined into one file by source system for end of day batch processing. The data architure is such that it is required to maintain each transaction history with the latest transaction as the only effective record and all others including historical transactions expired.
The problem I am trying resolve is to design the data integration process where one historical record can be compared to multiple input records in one data pass. A simple POC using the change capture stage produced the following:
Before Data
KEY|ChangeValue
P1|ABC123
After Data
KEY|ChangeValue|TransactionID
P1|DEF123|1
P1|ABC123|2
P1|XYZ789|3
The idea is to compare the record sets as whole rather than history to txn1, then txn1 to txn2, and finally txn2 to txn3. The expectation was the Change Capture would assign change code 3 to txn1 and txn3 and drop txn2. Then we would take the highest txnID (txnID3) and keep that effective and expire all others. This is not how it behaved.
Results of Change Capture Stage
KEY|ChangeValue|ChangeCode
P1|DEF123|3
P1|XYZ789|1
I am assuming the stage works in a way that after the first match it discards the old key and hence assigns a change code of 1 to txn3.
I may be overcomplicating the concept or have a completely wrong approach. I would sincerely appreciate anyone's feedback.
The problem I am trying resolve is to design the data integration process where one historical record can be compared to multiple input records in one data pass. A simple POC using the change capture stage produced the following:
Before Data
KEY|ChangeValue
P1|ABC123
After Data
KEY|ChangeValue|TransactionID
P1|DEF123|1
P1|ABC123|2
P1|XYZ789|3
The idea is to compare the record sets as whole rather than history to txn1, then txn1 to txn2, and finally txn2 to txn3. The expectation was the Change Capture would assign change code 3 to txn1 and txn3 and drop txn2. Then we would take the highest txnID (txnID3) and keep that effective and expire all others. This is not how it behaved.
Results of Change Capture Stage
KEY|ChangeValue|ChangeCode
P1|DEF123|3
P1|XYZ789|1
I am assuming the stage works in a way that after the first match it discards the old key and hence assigns a change code of 1 to txn3.
I may be overcomplicating the concept or have a completely wrong approach. I would sincerely appreciate anyone's feedback.