Context
Incremental Load in TimeXtender works great, until you hit relationships where the correctness of one table depends on a change in another.
Example: I have a header table and a lines table in my DSA. If the header changes (say the shipping address is updated), the lines need to be recalculated too, since the fact table references fields that are denormalized from the header onto the lines.
The current tool for this is Keep Field Values Up To Date. It resolves this with a FULL OUTER JOIN across all defined joins. That's not scoped per table or relationship, and it always runs at the end of the process. On a DSA with many tables this gets expensive fast. Especially with all the hashing that happens.
I could work around this in the ODX with a managed query, but I want to avoid pushing this kind of logic into the first layer. Keeping the ODX clean and letting the DSA handle this dependency feels like the right layer for it.
Proposal: cascading relationships
Let a relationship carry a setting: if either side receives an update, the other side is marked for recalculation and is included in the incremental load.
This is more subtle than reworking Keep Field Values Up To Date. The dependency is modeled once, directly on the relationship, and TimeXtender takes care of triggering reprocessing of the dependent table.
An alternative would be making Keep Field Values Up To Date configurable per table. The most lookup fields could be handled during the regular data cleansing phase. Only a few lookup-fields can be updated at the end with the full outer join. That would work too, but it's a heavier change to existing behavior and less elegant than modeling the dependency on the relationship.
Curious how others have handled this, and how the TimeXtender team thinks about extending incremental load for header/lines (or parent/child) dependencies.
