2. Architecture¶
The framework is organised in modules: the Automation, Core, Security, and GDPR modules described in the following chapters, complemented by the REST API, the web GUI RED Vision, and the Data Lineage module.
This chapter first explains the data flow, the layered structure, and the loading process.
2.1. Data Flow¶
In principle, data is loaded from source systems into an analytical database. The data is loaded largely unchanged and historisation information is added. A few transformations, such as the conversion of date values, can be performed before the data is persisted.
The framework can load data from multiple different sources. A connector is configured for each type of source (e.g. databases, files, REST APIs, see the figure below). The loading of the data and the historisation are triggered in near real time, so that the business department is able to make decisions based on current data. Access for reporting by the business department or follow-up systems is secured by a view layer.
2.2. Layered Structure¶
The framework consists of a layered structure (see the figure below) that is divided into a reporting and a technical layer. The reporting layer provides secured access for reporting users and connected systems; the technical layer is internal and not accessible to users.
Read the figure from bottom to top: each row is one layer. The PSV views normally feed the APB application marts; only in exceptional cases is the APB layer based directly on the PSB layer.
The layers above the PSA are plain SQL views in the database, and so are the views of the loading process (DV, PV, EV and others, see below). The ELT logic is therefore readable SQL in the database and is not hidden in external tools.
2.2.1. Reporting Layer¶
The reporting layer is fully secured and serves as the only way users can get access to data.
2.2.1.1. Persistent Stage Views (PSV)¶
This layer manages access for ad-hoc reporting via automatically generated views. The personalised access rights (row and column level security) are applied in the views of the PSV layer. For that reason, users only see the data that corresponds to their individual rights. The PSV layer is based on the PSB layer.
2.2.1.2. Application Marts (APV)¶
This layer manages access to centrally managed application marts via automatically generated views. Access rights can be assigned selectively and are independent of the access rights of the PSV layer. The APV layer is based on the APB layer.
2.2.1.3. Sandboxes¶
Sandboxes are private or project-related schemas in which users can create their own objects based on the data they are allowed to see.
2.2.2. Technical Layer¶
The technical layer holds the persisted data and is not visible to users.
2.2.2.1. Centrally Managed Application Marts (APB)¶
Centrally managed application marts are schemas that enable the persistence of complex queries and business models (e.g. data modelling as a star schema). They usually consist of views, but the top layer can also be materialised for performance reasons, e.g. for self-service BI tools when a response within milliseconds is necessary.
The APB layer is normally based on the views of the PSV layer, so that the security rules of the PSV layer apply. Only in exceptional cases is the APB layer based directly on the PSB layer.
2.2.2.2. Persistent Stage Base (PSB)¶
This layer is a compatibility view layer that compensates for structural changes in the layers below. Due to this layer a constant PSV layer is presented to the business department, no matter what changes appear in the source systems. It consists of the following view types:
- DR (dual date range)
All records with historisation in both time dimensions (business date and technical date).
- TA (technically active)
Only technically active records.
- BA (business active)
Only records that are technically and functionally active today (Type-2 tables only).
The PSB layer is based on the PSA layer. The business attributes of the source tables are displayed 1:1. In addition, renaming or hiding a column and pre-calculations for data enrichment can take place in this layer.
The PSB views are initially generated by API_REPPSA.COMPILE. Because the DR view can be customised manually, the framework uses a hash value (MD5) over the view text to decide whether PSB views are regenerated when an existing loader or PSA definition is adjusted (e.g. after importing TSA column changes with API_REPPSA.REFRESH_OBJECT):
TA and BA views: Since they select all columns (
SELECT *) from theDR(respectivelyTA) view, they are only created once when they do not yet exist and are not recreated on subsequent compilations.DR view – automatic regeneration: An existing
DRview is dropped and recreated only if the hash of the view text currently stored in the database still matches the hash of the view text generated in the previous Business Version (meaning the view was not modified manually) and the newly generated view text differs (for example because columns were added or removed, or their PSB visibility changed).DR view – no regeneration:
If the
DRview was modified manually in the database (e.g. to rename columns or add calculations), the hash of the live view text differs from the previously generated hash. API_REPPSA.COMPILE leaves the view untouched to preserve the customisations; new columns from an adjusted loader must be added to theDRview manually (or the view can be dropped before compiling to generate it anew from the metadata).If the adjustment does not change the
DRview text (for example when only column data types or lengths, key definitions, or BDate expressions are modified), the view-text hash remains unchanged and the view is not recreated.
2.2.2.3. Persistent Stage Area (PSA)¶
The PSA is the layer that serves as the data safe. In this layer the target tables with the historised and persisted data, the technical tables, and the views for the loading process are located. In addition to the data table, objects with the following postfixes (<TABLE_NAME>_<POSTFIX>) might exist:
POSTFIX |
DESCRIPTION |
|---|---|
ERR |
error table for invalid rows |
CTL |
control table for the historisation process |
HT |
hub table for resolved dependencies |
PAT |
patch table to store patched data used as a source like TSA |
POSTFIX |
DESCRIPTION |
|---|---|
DV |
difference view to detect new, changed, and deleted rows |
PV |
point-in-time view to set BDATE and TDATE |
PV2 |
point-in-time view 2 to inherit historisation from the source |
HV |
hub view to populate the corresponding hub table |
CV |
correction view for missing references in the BSK calculation |
CVR |
correction view for missing references in data columns |
CNV |
count view with the number of delivered TSA rows per BDomain and business date |
DEL |
deletion view for the history clean-up |
EV |
error view to detect null BSKs to populate the ERR table |
In case of dependencies between two or more tables, the framework automatically ensures that the tables are loaded in the appropriate order.
2.2.2.4. Transient Stage Area (TSA)¶
Every connector loads its data into this temporary stage without any modification. Once the data has been persisted in the PSA, the TSA data is deleted after an adjustable number of days.
2.3. Loading Process¶
The figure below shows how a delivery travels from the source into the PSA:
A connector loads the delivery from the source into the TSA table.
The framework queues the PSA load as soon as all dependencies are fulfilled.
Rows without a valid business key (detected by the EV view) are written into the ERR table.
The DV view identifies new, changed, and deleted rows, which are inserted into the PSA table.
The PV view closes the superseded versions by setting their BDATE and TDATE end dates.
The new data is immediately visible via the PSB and PSV views.
Other views in the PSA (e.g. HV, CV, PV2) are used depending on the properties of the source objects. The details of the historisation are described in Historisation.
Database objects whose name begins with ADM_ contain information for batch control. This level is independent of the data structures loaded and is used to manage the system processes in general (e.g. frequency of job execution or retention periods of log data).