Records that are modified in the source after the last update of the target dimension table have a corresponding current record in the target dimension table, but the relevant values are changed.
All source records are read using an appropriate Data Provider component. 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 table is checked for each source record based on the source key attribute, and the values are compared.
Choose an appropriate Lookup component.
Use the Data Calculator component to look up multiple values for a single key attribute and perform comparisons. See “Data Calculator JavaScript”.
Select the lookup data from the target. The key attributes and all values to be compared for all current target records are read using an appropriate Data Provider component. See “Source components”.
Add an attribute to the port structure to populate the additional target key attribute. The lookup result determines whether a source record has been modified or is either new or unchanged. This attribute indicates the data state and allows data to be filtered in the next step of the transformation process. The update operation on the target must uniquely identify the current record, thus the date part of the target key needs to be populated as well. See “Modifying port structures”.
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 the existing keys.
Look up all necessary target values. The first lookup uses the new target key attribute as the output attribute in the Data Calculator, thus indicating existence. The values to be compared are read into temporary variables.
Compare source and target attribute values. The target key attribute is recalculated, based on a value comparison for the existing records.
Example:
Source data – select Key, Name, Price from PRODUCT
Lookup data – select Key, Valid_From, Price from PRODUCT_PRICE where Valid_To='9999-12-31'
First check existence by reading the effective date from target:
Output attribute – Valid_From
Default value – 0
Read current target price into temporary variable:
Output Attribute – Tmp_Price
Default Value – 0
If a current target record exists (Valid_From is not 0), compare Price and Tmp_Price. Recalculate Valid_From to 0 if the Price has not changed. Performing these lookups and calculations results in records with Key, Name, Price, and Valid_From attributes. Valid_From either contains the effective date of the target record to be updated or contains 0, indicating new and unchanged records.
