MRDM,
The uniqueness [and non-standardness] of the task is that a) as you mentioned, you have no change indicator [which is not typical], and b) you want to do very specific non-SCD type of transformation [close the record, recording the end date as a change to the record, but not keep the history]. This makes the task *slightly* more challenging.
First off, the Update, Merge, and most of the other DI transformations in the SQL group (except Join) are for in-database work and only with certain RDBMS (and, as you noted, not ODBC). The proper SQL code to perform this task would be an UPDATE, but the only transformation we have to work with here is Join -- so we would end up hand-coding the solution (which you didn't want to do).
It could also be done in SAS Data Step fairly easily, but again this requires hand-coding.
So we have to convince DI Studio of the right thing to do. Here's how I accomplished it [I've attached an image of the flow].
- I added a SQL Join {xform #1} to simply add a column named CLOSE to the SOURCE table giving it the value 1.
- I added a SQL Join {xform #2} to join (a LEFT join of MASTER and SOURCE) ensuring that CLOSE flows through on the matching records. This effectively tags MASTER records as needed to be closed.
- I added a (very powerful!) Data Validation {xform #3} node and created a Custom Validation with:
condition: close=1
if condition is true:
is_current=0
record_end_date=datetime()
This does the transformations as you need them.
- I added a Table Loader {xform #4} writing out to the Master_Updated table (you could overwrite the MASTER if desired) so that I can drop the CLOSE column.
I'm sure there are other ways to accomplish this as well. But this *was* accomplished with only point-and-click and no hand-coding. Thats the nice thing about DI Studio, it usually gives you many different ways tasks can be accomplished.
Were you using DB2/Oracle/SQL Server/Sybase/Teradata, the Update transformation would (by far) be the correct way to go.
Steve
... View more
