Detecting deleted source records

Records that have been deleted from the source after the last update of the target dimension table still have a corresponding current record in the target dimension table.

Key attributes of all current records in the target dimension table are read using an appropriate Data Provider component. For a list of Data Provider components, see “Source components”.
The existence of a corresponding record in the source is checked for each current target record based on the source key attribute.
1. Choose an appropriate Lookup component. For a list of Lookup components, see “Lookup components”.
  
  Use the lookup functionality of the Data Calculator component if your source data does not reside in a database. See “Data Calculator JavaScript”.
2. Select the lookup data from source. As this is a simple existence check only the source keys are needed from all source records. However, lookups in ETL always return a value for a key, so an appropriate return value must be selected as well.
3. Add an attribute to the port structure to populate the lookup result. The lookup result determines whether a source record has been deleted or still exists. This attribute indicates the data state and allows the data to be filtered in the next step of the transformation process. The new attribute is selected as the value attribute of a Lookup component or the output attribute of a Data Calculator rule. See “Modifying port structures”.
4. 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.
  
  Example:
  
  Target data – select Key, Valid_From from PRODUCT_PRICE where Valid_To='9999-12-31'Lookup data – select Key, '0' from PRODUCTValue attribute – Removed (integer)Default value – 1Performing this lookup results in records with attributes Key, Valid_From, Removed. Removed will be 0 (lookup value) for all existing source records and 1 (default value) for all non-existing records.