A context-based entity applies when:

• At least 2 entities are delivered.
• A context relationship exists between these 2 entities. One entity is the parent context of the other entity.
• The parent context entity is delivered as a delta and the child entity is delivered as a full set.

You need to create context-based entities when a set of data may be delivered within the boundaries of a parent context. It may happen that all the Part Suppliers for just one single Part number are delivered. Since there is only focus on the Part that you specify in the delivery, all data of the other Parts remains unchanged. In this case, in order to allow implicit delete detection and set based validations, the existing set of Part Suppliers must be filtered based on the context of its parent context. If you change or remove an item, it is implicitly taken into account. Another term used for this is context-based enddating, since it enddates records that are not taken into the delivery.

{info} When you create a dependent relationships, the context filter is automatically set to True. In independent relationships the context filter is automatically set to False. You can change this, but be very careful if you want to put a context filter for the independent relation on.

Example: context-based partial delivery

Suppose we deliver Part as a delta delivery; we only deliver Part Nbr 1, 2, 200. We delivery Part Supplier as a full load. This is what will happen:

• For the Part numbers 1, 2, 200 we will do a full compare of the Part Supplier records between what is delivered and what is in CFPL.
  • If a new key of Part Supplier is added, new records are added to Part Supplier.
  • If a key of Part Supplier is delivered but attributes are altered, the record is updated.
  • If a key of Part Supplier is not delivered, the record in CFPL is flagged as deleted.
• If you add a not yet existing Part number, it will be added as a record in Part.
• The constraints are also checked only for this context delivery. So, between the boundary of the context data.

{danger} If you forget to set the context filter to True and you make a partial delivery for only Part numbers 1, 2, 200, than all other Part numbers will be deleted.

Layers

In our Design Time process the Logical DataModel drives the set-up of most physical datamodels. Each physical model is a representation of the data that resides in one of the technical layers of our Runtime environment. Understanding of this linkage is a key element of our solution.

TSTGIN : Technical STaGing In

This layer is a separate database that represents the physical data delivery of the related tenant. The main purpose of this layer is to create a common starting point for further processing. It is expected this layer holds data in columns and rows.

This layer contains data from the last delivery and must be truncated at the start of each new delivery. The status of the delivery is managed through i-refactory API's. We therefore assume an external process orchestrates deliveries by means of our API's.

You can use this Layer to:

1. Fix data problems in the sourcedata to prevent conflicts in the next layers
2. Enriching information (e.g. code to value translations)
3. Enrich metadata to capture knowledge of related sources
4. Renaming columns toward Business Language  - Often, we choose for source column naming, to keep lineage with sources as clear as possible. When source system uses confusing column names, we can give more descriptive names.

{info} This all to prepare the step from Technical Staging towards the Logical Validation Layer (LVL). Preparing in this layer avoid problems in LVL. In order to retain full lineage, all datafixes and enrichments must be captured in computed columns or derived entities.

LVL : Logical Validation Layer

The Logical Validation Layer (LVL) is the second layer in the chain and is initiated after the data has been fully transmitted to the Technical Staging In layer. The LVL layer is a separate database that services the following functions :

1. Transforms the source data to the structure of the Logical Data Model
2. Buffers the transformed data historically
3. Validate deliveries and determine whether the delivery should be "accepted", "accepted with findings" or "rejected"
4. Based on tresholds and defined actions non-compliant records can be skipped, non compliant attribute values can be removed or you can leave the non-compliant data as-is
5. Automatically executes type conversions

A delivery gets “accepted with finding” when there are constraint violations present in the data, without exceeding any constraint threshold. A constraint threshold determines the number of occasions in which a constraint can be violated before the delivery receives the “rejected” flag. Each constraint has a separate configurable threshold that describes the maximal allowed occurrences before a delivery is rejected, and an action that describes what to do when a violation occurs. In case the delivery is “accepted” or “accepted with findings” the data is prepared for handover to the next layer.

The actions that can be selected variates over the constraint types. For instance, a primary key constraint will not have the option to select ‘Empty column’ or ‘No action’, since it is for a database impossible to have an invalid or ‘NULL’ value as primary key. The possible actions are skip row, empty column or no action.

• The Skip row action will skip the entire row.
• The Empty column action will replace the value which caused a violation with an empty field (NULL).
• The No action does just simply nothing and accepts the current situation.

The Validation process

The data validation process determines the quality of the data by evaluating the constraints that are captured in the model. The constraints are grouped into a constraint type and are separated over two stages. At the end of each stage an intermediate evaluation is performed to determine whether any threshold exceeded.

The first stage covers the validation types that can be performed without requiring data from outside the delivered dataset itself. The validations that are performed during this stage are:

• The Primary key constraint determines whether entities in the dataset contain records with an identical primary key or combination of keys. (Possible actions: skip row)
• The Datatype constraint validates whether the value provided before the datatype conversion is identical to the value after the datatype conversion. Unsuccessful datatype conversions results in a NULL value, which causes a mismatch afterwards. (Possible actions: skip row or Empty column)
• The Mandatory constraint determines for each column that is specified as mandatory whether for each record has a value present. (Possible actions: skip row)
• The Enumeration constraint determines, after casting it to the correct datatype, whether the given value corresponds to a value in the enumeration set. (Possible actions: skip row, empty column or no action)
• The Attribute value constraint verifies for each imposed value constraint whether it complies to the user defined expression. (Possible actions: skip row, empty column or no action)
• The Record constraint checks for each imposed records constraint whether it complies to the user defined expression. (Possible actions: skip row or no action)

The output of the first phase of validations is initially stored in a temporary table. This temporary table is used to register the information of the witnessed violations in the ACME database and to determine whether the delivery should be rejected for exceeding a threshold. Only when the none of the threshold is exceeded the data is approved and transferred to the corresponding ‘UCLVL_BC’. These tables have an additional column ‘ACM_SKIP_ROW’ that indicates for which records the ‘skip row’ action is performed. The ‘no action’ and ‘empty column’ actions are column specific and the output of this action is already processed to its final state.

The second stage contains the constraints that requires additional data from outside the provided dataset. Missing data that is required to perform the validation is retrieved from the main dataset and appended to the validation dataset. For example, a delivery with data that references to a table that is only set once during the initially delivery can only be validated when this data is appended to the validation dataset. After all the missing datapoints are appended to the validation dataset the constraints are validated. The constraints that are validated during this phase are:

• The Alternate key constraint validates if there are multiple records with an identical set of alternate keys.
• The Parent relationship constraint validates whether there exists a parent for the given key value(s). (Possible actions: no action)
• The Child relationship constraint validates whether there exists a child for with the given key value(s). (Possible actions: no action). This constraint is only validated when a child relation is required.
• The Set constraint executes the user defined query that determines the incorrect records. (Possible actions: no action)

The result set of the second phase of validations is initially stored in a temporary table and used for violation registration and classification of whether the delivery should be rejected for exceeding a threshold. Only when none of the threshold is exceeded the data is transferred to the corresponding ‘UCLVL_SC’. These tables contain the final product of the two validation phases.

In case a delivery is accepted with or without findings the final step of updating the LVL dataset and transferring the data to the next layer (CFPL) is initiated. This process is performed by comparing the dataset stored in the UCLVL_SC tables with the current from the LVL, to construct a delta script that only contains the changes. This script is afterwards used to update the LVL itself and in placed in the queue for the next layer (CFPL). This allows the LVL and the CFPL to work concurrent and independent from each other. The LVL clears the data from the UCLVL_BC and UCLVL_SC and the whole process repeats for the next delivery.

{warning} Deliveries with key violations, data-type conversion violations or with validation issues above treshold will not be further processed and require mediation.

{editorial} KJD Link to missing file: ../data-logistics/metadata.md

For deliveries with active validation, validation results are stored in our metadata.

CFL : Central Facts Layer

This layer is a separate database that services the following functions:

1. data is stored historically and split into non-temporal key tables and temporal context tables
2. integration of diverse sources is managed
3. Business Rules that generate new facts are executed and results are persisted
4. capture facts from LVL Layer and GDAL Layer - meaning it is the central fact repository of both batch and CRUD related transactions

The CFPL database is automatically populated by the I-Refactory load processor, that will generate the SQL code from the Logical Validation Layer to the Central Facts Layer from the metadata.

GDAL : Generic Data Access Layer

This is the access layer that makes the data as persisted in the CFL layer accessible for data consumption in various perspectives:

1. current perspective that return current time perspective on data in a relational format
2. historical perspective that include all transaction timelines in a relational format and
3. the point-in-time perspective that helps to return data on a given (historical) point in time and
4. brings all non-key related attributes together in one view (known as One Attribute Set Interface - OASI)

This layer is by default virtual and this layer also supports consuming data at the same time this layer is refreshed. This layer also manages CRUD updates to the CFL layer by means of generated CRUD-triggers within the current perspective database views.

FILE_EXPORT : File Export Layer

This architecture layer groups together all tasks related to the file based exchange :

1. based on an additional perspective made available in the GDAL layer that provides point-in-time access to an OASI view including business keys
2. serves as a layer in the runtime monitor to monitor and manage all tasks related to the file based exchange
3. serves as the architecture layer when defining data consumptions agreements
4. serves as the architecture layer to use in the API call that creates the delivery

Load Strategies

The i-refactory orchestrates and executes a critical part of your data logistics. We designed our process taking the characteristics of physical deliveries into account. For this reason we identify the following main building blocks to manage your data logistics:

{list}

• A Delivery Agreement is an agreement for data-exchange between a Tenant and the i-refactory dataplatform targeted to/from one Layer.
• A Tenant is a party for a dataset or datarecord as agreed in the Delivery Agreement.
• A Delivery is a container that holds the specification of what is actually exchanged to/from the i-refactory platform.
• A Layer holds the logical model relevant for the the Delivery (Agreement). In practice the layer specifies if you deliver datasets (the layer is identified as Logical Validation Layer), you opt for OLTP integration (the layer is identified as Generic Data Access Model) or you opt to consume data in a physical file (the layer is identified as File Export).

The i-refactory orchestrates the data logistics for three different scenario's :

1. For snapshot based datasets delivered to the i-refactory
2. For snapshot based datasets delivered as parquet files from the i-refactory
3. OLTP integration

1. Strategy for datasets delivered to the i-refactory

The delivery of datasets is targeted towards i-refactory's Logical Validation Layer. The delivery of datasets is a combination of:

• inclusion of all or some entities related to the Logical Data Model, also known als a Complete and Partial Delivery. A Complete Delivery is a delivery that includes all entities of the corresponding Logical Data Model. A Partial Delivery is a delivery that includes some entities of the corresponding Logical Data Model.

• indication per entity if this entity represent a Full or Delta of the provided snapshot.

A Full provided snapshot of the corresponding entity includes all known records at the snapshot datetime. All inserts, updates and logical deletes can be determined automatically by comparing the available current facts with the content of the Delivery.

A Delta provisioned snapshot of the corresponding entity includes the delta as of previous successfully delivered facts and thus must include and indicate deleted records while new and changed records can be identified automatically.

With the options as described above a Tenant can:

1. Deliver all Entities, with each entity as Full
2. Deliver all Entities, with each entity as Delta
3. Deliver all Entities, with some entities as Full and other as Delta
4. Deliver some Entities, with those entities being marked as Full
5. Deliver some Entities, with those entities being marked as Delta
6. Deliver some Entities, of which some are Full and some are Delta
7. As an alternative you can use Context Based Delivery

Context Based Delivery is a special type of delivery that allows Full datasets within a given context to be executed by i-refactory. The main advantage is that the implicit delete detection (or physical delete process) works within context.

Example: assume we have a Logical Datamodel that holds the following entities: Sales Organization related to N Sales Orders related to N Sales Order lines. There are 5 regional Sales Organizations that individually provide their sales order details to this Logical Model. If we treat Sales Organization as Context, you can provide for one Sales Organization (thus context) all their Sales Order and related Sales Order Lines. The processing of Sales Order and Sales Order Lines are treated as Full within their context and thus implicit delete detection is determined automatically for this scenario.

2. Strategy for file based delivery from the i-refactory

The architecture layer File Export is used to support file based data consumption. For this architecture layer you can define Delivery Agreements and deliveries to load data into parquet files on your local filesystem or on your Azure Storage Account. The event that triggers the outbound delivery is based upon an external process that uses our delivery-API. Outbound delivers can be configured to balance system resources in similar manner as inbound deliveries, also monitoring outbound deliveries is done on the same overview as where you monitor inbound deliveries.

You need to specify the following parameters in our API to trigger an outbound delivery:

• the related Data Delivery Agreement
• the snapshot datetime that identifies the point in time perspective you want to use
• the location where the i-refactory Delivery create a specific folder to generate a parquet-file per entity
• optionally the column names and values that are used as filter criteria for all entities where the exact column name is matched
• optionally the custom metadata specification

3. Strategy for OLTP integration

The delivery of OLTP integration is targeted towards i-refactory's Generic Data Access Layer. This means OLTP integration can Create, Read, Update and Delete facts through the Generic Data Access Layer. From a delivery perspective this means you can activate deliveries to a Data Access Model, that results in Create, Read, Update and Delete actions orchestrated by the i-refactory solution.

The default pattern supported is a record based process. For applications that deliver sets of records to be processed, this requires additional configuration. Specific Business Rules or Controls other than primary key and relationship constraints can be included as part of the execution process as so-called Data Application Programming Interface (DAPI) controls. CRUD transactions process current timeperspective on data in a relational structure.

Timestamps through all layers

As a result of the temporal consistency we use a time stamp, part of the Delivery, for the (technical) timelines throughout relevant layers. The snapshotdatetime of the source system or the datetime chosen by user determine the timestamp used. Per entity a 'high water mark' is defined: the latest (most recent) time stamp processed for that entity. The logistical process won't accept delivery set to a time stamp older than the high water mark.

The logistic process fully manages the Traveling Now to ensure a GDAL view always delivers a time consistent view of the registered facts. This also enables processing facts in CFPL in parallel to time consistent consumption of data through GDAL - this avoids typical scenario's to separate loading in night batches from daily data consumption assuming the database is capable to technically orchestrate the workloads.

Migration of Historical Data

The i-refactory solution allows user influence of the date used as timestamp by defining the timestamp as part of the Delivery. This provide an option to migrate historical timelines to your i-refactory managed dataplatform by providing data per timeslice in chronological order. Through this method you can recreate your historical data perspectives with the existing datawarehouse as a source system.

Logical and Physical Deletes

When performing a delivery you can opt for logical or physical deletes.

The default scenario is logical delete, which means that:

• In the Logical Validation layer (HSTGIN), the value of the column acm_record_ind from the deleted records is changed to 'R'
• In the Central Facts layer, the deleted rows from a Context (Satellite) are end-dated and a new row with value 'R' for the column acm_record_ind is added
• In the Central Facts layer, the rows from Dependent (Links) and Independent (Anchors) entities are logically deleted by changing the acm_exists_ind from 1 to 0

Unified Anchor Modelling

With i-refactory you have the possibility to serve a mixture of OLTP and OLAP scenarios in a fully automated manner. In order to support these scenario's modelling concepts you need to balance availability versus quality. The Unified Anchor Modelling is an enhanced set of modelling patterns, evolved from Anchor Modelling and Data Vault. The modelling is closely aligned with the concepts of Fact Based Modelling.

We created the Unified Anchor modelling to specify the modelling construct of the Central Fact Layer. In order to be able to maintain full history, we split the business keys in Anchors and their context as Context. Context are functionally dependent (“belong to”) their Anchor. This context can consist of attributes describing the Anchor and/or relationships. This split is often referred to as ‘6th Normal Form’.

We keep track of existence for every Anchor: for each Business Key stored in the Anchor we track if that key was delivered in the last delivery. We assume that a business key is denoted as the primary key in a Logical Model.

All Anchors are ‘append only’: only new rows are added, a record in an Anchor will never be removed. By default we maintain history for all Context. Context can be logically or physically deleted.

Anchor: Independent Entity

An Independent Entity is an entity that implements an Anchor for a Business Key that ‘stands alone’ e.g. that does not contain a reference to another Entity. An Independent Entity contains Business Key fields, that show up as alternate key (AK), and the primary key (PK) is it's surrogate key (ID).

{info} With the Logical Model as your main reference, an independent entity can be identified by the following rule:

• Entity has a primary key
• None of the primary keys are foreign keys

Examples in TPCH: Customer, Order and Clerk.

Meta data stored for an Independent Entity:

1. Audit ID - Process that created the record
2. Entity ID - Reference to the Data Definition Register to the entity
3. Existence - Indicates whether or not the key value is found (exists) in the most recent load of the entity
4. Latest Start Datetime - the datetime at which the related context was last modified
5. Last Created Datetime = Last Existence Datetime - the datetime at which the anchor existed (existence = true) most recently.

Anchor: Dependent Entity

A Dependent Entity is an entity that implements an Anchor for a Business Key that ‘depends’ in its existence on another Entity.

A Dependent Entity contains Business Key fields of which at least one is a foreign key (FK). The Business Keys will show up as alternate key (AK), and the primary key (PK) is it's surrogate key (ID).

{info} With the Logical Model as your main reference, a dependent entity can be identified by the following rule:

• Entity has a primary key
• At least one of the primary keys are foreign keys
• The entity has a dependent relation to another entity

Examples in TPCH: Orderline Item and Part Supplier.

Meta data stored for a Dependent Entity equals to the metadata of an Independent Entity.

Context: Standard Context Entity

Context provides Context to an Anchor Entity and contains:

• Attributes describing the Context
• Relationships to Anchors (even to the same Anchor Entity)
• Every record is linked to the Anchor (reference named ID) as part of the Primary Key of the Context Entity
• Every record in a Context Entity is ‘timestamped’ with a Start- and End Datetime
• The Start Datetime is part of the Primary Key

Examples in TPCH: Part Supplier S Properties and Order Line Item S Properties.

{info} A Context Entity will be populated of the Attributes and relationships that are not part of the primary key.

The attributes in an entity in the Logical Model can be split over multiple Context Entities. Reasons to split one logical model entity into multiple Context Entities:

1. Multi Active or bi-temporal behavior.
2. Split of immutable attributes.
3. Separate identification and uniqueness validation for Alternate Keys (see later).
4. Rate of change (avoid data duplication of stable data).
5. Runtime / information logistic considerations: each table can only be loaded from one table.
6. Or any other physical / implementational consideration.

Metadata stored for a Context Entity:

1. Start datetime of the transaction timeline - the transaction datetime uniquely identifies each change in the context. For batch processing this date is provided as part of the metadata of a delivery. For transactional processing the time is set when the data is committed.
2. End datetime of the transaction timeline - this field will be end dated if the record is logically deleted or updated.
3. Audit ID - Process that created the Entity.
4. Entity ID - Reference to the Data Definition Register to the entity.
5. Record Indicator - indicates whether a record is a New record (N), result of an update (A) or logical delete (R).

The example shown above shows that first record is a new Record (‘N’) to Anchor with ID = 1. The functional Value is ‘aap’. For the same Anchor (with ID = 1) there are two more updates performed. The functional value is changed to 'noot' followed by 'mies'. Both updates are marked as appended update (‘A’). Last record is still open because the End datetime is dated in the future. Records are logically closed by physically updating the end date with the datetime of the next record.

We only INSERT records, we deviate from this rule to:

• set end dates for transaction timeline in context entities
• set existence and update metadata of anchors entities
• support physical deletion of context and registration of physical deletion on the Anchor.

Context: Immutable Context Entity

Immutable Context is a Context Entity for which all attributes and relationships are ‘insert once’: once the context is provided, it will never change. By doing so no history will be maintained.

As soon as context is provided for an Anchor, its context won’t be updated even if (changed) content is delivered. No errors or warnings are shown. Only when an update is provided via a CRUD-transaction processing call, an error will be returned.

{warning} Be aware: Updates by sources are ignored when processing Immutable Context.

In a Logical Model it is not visible whether context is immutable or not. This information should be specified by a modeller in the Central Facts Layer.

An example of Immutable Context could be meter readings or strict bookkeeping systems that allow no updates on financial transactions and thus enforce a reverse transaction instead.

Context: Bi-temporal Context Entity

A Bi-Temporal context is a context extended with two additional columns to store a valid timeline: Valid Start Datetime and Valid End Datetime. The Valid Start Datetime is part of the primary key, hence the primary key of a Bi-Temporal context is a composite key consisting of three columns: id, ACM Start Datetime, Valid Start Datetime.

A Bi-Temporal context cannot be used to track the existence of a key, for this you need to add a standard context (possibly a "dummy" context without any context columns).

Context: Multi-Active Context Entity

Multi-Active Context is a Context Entity that contains a Multi Active (Versioning Key) extending the business key of the associated Anchor.

{info} Each Multi Active Business Key has its own context which is functional dependent from the business key in the Anchor extended by the Multi Active Business Key.

This implies that one business key can have multiple context records at the same time.

Example: a customer has multiple types of phone numbers. “Home”, “Work” and “Mobile”. We add a Multi Active Key “Phone Nbr Type”.

Let's assume your logical model specified the relation between customer and customer phone numbers as shown above. How can this be reflected in the CFL ?

1. First alternative is to use Multi-Active Context
2. Second alternative is to define a Dependent Entity for Customer Phone with it's own context

What alternative should you use?

• Benefit of an Anchor is that it can have its own contexts and can be referred to from other places in the model. However, an Anchor adds extra storage and extra joins.
• For an Anchor existence is stored as metadata. For a multi active key there is no existence stored.

{info} Rule of thumb: if we know for sure that there is and will never be context to the multi active key we can create a Multi Active Entity especially when performance and/or volume are important considerations. Choosing for an Anchor is more flexible but less efficient.

Context: Alternate Key Context Entity

Alternate Key Context is a Context Entity with an additional uniqueness constraint over it's alternate key columns.

{info} An Alternate Key Context only holds Alternate Keys that are treated as unique. If you combine Alternate Keys in one Context, the combination is treated as unique.

Relation: Foreign Key Link

A Foreign Key Link relates Anchor Context to another Anchor. It's a formalization of a foreign key relation as defined in your Logical DataModel.

The main benefits of Foreign Key Links are:

• simplification of the UAM Model construct,
• reduces number of joins by eliminating the use of a separate Dependent Entity as relation construct
• the loading pattern is determined automatically: you can load Context of Anchor A if the key as specified in the Foreign Key Link exists in Anchor B.

Relation: Super-Subtype Construct

A Subtype forms a subset of the business keys of its Supertype. A subtype is an (in)dependent entity on which existence is maintained. As an additional step, any insert to the subtype creates a record in the supertype.

Subtypes are designated in your Logical Datamodel by the Subtype-symbol. The subtype construct can be Mutually exclusive and/or Complete. Check on these constraints should be part of the Logical Validation Model checks. No constraints are implemented in the CFL.

It is not necessary that every subtype is also physically implemented as a CFL subtype. Attributes of subtypes can be stored at their supertype. Formally a subtype describes a relation only.

Anchor: Generalization

A Generalization pattern defines a Supertype of more than one Anchor. This pattern generalize Anchors with different Business Keys. Generalization Patterns are relevant for use cases when :

• You define relations to the Generalization Concept
• You maintain Context on the level of Generalization Concept as "common attributes"

Anchor: Key Root

This pattern is mainly used in a separate Concept Integration Model. A Key Root integrates entities of different Models that share the same Business Key. The KeyRoot is a technical construct that manages the integration within the Central Facts Layer. Since a Key Root only manages the integration based on keys, a Key Root cannot have any context.