As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at firstname.lastname@example.org and provide your corporate email address, company, and name.
I've noticed inconsistencies in type 1 updates when both type 1 and type 2 changes occure in the same incoming row. When an incoming row only contains type 1 changes ALL versions of a logical key are updated. However, if the source has both type 1 and type 2 changes in the same incoming record, a new type 2 record is created, however the type 1 changes only occure in the newly created row.
You can reproduce this issue using the dmsurscd fex
A. Add the following filters on select columns icon to issolate on incoming row
SELECT DISTINCT T1.EMPID , --Employee ID T1.PLANT , --Store Name T1.SALESREP --Plant Location FROM dmsale T1 WHERE T1.EMPID = 'YY040' AND T1.PLANT = 'ORL'
B. In the target hard code values for fname = 'Deb' ; lname = 'Smith'; plant = ORL
C. Run the initial load: Result below
1 YY040 Deb ORL FL 1
D. Test incoming row only has type 1 changes by changing fname = ted and rerun: Results below
1 YY040 Ted ORL FL 1
E. Test incoming row only has type 2 changes by changing plant=RSE and rerun: Results below
1 YY040 Ted ORL FL 0 2 YY040 Ted RSE FL 1
F. Test incoming row has both type 1 and type 2 changes by changing fname = Sue and plant = ZZZ: Results below
G. Retest incoming row has only type 1 changes by changing fname=Lisa and rerun: Results below
1 YY040 Lisa ORL FL 0 2 YY040 Lisa RSE FL 0 3 YY040 Lisa ZZZ FL 1
Is this a bug. If not could someone explain why we would want/expect this difference in type 1 updates. I would expect type 1 changes to ALWAYS update all rows that match the incoming logical_key.This message has been edited. Last edited by: <Kathryn Henning>,
It's arguable whether Type I changes should apply all rows with a matching natural key or just the current one. But I agree that presence of type Type II changes in the row shouldn't affect that one way of the other.
We've reproduced your scenario here and we are working to address it.
Posts: 397 | Location: New York City | Registered: May 03, 2007
In fact, it would be great to be able to have an option to specify how you wanted your type one changes to behave. Update the active record only or 'punch through' and update all rows with a matching natural key.
Thanks for the looking into this issue and thanks for the update.