How Compose handles missing references in the data warehouse

Before running a data warehouse task, it is important to understand how Compose handles missing references. Missing references may be involve records that are simply missing or records whose arrival has been delayed. The latter might occur if data is ingested from two different systems (for example, an ERP system and a CRM system), with each system having its own task.

Handling an early-arriving fact:

If a record references another record which does not exist yet, then Compose will do the following:

• Insert a placeholder for the missing reference record. The placeholder record will only include the business key and surrogate key. The rest of the columns will be set to NULL.

  Information note

  The fact being processed can already include a valid reference to the surrogate key of the reference record.

• Document the missing record in the TLOG_REF_ERRORS_VALUES table. The TLOG_REF_ERRORS_VALUES table contains the following columns:

  • RUNNO - The task run number.

  • RELATIONNR - An internal number that can be used by Qlik Support to determine the source entity.

  • NO_RELATIONS - The number of missing references. For example, if Customer A ordered three different items (from the Orders table) and Customer A is missing, this number will be three.

  • KEYVALUE1-20 - The missing record. Since the missing record is a Primary Key, which may consist of several columns, there are 20 KEYVALUE columns.

Example:

If the "Orders" table references "SuperGlue" in the "Products" table, but "SuperGlue" does not exist in that table, Compose will mark "SuperGlue" as a missing reference, insert a record with the key value "SuperGlue" (assuming that the product name is the business key) to the "Products" table, and insert NULL values in the remaining "Products" table columns.

When the missing reference eventually arrives, it will be mapped to the record created for it and the NULL values will be replaced by the actual values.

See also: Viewing missing references.