ETL Step 3: XREF

In the XREF step, the system generates MD_ENTERPRISE_KEY for records based on the value of MD_BUSINESS_KEY. Unlike many other staging tables that are dropped and re-created every time when the ETL Job runs, *_XREF tables (naming convention: *_XREF) are not cleared. *_XREF tables maintain a mapping relationship between MD_BUSINESS_KEY and MD_ENTERPRISE_KEY to make sure each MD_ENTERPISE_KEY is uniquely mapping to a MD_BUSINESS_KEY.

The system also generates MD_ENTERPRISE_KEY for other reference entities if the referred record is not arrived. This is a part of the Late Arriving Dimension function.

*_SSI tables are input in this step, while *_XREF tables are output.

The following table lists the key staging tables.

Key Staging Tables

Table Name

Table Description

<TARGET_ENTITY_NAME>_ XREF

Contains a mapping relationship between MD_BUSINESS_KEY and MD_ENTERPRISE_KEY

The XREF table contains the following system reserved fields.

System Reserved Fields in Staging Tables

Field Name

Field Description

MD_BATCH_ID

For late arriving data, the batch ID is set to -1. The system updates MD_BATCH_ID when data arrives

BUSINESS_KEYVALUE

Stores business key values that are read from MD_BUSINESS_KEY in the *_SSI tables

ENTERPRISE_KEY

Stores generated sequence numbers