I sometimes need to modify one of my dimension, fact or model tables and re-target the source table in the field definitions.
It can be because the source name has changed or because it needs to be replaced by another table upstream. I must admit, I used to change them manually. It was pretty painful. Result: thousands of copy/pastes to do and the guarantee that I would forget a few fields. This was leading to endless procedure re-generations before it successfully compiled. When the table was holding a huge number of fields, this could rapidly turn into a nightmare.
It turns out that you don’t actually need to pass it to your trainee or your neighbor, or even try to do tricky things in the WhereScape RED metadata tables. A brilliant WhereScape RED option is ready to give you a hand. All you have to do is right click on your table in the left pane of WhereScape RED. Then, choose “re-target source”.
A dialog box will appear. The target table obviously is the table to modify. You need to set the primary source table, i.e. the source you want to modify. The two other dropdown lists will serve to map fields from target and new source tables.
Two modes are basically available. First one is “Auto Match”; you rely on WhereScape RED to find in the specified new source table the corresponded fields. For this mode, column names from new source and target must be the same. Click on the “Auto Match” button, press “OK”, the job is done!
The second option is do it manually. For every column of your table you can go and pick the new source from any table / field available in the metadata. This takes a bit longer but is very useful when the name of the column is to be changed over the two tables. Column renaming should nevertheless rarely occur over fact, dimension or model population.
Let’s conclude by a warning. This option will be very efficient but won’t replace source tables and fields in any transformation definition of the target. This is something that might be changed in a later version but generally, transformations are unlikely to be done anyway when populating fact, dimension or model tables.