Records that are added to the source after the last update of the target dimension table do not have a corresponding current record in the target dimension table.
All source records are read using an appropriate Data Provider component. For a list of Data Provider components, see “Source components”.
All attributes that are transferred to the target dimension
table are read, although only the key attribute is required for
detection.
The existence of a corresponding current record in the target dimension is checked for each source record based on the source key attribute.
Choose an appropriate Lookup component. See “Lookup components”.
To perform calculations on the data to be transferred, consider using the lookup functionality of the Data Calculator component. See “Data Calculator JavaScript”.
Select the lookup data from target. As this is a simple existence check, only the original source keys are needed from all current target records. However, lookups in ETL always return a value for a key, so you must also select an appropriate return value.
Add an attribute to the port structure to populate the lookup result. The lookup result determines whether a source record is newly added or existed previously. This attribute indicates the data state and allows data to be filtered in the next step of the transformation process. See “Modifying port structures”. The new attribute is selected as the value attribute of a Lookup component or the output attribute in the Data Calculator.
Set an appropriate lookup default value. The default value is returned by the lookup for nonexisting keys. To ensure that the lookup value correctly indicates the existence of records, set it to a constant that is different from all lookup values for any existing keys.
Example:
Source data – select Key, Name, Price from PRODUCT
Lookup data – select Key, '1' from PRODUCT_PRICE where Valid_To = '9999-12-31'
Value attribute – Exists (integer)
Default value – 0
Performing this lookup results in records with attributes Key, Name, Price, Exists. The value of the Exists attribute will be 1(lookup value) for all records existing in target, and 0 (default value) for all nonexisting records.
