Reference Data Stage Pipeline (General)

Which Stereotypes for Reference Data Stage are available?

Table type: Reference - Stores all loaded non-historized reference data. Table stereotype: SL - Reference Zone This table stores (insert,…

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).

Posted 1 year agoby Dirk.Lerner

How to design a domain Status CDC?

To ensure data quality it is recommended to use a domain for the Status CDC column. Set up a domain…

To ensure data quality it is recommended to use a domain for the Status CDC column. Set up a domain with the following characteristics.

See section How to design a domain Status CDC? in Chapter Change Data Capture Pipeline.

Posted 1 year agoby Dirk.Lerner

Any background information?

DAPI The DAPI MetadataZoneDapi.MetadataForGenerateStageReference is the basis for generating the LPS with the LoadPatternId 18. Generator The script for generating…

DAPI

The DAPI MetadataZoneDapi.MetadataForGenerateStageReference is the basis for generating the LPS with the LoadPatternId 18.

Generator

The script for generating the LPS with LoadPatternId 18 is Reference-Stage.sql

Logging

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.

Resources

With Release 2.13.0 this issue is solved. Inserted, Updated (Changed) and physical deleted rows are logged correctly!

Posted 1 year agoby Dirk.Lerner