Table type: Reference - Stores all loaded non-historized reference data.
- Table stereotype: SL - Reference Zone
This table stores (insert, update and delete) all current reference data which were available within the full current load. There is no versioning of the data.
Hint: For historized reference data it is recommended to use Data Vault patterns in the History Zone of the Stage Layer.
- Mandatory column: Audit Trail Id (Name and Code as configured)
This column stores an Id which ties the loaded data to the ExMeX Logging Framework.
- Mandatory column: Status CDC (Code: StatusCDC)
This column stores a flag, generated by Reference Data Stage LPS: I,U
In the case of the Reference Data Stage LPS only I and U are used.
To ensure data quality it is recommended to use a domain for the Status CDC column (see also the following topics).
To ensure data quality it is recommended to use a domain for the Status CDC column. Set up a domain with the following characteristics.
The DAPI MetadataZoneDapi.MetadataForGenerateStageReference is the basis for generating the LPS with the LoadPatternId 18.
The script for generating the LPS with LoadPatternId 18 is Reference-Stage.sql
Because of how the MERGE SQL statement logs data changed in the target, the ExMeX Logging Framework can display only the sum of inserted, updated, and deleted rows in the RowsInserted column of the FctProcessStepVapourTrail in the MetadataZone-Mart.
Quote from the Microsoft SQL Docs:
When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.
With Release 2.13.0 this issue is solved. Inserted, Updated (Changed) and physical deleted rows are logged correctly!