Create Business Rule Helpers

2023-04-17

In this section you can find how to create a business rule helper in a central facts model.

What is a Business Rule Helper?

You can use a business rule helper in the central facts model if you want to make calculation and want to keep a transaction history of the results of these calculation.
A business rule helper is essentially a view on the UAM tables.

Based on data available in the Central Facts Layer, new facts can be created or derived using a business rule helper. These newly created facts are treated in the same way as as other facts within the central facts model.

A business rule helper is essentially a view on the UAM tables. You use the existing entities from the central facts model as input and the results of the helper can be materialized in other UAM Entities, such as Anchors and Context.

It is also possible to build a helper based on an existing helper. If you do this, then you can make use of the option to cache the helper results as input for the next helper. This supports the functional decomposition of calculations.

{info} In the database, a Business Rule Helper is implemented as a separate UAM View defined on top of the referenced UAM Entities.

Create a Business Rule Helper

To create a business rule helper:

Select the Anchors and Contexts relevant to define the input for the new business rule helper.
Go to the Menu and click Tools > i-refactory > Business Rules > Design from Selection.
Design a business rule from selection
Assign a 'Name' to your Design (the design itself will be thrown away later).
Design name
A new tab with the 'Name' you've defined opens, it includes the selected Anchors and Contexts.
Right click on the Context where you expect to start your query from.

{tip} Select multiple entities by holding down the Shift key on the keyboard and clicking the mouse on every entity you want to select.

In the context menu, click on i-refactory > Business Rule > Generate Business Rule starting from this table.
Generate business rule starting from this table
Complete the information about the new helper:
- Helper Entity Name that uniquely identifies the Business Rule Helper.
- Helper Entity Owner is required, select the owner from the drop down list.
- Traverse Parent References is optional, use this if all parent anchors and their context tables must be pre-joined in the query of the BR Helper
- Include Driving Child Tables is optional, use this if all dependent child anchors and their context must be pre-joined in the query of the Business Rule Helper
  Create business rule window

{warning} By default, a BR Helper query will be executed as a Full Load. As a consequence of the full load, all existing keys that are not returned by the BR Helper query will be logically deleted in the Central Facts Layer. If the BR Helper query does not return the complete set of keys of the anchor, it should be flagged as Delta.

Properties

A new Business Rule Helper is created with the following properties:

ACM Start Datetime acm_start_dt:
- In the query this attribute has the value @time_consistent_transaction_dt (the value of this parameter will be calculated by the engine in the runtime process)
ACM Record Indicator acm_record_ind that must have one of the values:
- N: indicates a new record
- A: indicates an appended record
- R: indicates a removed record
Business Keys of the involved Anchors
- In the query, the business keys of all parent anchors are in the join path
Surrogate Keys of the involved Anchors: optional, just for reference
Context Columns: if you've selected a Context as your starting point

Minimum requirements

The minimum requirements for a Business Rule Helper are:

ACM Start Datetime is included in the Column list
ACM Record Indicator is included in the Column list
You've selected one or more Business Keys as Primary Key
You've defined the expected output Columns with the exact Code as you defined the Column in your query
All Columns are stereotyped
The query syntax is correct

Generated query

To see the generated query:

Double click on the BR Helper
The Helper Business Properties appears.
Click on the tab IR Derived Entity

In the generated query you'll see that the attributes of all parent anchors of the selected table are pre-joined.

The generated or manually created query should be constructed using a WITH clause as in the example below:

WITH QRY AS ( SELECT @time_consistent_transaction_dt as acm_start_dt
    , 'N' as acm_record_ind
    , <rest of columns>
    FROM <tables>
    )

By default the query filters the anchor tables on

existence = True
and the context tables on the time consistent transaction date

You can adjust these filters and query if necessary.

In the figure below, the generated query of Order Total is adjusted to calculate the order total.

{info} A BR Helper is processed as if it is a source table from the logical validation layer. This means that the BR Helper must have the full business key of the concept as its primary key, just like a regular logical validation model entity.

Mappings

Additionally, a BR Helper object requires two different types of mappings:

The mappings from the tables referenced in the BR Helper query to the BR Helper object (dependency mappings).
Dependency mapping Order totals
The mappings from the BR Helper object to the target UAM table where the results of the BR Helper will be materialized.
Mapping from Order totals to materialized object

The dependency mappings are created automatically when you use the option 'Generate BR Helper starting from this table' menu option. Also for every dependency mapping a 'traceability link' is created in the diagram. However, if the BR Helper query is manually changed, the dependency mappings must be manually updated as well. This means that if an extra table is joined in the query, an extra dependency mapping from this table must be created as well. And if a table is removed from the query, the dependency mapping from this table can be removed as well.

{warning} There must be dependency mappings from all source tables that are used in the BR Helper query to the BR Helper. This will make sure that all source data of the BR Helper is processed before the BR Helper query is executed. If one of the dependency mappings is missing, the BR Helper query may give unexpected results.

{info} Although it is good practice to create dependency mappings on the column level, they are only required on the table level.

Delta Helper

Use a Delta helper if the BR Helper query does not return the complete set of keys of the anchor.

If the Delta flag is set, the BR Helper query will be executed as a Delta Load. In this case the query itself is responsible for calculating the proper N | A | R values for the acm_record_ind column. With a Delta BR Helper it is possible to delete existing keys, but then the records with acm_record_ind = R must be calculated by the BR Helper query.

{info} A Full Load business rule helper does essentially a FULL OUTER JOIN between the results of the query and the existing data. A Delta Load business rule helper does a LEFT OUTER JOIN between the results of the query and the existing data.

Caching of central facts business rule helpers

A business rule helper in the central facts model can be marked as a cached helper. Caching intermediate results enables reuse of precomputed derived facts and result in better throughput. Caching business rule helpers also aids in a more modular approach (functional decomposition) of complex business rules as the intermediate results can now be stored and reused.

To cache the results of a BR Helper query in the Central Facts Layer, set the Cache flag. This will create an auxiliary table in the database to store the current results of the helper entity calculations and the derived transaction time for the helper. This table can then be used in other helper entity calculations that use this data.

Transactional Consistent Filter

A Helper Entity is a calculation of facts given a set of Input Datasets. The calculation is automatically triggered by the i-refactory engine if it detects that one of the Input Datasets used in the Helper Entity are changed or delivered. Because the 'Fact Store' stores a temporal view of all facts delivered over time, the calculation of Derived Datasets needs to take into account temporal consistency as well.

Before the helper executes, i-refactory computes the transaction time for the Derived Datasets. There are three options to calculate the transaction time: LEAST | GREATEST | CURRENT TIME. If you need to ensure full consistency for derived facts - which may result in missed updates - then you should choose LEAST. If you need to ensure maximal accuracy of the derived facts - which may result in transaction time inconsistencies - then you should choose GREATEST. If Current Time is chosen the variable @time_consistent_transaction_dt will be set to the current time.

{info} If the calculated transaction time is less than or equal to the transaction time of the last time the helper was executed then i-refactory skips the execution of the helper, otherwise the helper will be executed.

You should always filter your context in the query specification on a consistent point in time as shown in the example query below.

WITH QRY AS 
    ( SELECT @time_consistent_transaction_dt AS acm_start_dt
        , 'N' AS acm_record_ind 
        , [h_order].[nbr] AS order_nbr
        , [h_order].[id] AS order_id
        , COUNT([l_order_line_items].[id]) AS nbr_of_lines
        , SUM([l_order_line_items].[quantity]) AS quantity
        , SUM([l_order_line_items].[revenue]) AS revenue
        , SUM([l_order_line_items].[discounted_revenue]) AS discounted_revenue
        , SUM([l_order_line_items].[discounted_revenue_plus_tax]) AS discounted_revenue_plus_tax
        FROM [tpc_h].[h_order] AS [h_order]
        LEFT
        JOIN [tpc_h].[l_order_line_items] AS [l_order_line_items]
        ON   [l_order_line_items].[id] = [h_order].[id]
        AND  [l_order_line_items].[acm_start_dt] <= @time_consistent_transaction_dt
        AND  [l_order_line_items].[acm_end_dt] > @time_consistent_transaction_dt
    WHERE [h_order].acm_exists_ind = 1
    GROUP BY [h_order].[nbr], [h_order].[id]
    )

Example of how to create a business rule helper

Suppose you've defined your central facts model as a representation of a computer filesystem.

A server has one or more drives, and each drive contains a number of root folders. Each root folder may contain subfolders, each of which may contain subfolders of their own, etc. Each folder may also contain one or more files. Each file has a size in bytes and a last modified date.

Now we want to calculate the total size in bytes of (all files in) a folder and the last modified date of (any file in) a folder. Moreover, we also want to calculate the total size of (all folders on) a drive and the last modified date of (any folder on) a drive.

To do this, we select the File Properties Satellite table, right click and select i-refactory - Business Rules - Generate a Business Rule Helper starting from this table. The following dialog now appears:

This will define a BR Helper object with pre-defined columns and generated query that can be modified afterwards. The following information must be provided:

Helper Entity name: the name of the BR Helper object and database view
Helper Entity owner: the schema in which the database view will be deployed (cannot be left empty)
Traverse Parent References Yes/No: if Yes then all parent anchors and their context tables will be pre-joined in the query of the BR Helper
Including Driving Child Tables Yes/No: if Yes then all dependent child anchors will be pre-joined in the query of the BR Helper

We now click OK. This will create the BR Helper object as well as the dependencies with the source tables that are used in the query (displayed as PowerDesigner Traceability Links):

When we select the properties of the BR Helper and click on the Derived Entity tab, we see that a query is generated in which all parent anchors of the selected table (File Properties) are pre-joined:

s_file_properties 
JOIN l_file 
JOIN l_folder 
JOIN l_drive 
JOIN h_server
<p class='spacing'></p>

The query generated or manually created should be constructed with a WITH clause as in the example below:

    WITH QRY AS ( SELECT @time_consistent_transaction_dt as acm_start_dt
    , 'N' as acm_record_ind
    , <rest of columns>
    FROM <tables>
    )

By default the query filters the anchor tables on existence = True and the context tables on the time consistent transaction date (adjust these filters when necessary). When we look at the column list (or the select clause of the query) we see that our BR Helper object has the following pre-defined columns:

A column acm_start_dt with value @time_consistent_transaction_dt (the value of this parameter will be calculated by the engine in the runtime process)
A column acm_record_ind that must have one of the values: N(ew) | A(ppended) | R(emoved)
The Business Key columns of all parent anchors in the join path: File Name, Folder Name, Drive Letter, Server Name (note that the Link Reference columns Folder ID, Drive ID, Server ID are obsolete because they are not part of the business key)
The Satellite columns of the starting table (Size, Last Modified Date)

A BR Helper is processed as if it were a source entity from the logical validaton model. This means that the BR Helper column interface must have the full business key of the concept as its primary key, just like a regular logical validation entity.

We will now modify the generated query for our purposes, namely to calculate the total size and the latest modified date of a folder. These are derived properties of Folder, so we need to select the business of key of Folder: Server Name, Drive Letter, Folder Name, and set this as the primary key of the BR Helper object. Because we need to find all files in a folder including all its subfolders, we use a recursive Common Table Expression (CTE) to select all subfolders of a folder on any level. Then we can aggregate over all files in these folders and calculate the sum of the file sizes and the maximum of the last modified dates of the files. If the BR Helper query uses one or more CTEs in its definition, we must make sure that it returns QRY as its last CTE.

Additionally, a BR Helper object requires two different types of mappings:

The mappings from the tables referenced in the BR Helper query to the BR Helper object (dependency mappings)
The mappings from the BR Helper object to the target central facts model entity where the results of the BR Helper will be materialized

The dependency mappings are created automatically when using the Generate BR Helper starting from this table menu option. Also for every dependency mapping a Traceability Link is created in the diagram. However, if the BR Helper query is manually changed the dependency mappings must be manually updated as well. This means that if an extra table is joined in the query, an extra dependency mapping from this table must be created as well. And if a table is removed from the query, the dependency mapping from this table can be removed as well. In our example we use the FK Link Folder is in parent Folder in the recursive CTE part, and hence we have to manually add the dependency mapping from this table.

{warning} There must be dependency mappings from all source tables that are used in the BR Helper query to the BR Helper. This will make sure that all source data of the BR Helper is processed before the BR Helper query is executed. If one of the dependency mappings is missing, the BR Helper query may give unexpected results.

{info} Although it is good practice to create dependency mappings on the column level, they are only required on the table level.

BR Mapping to Folder Totals to Satellite

In our example we have created a new Satellite Folder Derived Properties to store the results of the calculations in the BR Helper. Because a BR Helper object acts as source table from the LVL, we need to map the columns from the BR Helper object to our new Satellite table. This can be done as follows:

Open the Mapping Editor (go to the Tools menu and select Mapping Editor...)
Under the Source data source Internal, select the BR Helper object Folder Totals
Under the Target data source, select the Satellite Folder Derived Properties
Map the business key columns of the BR Helper (Server Name, Drive Letter, Folder Name) to the id column of the Satellite
Drag the calculated columns Size and Last Modified Date from the BR Helper to the Satellite, this will create the new Satellite columns as well as the mappings

In our example we have created a new Satellite Folder Derived Properties to store the results of the calculations in the BR Helper. Because a BR Helper object acts as source table from the LVL, we need to map the columns from the BR Helper object to our new Satellite table. This can be done as follows:
Open the Mapping Editor (go to the Tools menu and select Mapping Editor...)
Under the Source data source Internal, select the BR Helper object Folder Totals
Under the Target data source, select the Satellite Folder Derived Properties
Map the business key columns of the BR Helper (Server Name, Drive Letter, Folder Name) to the id column of the Satellite
Drag the calculated columns Size and Last Modified Date from the BR Helper to the Satellite, this will create the new Satellite columns as well as the mappings

Helpers on Helpers

We can use use the results of a BR Helper calculation in another BR Helper calculation. Now that we have calculated the total size and the latest modified date of a folder, we can go one step further and calculate the total size and the latest modified date of a drive.

To do this :

Select the Folder Derived Properties Satellite table
Create a second BR Helper object Drive Totals starting from this table
In the query for this BR Helper object we aggregate over all folders in a drive and calculate the sum of the folder sizes and the maximum of the last modified dates of the folders

This results in the following diagram and BR Helper query:

This completes the definition of the business rule calculations in our example UAM model. To summarize, we took the following steps:

Create a BR Helper Folder Totals starting from the File Properties table
Modify the columns and the query to calculate the total size and the latest modified date of a folder
Create a Satellite table Folder Derived Properties to store the results of the BR Helper Folder Totals query
Make sure that all source tables of the BR Helper query are mapped to the BR Helper object
Map the columns of the BR Helper object to the columns of the Folder Derived Properties satellite
Create a BR Helper Drive Totals starting from the Folder Derived Properties table
Modify the columns and the query to calculate the total size and the latest modified date of a drive
Create a Satellite table Drive Derived Properties to store the results of the BR Helper Drive Totals query
Make sure that all source tables of the BR Helper query are mapped to the BR Helper object
Map the columns of the BR Helper object to the columns of the Drive Derived Properties satellite

We are now ready to deploy the model to the database. We note the following:

A BR Helper object in the model will result in a view (or table valued function) in the database
A mapping between a BR Helper object and a UAM table will result in an entity update from the BR Helper object (source) to the UAM table (target)
The load query of the entity update will insert the results of the BR Helper view into the target table
The @time_consistent_transaction_dt parameter will be calculated during the runtime process using the dependency mappings and the Least / Greatest setting

What's next?

When you have created business rule helpers:

After you complete the model, please perform a Model Check.

Constraint violation actions are applicable to certain constraint categories. Not all combinations of constraint categories and violation actions are allowed.

An attribute must have a value, whatever that value may be. It must not be NULL.

A data type of an attribute defines what value an attribute can hold. The data type specifies what type of mathematical, relational, or logical operations can be applied to it without causing an error.

An attribute datatype constraint is the most basic constraint type. It checks for the datatypes we support and have implemented.

For example, we check for string, length of string, integer, date, etc. In the following figure you can see the supported data types by PowerDesigner.

Image is omitted: Supported data types

Constraints can be violated and there are some actions that can be performed when a violation occurs. The possible actions are: EMPTY COLUMN, NO ACTION and SKIP ROW.

An attribute value constraint is an expression that is evaluated. The person who writes the expression is responsible for the correctness of it. The expression should be formulated in a positive way and lead to a Boolean answer. If the expression validates to True, than the value is correct.

Examples

The values in attribute X has to be bigger than 10: X > 10
The email address has to be in a certain pattern: email address LIKE '%_@_%.__%'

A Concept Integration Model is also a central facts model on which you place integration patterns. It is not required to create a concept integration model, but it can be very useful.

Every constraint is assigned to a constraint classification.

The main purposes of the Generic Data Access Layer (GDAL) are to provide logical perspectives for data consumption and to manage CRUD actions.

A generic data access model is a virtual data model that acts as an interface bridge between consumer applications and the central fact storage.

Every attribute is assigned to an attribute classification.

An entity record constraint checks whether an attribute meets the requirements set by another attribute belonging to the same entity.

The main purpose of the Logical Validation Layer (LVL) is to transform the data received from external data sources to fit into the logical data model structure. It is also responsible for validating deliveries. The Logical Validation Layer is also known as the Historical Staging In (HSTGIN) Layer.

The logical validation model is the representation of a single external data source in a logical format. It represent how data delivered by a specific tenant should be transformed, temporalized and validated in the {popup}logical validation layer. The logical validation model is also known as Historical Staging model (HSTGIN).

Multi-active attributes are attributes that contain a business key to provide multiple context records at the same time. For example: a customer has multiple types of phone numbers. “Home”, “Work” and “Mobile”. In that case we add a dependent entity on customer with key “Phone Nbr Type”. This is to prepare for the CFPL multi-active key on customer.

The main purpose of the Technical Staging Layer (TSL) is to create a common starting point for further data processing. It receives data delivered from external data sources and temporally stores them in a database. The input data should be in a tabular format (rows and columns).

Bi-temporal attribute is an attribute that changes over time: they follow a valid timeline. For example, a Part may have a price valid for December and a price valid for January.

Every entity is assigned to an entity classification and to a parent entity classification. The possible values for entity classification are: ALTERNATE KEY CONTEXT, ATTRIBUTE CONTEXT, GENERALIZATION,HELPER, REFERENCE CONTEXT, STABLE, STABLE DEPENDENT and STABLE INDEPENDENT

Entity Set Constraint An entity set constraint can be used to perform a check concerning values of two or more attributes that belong to different entities or to perform a check concerning the value of an attribute with respect to a set of values.

A Set Constraint Helper is a helper in the logical validation model. It is the implementation of a set constraint. The helper returns the records of an entity for a given set constraint, where the instances of this entity do not meet the definition of this set constraint.

The business requirements describe how data should be delivered for the data consumers (end users or applications) in terms of concepts, relationships between concepts and constraints to validate the data. These requirements can be described in a logical data model, for example.

A Business Rule Helper is a helper in the central facts model. It is a set-based calculation of derived facts. You need to use a Business Rule Helper if you want to make a calculation and want to keep a transaction history of the results of this calculation. You use the existing entities from the central facts model as input. The results of the helper must be materialized in 'regular' fact entities, such as Anchors and Contexts, to make them accessible in the Generic Data Access Layer.

Closed Open means that the timeline is valid from (vanaf in Dutch) the supplied valid start date until - but not including - (tot in Dutch) the supplied valid end date. In practice, this means that the start date of a valid time record is equal to the end date of the previous valid time record.

You need to create context-based entities when a set of data may be delivered within the boundaries of a parent context. 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. 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.

The Management Model contains the PowerDesigner objects for the Unified Anchor Modelling (UAM). When a UAM object is created, a so-called PowerDesigner replica of the corresponding Management Model object is created. This means that certain properties such as metadata columns and column stereotypes are configured in the Management Model and cannot be changed. The replication settings specify which elements of an object can be changed after creating a replica from the template object. It is possible to override the replication settings of an UAM object and change a specific property.

The temporal atomic type describes the datatype of the temporal attributes|

The main purposes of the Central Facts Layer (CFL) is to store data historically. It can also integrate data from different sources. The Central Facts Layer is also known as Central Facts Persistency Layer (CFPL)

The central facts persistence implementation model is the representation of facts in an anchorized data model with the ability to integrate multiple logical models.

In the context of i-refactory, data transformation refers to operations involved in turning raw data readily useful and closer to the business requirements.

Integration patterns are used to integrate entities from different data models. If two or more entities from different data models share the same business key, you can use the Integration Pattern named Key Root. It is a good practice to capture integration patterns in a separate model, named Concept Integration Model.

An attribute is mandatory when its value can not be empty (NULL).

A Physical Data Model (PDM) represents how data will be implemented in a specific database.

{note} The i-refactory uses four PDMs: technical staging model, logical validation model, central facts model and generic access model. Each one of these models is implemented as an additional database, which is used to store data from external and internal data sources.

Reverse engineering is the process of reconstructing a physical and/or Entity Relationship (ER) model from an existing data source. The purpose of reverse engineering is to avoid manual work as much as possible.

Architecture layer

The core of the i-refactory architecture has four layers: TSTGIN, LVL, CFL and GDAL. There are also two auxiliary layers: UCLVL and EXT.

If an entity has one or more attributes that changes over time and you want to keep track of when a attribute is valid at a certain transaction time, then you have a special case of a regular dependent entity, called bi-temporal entity. The bi-temporal entity stores historical data with two timelines. The primary key of the bi-temporal entity is composed by the primary key of the parent entity and the valid start date attribute. The attribute that changes over the valid time is called a bi-temporal attribute.

A delivery agreement is a contract between a Tenant and a Logical Implementation Model or Generic Data Access model. An agreement has a duration. The delivery agreement set the architecture layer (interface) where the data should be ingested as well as the default settings to be applied to the deliveries.

A dependency mapping is a mapping between a helper (or BR helper) and a source entity used in the query of the helper. The helper and the source entity must belong to the same model.

Default dependency is set on entity level (source entity to helper entity)
To allow lineage on attribute level, via the Mapping editor, you could manually add the dependency on attribute level.

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.

A Logical Data Model (LDM) matches the language, structure and quality of the business, regardless of the physical data implementation. The Logical Data Model reflects the business requirements.

A delivery may be considered as "untrusted" if deletes of data in the Logical Validation Layer have taken place and the processing of new deliveries cannot 100% rely (trust) on having enough statistics and data available to detect logical deletes, to determine the exact delta and to execute set based validations.

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 transaction time in i-refactory is different from what is commonly understood by transaction time. Transaction time is usually seen as the moment when a fact was stored in the database. In the i-refactory, the transaction time is the time, as dictated by the source system, not by the i-refactory database.

The Attribute type links the attribute to one of the existing interfaces.

Computed columns are columns whose content is computed from values in other columns in the table.

Functional date A functional date or time is a point in time and is defined by a user. An example is an order date or date of birth.

The technical model (also known as Technical Staging In model: TSTGIN) is a representation of how exactly one delivery from a specific data source will be processed in the technical staging layer.

Generalization is the process of extracting shared characteristics from two or more classes (hyponyms), and combining them into a generalized superclass (hypernym). For example: an 'employee' and a 'customer' are both 'persons'.

The Mapping Editor provides a graphical interface for creating and viewing mappings between models. It provides a global view of all the mappings related to the entities of a given model, allowing you to quickly identify those which are mapped and not mapped.

When a certain fact can change over time and you need to capture when that fact is valid in the real world, you can add a valid start date and a valid end date to the entity.

A valid time tells us in which period a record is valid. While a functional date represents just one point in time, the valid time has a begin and an end date, for example:

For Order item 123, a Retail price of 10.00 was valid from 2019-01-01 to 2019-06-01.
For Order item 123, a Retail price of 12.00 was valid from 2019-06-01 to 2020-01-01.

Alternate key is an attribute or a group of attributes whose values uniquely identify every record in an entity, but which is not the primary key

Candidate key

A candidate key consists of one or more attributes and meets the following requirements:

Unique: The value of the key defines uniquely one instance of a concepts. There are no double values.
Non-volatile: (Almost) doesn't change.
Minimal: Contains only the elements needed.

There are two kinds of candidate keys:

primary key
alternative key

A Key Root Link is an integration concept that must be used when the exact same business concept or dependent business key occurs in different models. The Links for this dependent business key in the different UAM models are all subtypes of the Keyroot Link.

Normalization is the process of decomposing tables in a database in order to reduce data redundancy and improve data integrity.

A strongly typed model is a model in which each all attributes have a predefined data type, for example: integers, doubles, date.

Surrogate Key A surrogate key is a system generated unique identifier that does not have any contextual or business meaning.

Business Key

A business key is an unique identifier that has business meaning and exists in the real world outside of the database. It consists of a column or a set of columns that already exists in a table. A business key is also known as a natural key

A Key Root Hub is an integration concept that must be used when the exact same business concept or independent business key occurs in different models. The Hubs for this independent business key in the different UAM models are all subtypes of the Keyroot Hub.

A relationship shows how two entities are related to one another. For example, a customer can place an order, and a order can have a customer.

Every Attribute has an atomic type (data type) which is linked to the attribute type of that attribute.

The cardinality shows how many instances of an entity can take place in a relationship.

An enumeration consists of the list of values that a given attribute should adhere to.

{example} An order can have different statuses, such as shipped,packing,created anddone. Other statuses are not allowed.

Foreign Key

A foreign key is an attribute or a set of attributes that refers to the primary key of another entity. The original entity containing the primary key is called the 'parent' entity and the entity containing the foreign key is called the 'child' entity.

A natural key is an unique identifier that has business meaning and exists in the real world outside of the database. It consists of an column or a set of columns that already exists in a table. A natural key is also known as a business key

The primary key is an assigned key that consists of a minimal set of attributes to uniquely specify an instance of a record. The attribute or a combination of attributes should meet the following characteristics:

Unique: The attribute values of the key uniquely identify one instance of a concept. There are no duplicate instances.
Non-volatile: The key does not change.
Mandatory: All values are filled; there are no NULL values.

It is good practice to choose a primary key that also meet the following characteristic:

Safe: Doesn't contain private or sensitive information, such as a social security number.

Constraints are related to the other elements depending of the type of the constraint. Certain constraints are associated to attributes, entities, helper entities, unique keys or relationships between entities.

An attribute may be assigned to one or more entities (ex: acm_exists_ind) and an entity may have several attributes

Each layer may have one or more interfaces. The amount of interfaces depend on how many tenants and delivery agreements have been configured.

Namespace is what in the terminology of SQL Server is called database schema.|

A Delivery is a container that holds the specification of what is actually pushed to the i-refactory platform. This specification consists of a list of entities.

Key Root A Key Root is a central repository for Business Keys. A Key Root ensures that similar records out of different data sources are identified by both the same Business Key as the Surrogated Key.

Context

A Context is a temporal table with a transaction start and end date. The Context tracks all changes of the context attributes related to a business key in the transaction time. This means that every change of an attribute value in a source system leads to a new record in the Context. The old record is end dated with the load date and the new record is start dated with the load date.

Hyponym is a term that denotes a subcategory of a more general class. For example: 'cat' and 'dog' are a hyponyms of 'animal'.

A mapping establishes relationships between concepts of separate data models. It creates a link between entities and attributes from a source model to related entities and attributes in the target model. A source model should precede the target model in the i-refactory architecture.

oasi_bk is an abbreviation for One Attribute Set Interface (OASI) with business keys. A normal view in the generic data access layer (GDAL) consists of the surrogate key, foreign key and attributes. The oasi_bk-view in the GDAL is a view where the business key(s) are also shown.

A subtype is a subgroup of an entity. You can create a subtype if a group of instances share some attributes and relationships that only exist for that group. For example, entity Customer can have a subtype Company and a subtype Person. They share the common attribute customer number, and can have some attributes of their own. Such as birth date for a Person. The entity Customer is called a supertype.

A subtype:

inherits all attributes of the supertype
inherits all relationships of the supertype
usually has one or more own attributes
can have subtypes of its own

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.

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 domain will help you to identify the types of information in your model. It defines the set of values for which a column is valid. A domain can specify a data type, length, precision, mandatoriness, check parameters, and business rules. It can be applied to multiple columns, which makes it easier to standardize data characteristics for columns in different tables.

Each interface may have one or more entities and one entity belongs to only one interface. An entity belongs to an i-refactory data model.

A helper entity creates a derived entity and can be used when you need to transform, filter, or calculate data. The purpose of a helper differs per model:

Technical model: a helper is used to transform data.
Logical validation model: a helper is an implementation of a set constraint (Set Constraint Helper).
Central facts model: a helper is used for a set-based calculation of derived facts (Business Rule Helper).

HSTGIN is the abbreviation of Historical STaging IN. It is an older term to indicate the Logical Validation Model or Logical Validation Layer.

A schema is a set of database objects, such as tables, views, triggers, stored procedures, etc. In some databases a schema is called a namespace. A schema always belongs to one database. However, a database may have one or multiple schema's. A database administrator (DBA) can set different user permissions for each schema.

Each database represents tables internally as <schema_name>.<table_name>, for example tpc_h.customer. A schema helps to distinguish between tables belonging to different data sources. For example, two tables in two schema's can share the same name: tpc_h.customer and complaints.customer.

A Tenant is a delivering party for a dataset or datarecord as agreed in the Delivery Agreement.

TSTGIN is the abbreviation of Technical STaging IN. It is an older term to indicate the Technical Model or Technical Staging Layer.

An index organizes data in a way that improves the speed of data retrieval from a database. To maintain the index data structure, there is a cost of additional writes and storage space.

The acronym CRUD stands for create, read, update, and delete. These are the four basic functions of persistent storage.

OLAP is a acronym for Online Analytical Processing. OLAP is category of software tools which provide analysis of data for business decisions. It uses complex queries to analyze aggregated historical data from OLTP systems.The primary objective is data analysis and not data processing.

OLTP is a acronym for Online transaction processing. OLTP captures, stores, and processes data from transactions in real time. Its primary objective is data processing and not data analysis.

A hub or 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 its surrogate key (ID).

A key is a combination of one or more attributes of an entity that uniquely defines one instance of that entity.

Index