In this section you find the good practices related to the logical validation model. We advise you to apply these good practices when you create a logical validation model.
Consult your data architect to get aligned on the level of normalization
When you make a mistake in defining a Helper, it is advisable to delete the object and start from scratch to avoid missing dependency links.
In the logical validation model, it is advisable that the order in which you select the composite key matches the order of attributes in the data source. This is important because the data will be sorted according to the composite key. If the order is not the same, then this will have a negative impact on the performance.
It is good practice to use domain types instead of a datatype. This is especially convenient when multiple attributes need to adhere to the same datatype and values. If the datatype or enumeration changes, you only have to adjust one domain instead of several attributes.
When you have an entity with an attribute whose values are restricted to a list of possible values:
if you don't expect changes on the list of possible values of the attribute or you have no intention to track any additional information about the attribute then you should use an enumeration constraint to restrict the values of the attribute (or domain).
if you expect changes on the list of possible values of the attribute and you have the intention to track information about these changes then you should have another entity, called reference entity, with an attribute whose values will be used as reference. To check if the value of the attribute is valid then you create a foreign key relationship between the original entity and the reference entity.
As an alternative to the first option, you can implement the enumeration constraint by means of a value expression.
{example} In our example, consider that a Nation belongs to a Region, and a region corresponds to one of the following continents: Asia, Africa, North America, South America, Europe, and Australia. If this list of values is static then Region_Name could be an attribute of Nation restricted by an enumeration constraint with the names of the continents .
However, suppose that during a period of time there will be no orders from Asia. This means that Asia is no longer a possible value for region and it should be removed from the enumeration constraint. If you use an enumeration constraint to validate the possible values of region, it wouldn't be possible to keep track of this change. To avoid this, you create an entity, called Region, with an attribute for the name of the region and a foreign key relationship between its primary key (Nbr) and the attribute Region Nbr of the entity Nation. Doing this, it is possible to keep track of the changes on the values of region and check if the region assigned to a nation is valid.
It is a good practice to use naming conventions for a business rule and it's helper (if applicable).
In the following example you can find the Business rule in the left window, and the helper in the right window.
Using strong data types improves data quality. It may result in data type conversion errors when the source data is inconsistent.
We can distinguish three types of time:
If you use a bi-temporal entity, it is important to do some sanity checks:
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.
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 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.