When using the Dimension Merge SCD Transform, users begin by connecting two of the following:
Existing Dimension – This input provides data from a data warehouse's dimension table and must include the “housekeeping” columns (SCD2, effective, and expiry). Although optional, it is recommended users sort this input on the business key.
Source System - This input provides data from the source OLTP system needed to compare and insert/update to the data warehouse's dimension table. Source system and existing dimension datatypes must match. Although optional, it is recommended users sort this input on the business key.
Special Members – (Optional) In typical dimensions, special or “unknown” members may be used to link fact table rows to NULL or unknown dimension values. The special members input allows users to specify and maintain these "special members” in a data warehouse dimension table. (Special member rows should be treated as Type 1 changes to avoid duplicate versions of “unknown” values.) Like the previous two inputs, it is recommended the Special Members input also be sorted on the business key.