4. Core Module

The Core Module combines the data import via different connectors with bitemporal, two-dimensional historisation and a calendar logic. The result is an object and application status that can be used to trigger downstream applications.

4.1. The Concept of Business Domains

Business Domains (BDomains) are used to handle multiple TSA loader streams into the same target PSA table independently. The corresponding TSA tables may differ, e.g. some sources may have more or fewer columns. However, the PSA table is always a superset of all possible columns. The BDomain is the finest granularity tracked by the RED framework. BDomains may have different load cycles; the PSA object status is evaluated for each BDomain separately.

4.2. Historisation

Why two time axes? Consider an interest rate that applies from 1 January but is only entered into the source system on 3 January, and corrected again on 10 January. A report must be able to answer two different questions:

  • Which rate was valid on 2 January? This is the business view (business date, BDATE).

  • What did the data warehouse know about this rate on 5 January? This is the technical view (technical date, TDATE).

RED therefore uses a strict bitemporal, two-dimensional historisation for all data, in order to be able to reconstruct all data changes. Each record has a period of functional and a period of technical validity:

Functional validity (business date)

Results from the functional requirements, e.g. between which two dates a given interest rate applies. Columns BDATE_FROM and BDATE_TO, stored with calendar day accuracy.

Technical validity (technical date)

Reflects the time of storage: from the load of the record until it is replaced by a newer version. Columns TDATE_FROM and TDATE_TO, stored with millisecond accuracy.

The DR views in the PSV layer include both types of validity for each record.

Two types of tables exist:

Type-2

Corresponds to the definition of a Slowly Changing Dimension (SCD) Type-2 and is mostly used for master data. The functional validity is given by BDATE_FROM and BDATE_TO, the technical validity by TDATE_FROM and TDATE_TO.

Type-3

Intended for event tables, also called transaction tables, e.g. sales which occur on a certain day. These tables only have a single BDATE column, which describes the date on which the event occurred. Historisation therefore happens in the technical dimension only.

Note

The term “SCD Type-3” for event or transaction tables does not comply with the usage of this term in common literature. However, the RED framework uses this term as a synonym for such event tables.

Note

SCD Type-1 tables, which have neither a bitemporal historisation nor a single point in time, do not exist in the framework as they contradict the idea of reproducibility and traceability.

Internally there are optional _HT tables in the PSA layer that are managed as Type-0.

4.2.1. Key Columns

RED uses three kinds of key definitions:

KEY

PURPOSE

MANDATORY

DEFAULT

Business Surrogate Key (BSK)

identifies a record and all its historical versions; basis of the historisation

yes

primary key of the source table (database sources)

Identity Code (IDC)

identifies a row in the source uniquely

yes

primary key of the source table (database sources)

(Alternate) Business Key (BK)

alternate key for hub (_HT) lookup tables

only for _HT tables

none

4.2.1.1. The Business Surrogate Key (BSK)

The BSK serves as the basis and absolute prerequisite for the historisation logic, as each data record can be uniquely identified via the BSK. The data record and all its historical variants have the same BSK. A query via the BSK therefore returns the complete temporal history of a record. This means that any change to a data record, for example made by SQL updates in the source system, is traceable via the BSK, and a record can be reconstructed as it was at any point in the past.

Definition of the BSK

The BSK is composed of the BDomain and a logical business key (BK). In typical OLTP systems the primary key of a table is usually a good choice for the BK. However, do not use the primary key if the source system is already historised and the primary key contains a time dimension. In any case the BK must uniquely identify the entity within one BDomain, e.g. a customer number or a bank account number (IBAN). The BK can consist of multiple columns.

The BSK is calculated as a hash value (MD5) of the BK columns and the BDomain. The composition of the BSK can be seen in the definition of the _DV view of the respective table; the value is stored in the PSA tables and made available in the PSV views in the column BSK.

In addition to historisation, the BSK may serve as a join attribute. Due to technical or business reasons, the content of the BSK may change in the future. Therefore, it is not recommended to use the BSK as a filter criterion. The columns that make up the BSK are available in the PSV views anyway.

Important

The definition of a BSK is mandatory. If the source is a database and the source table has a primary key, this key is implicitly taken as BSK but may be changed later.

4.2.1.2. The Identity Code (IDC)

The Identity Code (IDC) identifies a row in the source uniquely. It is always identical with the primary key of the source. In many cases IDC and BSK are the same, as long as the primary key of the source does not contain a time dimension.

Important

The definition of an IDC is mandatory. If the source is a database and the source table has a primary key, this key is implicitly taken as IDC but may be changed later.

4.2.1.3. The (Alternate) Business Key (BK)

Source tables may provide two different sets of columns which may serve as BK. An alternate BK (different from the definition used for the BSK) may be used for _HT (lookup) tables. In this case the columns of the first set are used for the BK while the BSK is made up of the second set.

_HT tables record IDC, BSK, and BK together. New combinations of these three are inserted as a new row; there are no updates or deletes. Therefore, _HT tables serve as high-performance lookup tables from IDC or BSK to the BK columns. These lookup joins may be used during the PSA load for resolving and persisting key information (denormalisation of key tables).

Important

The definition of an alternate BK is only mandatory if _HT tables are used.

4.2.2. Control-BDate vs. Effective-BDate

Control-BDate

When a dataset is loaded into the TSA layer, the column RED_BDATE is filled as follows:

  1. DBI loader: the external BDate expression (EXT_BDATE_SQL_EXPR in DBIADM.DBI_LOADER_BD) is evaluated. CFI loader: the BDate is taken from the file name, if defined (BDATE_PATTERN and BDATE_FMT in CFIADM.CFI_LOADER_BD).

  2. If the result of step 1 is NULL, the current date (SYSDATE) is used.

  3. If the “first full daily load” option is set (BDATE_FFDL_PREV_DAY in the loader definition), the first load of the day (after midnight) uses SYSDATE - 1 instead.

The resulting RED_BDATE of the TSA table is called the Control-BDate. For Type-3 tables an entry with zero rows is created with this Control-BDate in REDADM.RED_PSA_CONTROL, in order to document a load for this business date even if no rows are propagated into the target PSA table.

Effective-BDate

During historisation the Effective-BDate is evaluated by the _DV view for each row. Each row of a delivery may therefore belong to a different business date and is historised accordingly. The expression used is defined in the columns BDATE_EXPRESSION and BDATE_INIT_EXPRESSION of REPPSA.REP_TSA_OBJECTS; the resulting expression can be checked in the corresponding _DV view.

For each Effective-BDate a separate control entry is created in REDADM.RED_PSA_CONTROL, which changes the related object status.

Example: Control-BDate and Effective-BDate

A CFI loader receives the file sales_20240315.csv without a business date pattern on 16 March 2024. The Control-BDate is therefore 2024-03-16 (SYSDATE). The file contains bookings of 14 and 15 March in its column BOOKING_DATE, which is defined as BDate expression. The rows are historised with the Effective-BDates 2024-03-14 and 2024-03-15, and the object status changes for both business dates.

4.3. The Process of Historisation for Type-2 Tables

Important

The RED framework uses closed intervals for all kinds of time frames.

Records are updated by comparing the content of each delivered record with the content of the existing record. If there is no change, nothing is stored.

If a business attribute of a record changes, three things happen:

  1. The previously valid record is copied with unchanged BDATE_TO but terminated TDATE_TO: it is set to the load time of the new record minus 1 millisecond (closed interval), which also records the time of the change.

  2. A copy of the old version is stored with BDATE_TO = “day before the change” and is technically valid (TDATE_FROM = load time, TDATE_TO = infinite).

  3. A new record with the new values is stored with BDATE_FROM = “day of the change” and is technically valid as well.

Thus, changing a record in the source system creates two new records. Of the records with overlapping functional validity, only the newest is technically valid.

Note

“Not terminated” or “infinite” means the date is set to 9999-12-31.

If the same data record (identical BSK) with the same BDATE_FROM is delivered again during a reload, but with a changed attribute, the change is only visible in the technical dimension: the old record is closed with TDATE_TO = load time minus 1 millisecond and the new record is inserted with TDATE_FROM = load time.

Historisation prevents overlapping time slices. If overlapping time slices occur, they are corrected automatically. In addition, as is generally the case in data warehouses, a record is never physically deleted (except by the delete logic).

Important

The historisation process in the RED framework handles changes on the whole time scale transparently. Validity periods in the past are split if necessary. Old data may be reloaded at any time without extra effort.

4.4. The Process of Historisation for Type-3 Tables

Since Type-3 tables only have a BDATE (no from-to period), an attribute change for the same BDATE triggers a technical historisation: the new record is inserted with the current load time as TDATE_FROM and an infinite TDATE_TO, while the old record is terminated with TDATE_TO = load time minus 1 millisecond.

Example: Type-3 Change

A sale was booked on 1 December 2020. Subsequently, the amount is changed because an incorrect value had been entered. The business date does not change, so the historisation is achieved via the technical date only.

4.4.1. Changes to a Record

The following example shows the evolution of a record (same BSK throughout) as it changes in the source system. We assume a database of a wine retailer with a table WINES which contains the name of the wine and the grape variety.

The date columns, the name, and the business attribute GRAPE_VARIETY are queried after each change using the following SQL statement:

 1SELECT 
 2   bdate_from, 
 3   bdate_to, 
 4   tdate_from, 
 5   tdate_to,
 6   name,
 7   grape_variety
 8FROM psv_db1_dr.wines
 9WHERE bsk = '1c63ce7fbfc874f72ea480d4682344a1' 
10ORDER BY bdate_from, tdate_from;

The record is loaded for the first time on 30 December 2019. It is given infinite validity from both a business and a technical perspective.

Table 4.1 new record (first load)

ROW

BDATE_FROM

BDATE_TO

TDATE_FROM

TDATE_TO

NAME

GRAPE_VARIETY

1

2019-12-01

9999-12-31

2019-12-30 17:50:15.305

9999-12-31 00:00:00.000

The RED Vineyard

Sangiovese

The attribute GRAPE_VARIETY is changed from “Sangiovese” to “Sangiovese Grosso” with effect from 3 January 2020; the change is loaded on 4 January. This creates two new records:

Table 4.2 changing the business attribute GRAPE_VARIETY

ROW

BDATE_FROM

BDATE_TO

TDATE_FROM

TDATE_TO

NAME

GRAPE_VARIETY

1

2019-12-01

9999-12-31

2019-12-30 17:50:15.305

2020-01-04 04:48:13.895

The RED Vineyard

Sangiovese

2

2019-12-01

2020-01-02

2020-01-04 04:48:13.896

9999-12-31 00:00:00.000

The RED Vineyard

Sangiovese

3

2020-01-03

9999-12-31

2020-01-04 04:48:13.896

9999-12-31 00:00:00.000

The RED Vineyard

Sangiovese Grosso

Row 1

The original record is technically terminated: TDATE_TO is set to 1 millisecond before the load time of the change. This documents when the change was recorded.

Row 2

A copy of the original record is functionally terminated with BDATE_TO = 2 January 2020, the day before the change. It is technically valid from the load time onwards.

Row 3

The new record with the new value is functionally valid from 3 January 2020 and technically valid from the load time onwards.

Changing GRAPE_VARIETY again to “Sangiovese Piccolo” with effect from 16 January 2020 (loaded on 17 January) adds two more records:

Table 4.3 changing the business attribute GRAPE_VARIETY again

ROW

BDATE_FROM

BDATE_TO

TDATE_FROM

TDATE_TO

NAME

GRAPE_VARIETY

1

2019-12-01

9999-12-31

2019-12-30 17:50:15.305

2020-01-04 04:48:13.895

The RED Vineyard

Sangiovese

2

2019-12-01

2020-01-02

2020-01-04 04:48:13.896

9999-12-31 00:00:00.000

The RED Vineyard

Sangiovese

3

2020-01-03

9999-12-31

2020-01-04 04:48:13.896

2020-01-17 06:30:34.677

The RED Vineyard

Sangiovese Grosso

4

2020-01-03

2020-01-15

2020-01-17 06:30:34.678

9999-12-31 00:00:00.000

The RED Vineyard

Sangiovese Grosso

5

2020-01-16

9999-12-31

2020-01-17 06:30:34.678

9999-12-31 00:00:00.000

The RED Vineyard

Sangiovese Piccolo

Row 3

The previously newest record is technically terminated.

Row 4

Its copy is functionally terminated with BDATE_TO = 15 January 2020.

Row 5

The new record is functionally valid from 16 January 2020.

Rows 2, 4, and 5 are technically valid today; together they describe the complete business history without gaps or overlaps. Rows 1 and 3 document what was known before the respective change. The figure below shows the business validity of all five rows:

Timeline of the business validity of rows 1 to 5 of the Sangiovese example Technically valid Technically closed Row 2 Sangiovese Row 4 Sangiovese Grosso Row 5 Sangiovese Piccolo Row 1 Sangiovese closed 04.01.2020 Row 3 Sangiovese Grosso closed 17.01.2020 01.12.2019 15.12. 03.01.2020 16.01. 01.02. business date (BDATE) open end: 31.12.9999
Business validity of the rows in the example (technically valid rows above, technically closed rows below)

4.4.2. Several Changes on the Same Business Date

If a record is changed again for the same business date, this is called a change of an “old booking”. Such a change is only represented via the technical dates: the current record is technically closed by setting TDATE_TO to 1 millisecond before the load time of the new record (row 5 in Table 4.4). Only one of the two records is technically valid, but the historical development of the data can still be traced.

Table 4.4 changing the business attribute GRAPE_VARIETY twice for the same business date

ROW

BDATE_FROM

BDATE_TO

TDATE_FROM

TDATE_TO

NAME

GRAPE_VARIETY

1

2019-12-01

9999-12-31

2019-12-30 17:50:15.305

2020-01-04 04:48:13.895

The RED Vineyard

Sangiovese

2

2019-12-01

2020-01-02

2020-01-04 04:48:13.896

9999-12-31 00:00:00.000

The RED Vineyard

Sangiovese

3

2020-01-03

9999-12-31

2020-01-04 04:48:13.896

2020-01-17 06:30:34.677

The RED Vineyard

Sangiovese Grosso

4

2020-01-03

2020-01-15

2020-01-17 06:30:34.678

9999-12-31 00:00:00.000

The RED Vineyard

Sangiovese Grosso

5

2020-01-16

9999-12-31

2020-01-17 06:30:34.678

2020-03-15 11:46:30.339

The RED Vineyard

Sangiovese Piccolo

6

2020-01-16

9999-12-31

2020-03-15 11:46:30.340

9999-12-31 00:00:00.000

The RED Vineyard

Sangiovese Picc.

4.4.3. Historisation of Deleted Data

The deletion of records in the source system can be historised as well. Deletions are detected either by a delete flag from the source (e.g. the change tracking operation “D” for SQL Server or Oracle sources) or by a full load, which compares the PSA with the delivered TSA data.

Depending on the business needs, the validity of a deleted record is terminated either by business date (BDATE_TO is set to the day of the deletion) or by technical date (the business dates stay the same, but TDATE_TO is terminated). For sources with active change tracking (CDC type CT) only the termination by technical date is possible.

Optionally, deleted records can be kept visible by an ACTIVE flag: a new record with infinite validity and ACTIVE = false is created. This can be useful e.g. for deletion statistics.

4.5. Connectors

Connectors manage extraction and load processes from different sources. With minimum impact on the sources, data is transferred as quickly as possible from the source into the TSA layer. The RED framework takes care of the optimal timing, parallelism, and logging. No external scheduler is needed.

The RED framework provides two standard connectors:

  • The Database Interface connector (DBI) for all types of databases with a JDBC driver.

  • The Common File Interface connector (CFI) for files like CSV, FBV, JSON, and Parquet.

Custom connectors may be developed individually and plugged in.

4.5.1. Database Interface Connector (DBI)

The generic DBI connector accesses any database via JDBC or using custom drivers provided by Exasol.

Special features are supported for the following databases:

  • Exasol

  • Oracle

  • Microsoft SQL Server

  • PostgreSQL

In addition, the DBI connector is used to fetch data from REST APIs. Since REST APIs have very individual specifications, the API must first be wrapped in a Python UDF (user-defined function), which presents the data via views. These views are then used as DBI sources.

The DBI load process is based on an extended change data capture (CDC) concept. As soon as a change is detected in the source, the load processes are triggered. Alternatively, load processes may be triggered by a timer, e.g. once a day or every 4 minutes. By these mechanisms the RED framework allows for near-real-time extractions and loads.

Depending on the type of source, native CDC features are supported for incremental loads, e.g. Change Tracking in SQL Server or Flashback Query in Oracle.

In some cases it might be useful to trigger a single loader or a whole group of loaders manually. The RED framework supports such manual triggers via API calls or even via a mail with a specific subject.

Important

In any case the RED framework relies on pull and not on push propagation.

For sources that want to stream data into the data warehouse, a Kafka connector can be provided on request. In this case RED acts as a subscriber of a topic, pulling data out of Kafka into the TSA layer in sub-minute intervals.

4.5.1.1. CDC Process

Each DBI loader is associated with exactly one watch group (CDC group). The CDC group defines an SQL query per BDomain which yields a so-called watch ID. The watch ID may be a column value of a source object which indicates a change for a set of tables. Or the SQL expression could simply be something like select sysdate, which results in one change per day and therefore triggers a load of all dependent loaders once a day (at midnight).

Sequence of the CDC process: watch query, pending queue, RED-PSS, source, TSA table CDC group watch query Pending queue RED-PSS Source TSA table 1 evaluate the watch query per BDomain queue dependent loaders 2 pick up loaders 3 extract data 4 load TSA table 5 6 record the load in the CDC log
CDC process of the DBI connector

Once the CDC group has detected a change, all dependent loaders are queued (DBIADM.DBI_CDC_PENDING) and are scheduled and executed by RED-PSS (see the figure above). Load processes are recorded in DBIADM.DBI_CDC_LOG and DBIADM.DBI_CDC_LOG_DETAILS.

The CDC type of each table is stored in DBIADM.DBI_LOADER:

CDC TYPE

DESCRIPTION

FULL

The entire content of the source table is read at each load; the RED framework filters the changes for further processing.

INCR

Only changed records are transferred. The changed records are identified by an SQL expression in DBI_LOADER which returns an integer, date, or timestamp value.

CT

Change tracking: any near-real-time tracking of data changes in the supported databases. The method delivers the keys of the changed records and the change operation (insert, update, delete).

Generic statements for checking the status of a table and detecting changes in source tables are defined in the table DBI_CT_GROUPS. This could be Change Tracking within SQL Server, a trigger framework, or some other method of tracking changes. In the table DBI_LOADER_BD the CT group, and therefore the CT method, is defined for each table. If it is temporarily not possible to use the CT method, e.g. because change tracking has been disabled on the source table, a fall-back mechanism guarantees either a full or an incremental load, as defined in DBI_LOADER.

4.5.1.2. Configuration

Metadata stored in the DBIADM schema is used to configure the DBI connector. It is maintained via APIs. The main configuration objects are:

ADM_PARAMS

Settings for parallelisation and logging.

ADM_JOBS

Control data for regularly running jobs, e.g. the watcher of the CDC groups.

DBI_CDCG_WATCH

Defines where the trigger for starting the load of a group of tables is found and what it looks like. The status of the trigger is queried regularly and stored in DBI_CDCG_WATCH_STATUS. If changes are detected, a new load process is started (entry in DBIADM.DBI_CDC_LOG).

DBI_CDCG_WATCH_BD

Optional division of the trigger into different BDomains.

DBI_LOADER

Tables that are loaded together, i.e. share a common trigger (CDC group).

DBI_LOADER_BD

For each source table and BDomain: where the source table is found and into which TSA table it is loaded.

DBI_LOADER_COLUMNS

Source columns with a time data type with a precision higher than 1 millisecond. Exasol stores time values with a maximum precision of 1 millisecond, so these columns are converted to VARCHAR before being read from the source.

4.5.2. Common File Interface Connector (CFI)

The CFI connector is used to load and historise CSV, FBV, JSON, and Parquet files. Its mode of operation is very similar to the DBI connector, but currently only incremental loads are supported. A load is triggered by the arrival of a file in the input directory. For PSA tables of Type-2 (master data) a file contains the entire content of the table; for PSA tables of Type-3 (event tables) it contains the daily changes of one or several days.

JSON and Parquet files are converted to CSV by RED-PSS before they are imported. A Parquet file becomes a regular CSV file; a JSON array becomes one JSON document per row, whose key-value pairs are then extracted in the database.

Files can contain data for one or more business days. The business date is determined by the following rules (in descending priority):

  1. The business date is contained in the rows of the file.

  2. The business date is contained in the file name.

  3. Otherwise today’s date is used (yesterday’s date for the initial load).

4.5.2.1. Process

The following directories exist for each environment instance on the RED server:

data/input

Delivery of new files.

data/work

Files being processed.

data/error

Files that could not be loaded.

data/archive

Successfully loaded files.

Files are delivered into the input directory with the temporary extension .tra (upper or lower case). When the delivery is complete, the delivering process must remove this extension. From this point on the delivery is considered complete. Files ending with .tra are never processed, which prevents the import of incomplete files in the event of a process termination.

Files can also be delivered into sub-directories of the input directory, at any depth (e.g. data/input/east/hr/). A leading directory part of the file name pattern of a loader selects the sub-directory (e.g. ^east/hr/EMP_person_...). The protection of incomplete files and the conversion of JSON and Parquet files apply on all levels.

A watcher lists the files in the input directory and its sub-directories (except .tra files). New files are moved to the work directory, and RED-PSS queues and executes the loads in parallel. If the load is successful, the file is moved to the archive directory, otherwise to the error directory.

Behaviour in case of errors:

  • Network or system errors: the normal restart logic applies, as with the DBI watcher.

  • Rejected rows (rows that could not be loaded due to format errors):

    • FBV: the number of rejected rows must be 0. Otherwise the load ends with an error and the file is moved to the error directory.

    • CSV: the maximum number of rejected rows is configurable per loader (default 0). Rejected rows are written to the error table of the TSA table.

      • If the limit is exceeded, the load ends with an error and the file is moved to the error directory. The file must be corrected and delivered again.

      • If the number is above 0 but within the limit, a warning is shown, but the file is considered loaded and is moved to the archive directory.

4.5.3. Custom Connectors

Connector types are not limited to CFI and DBI connectors. Custom connectors of any type can be designed and developed if needed.

4.6. PSA Metadata Repository

The repository resides in the REPPSA schema and allows for the automatic generation of all PSA structures.

As soon as the loaders of the CFI and DBI connectors are defined and the TSA tables are created, the metadata for the PSA layer can be created in the REPPSA schema (see the APIs in API_REPPSA).

The metadata is stored in the following main tables:

REP_TSA_OBJECTS

TSA objects: schema and table name in the TSA layer, business release version, BDomain, and the expression that assigns the business date when loading data.

REP_TSA_COLUMNS

TSA columns: column name and data type, whether the column contains the business date, the position of the column in the IDC, BK, and BSK, whether the column references another table, and the visibility in the PSB layer. IDC and BK are only relevant for _HT tables.

REP_PSA_OBJECTS

PSA objects: schema and table name in the PSA layer, business release version, table type (SCD_TYPE), historisation type, and the treatment of deleted records.

REP_PSA_COLUMNS

PSA columns: column name and whether the column marks the end of the functional validity of a record (BDATE_TO).

REP_PSA_TSA_OBJECTS and REP_PSA_TSA_COLUMNS

Links between PSA and TSA objects and columns.

The API call API_REPPSA.COMPILE creates all necessary PSA tables and views, as well as the PSB views, based on this metadata. When an existing loader definition is adjusted, API_REPPSA.REFRESH_OBJECT imports the TSA column changes into the repository; API_REPPSA.COMPILE then generates and executes the corresponding ALTER TABLE statements automatically and updates the PSB views according to the view-text hash check (see Persistent Stage Base (PSB)).

4.7. Calendar Logic

A complete calendar solution to control the loading of data is a core part of the RED framework.

For each source table it is key to describe when the source system is ready to provide the data and therefore when the data should be loaded. Some data is loaded every day, seven days a week; other data is only available on business days, which vary with national holidays. Other data is only available on the last day of the month if that day is a business day, otherwise on the first business day after it. And some data is not ready at midnight, so the load can only begin at the time the data is available.

With the calendar logic, an individual load cycle can be defined for each object, considering business days, weekends, holidays, month and year ends, etc. A delivery offset can be defined if the source provides data with a known delay, and dependencies between loads ensure that certain data is only loaded once other data is available.

The view REDADM.RED_TSA_LOAD_CYCLE_STATUS shows load cycle mismatches, e.g. cycle overlaps. Once the system has been running for a while, it also shows actual loads without a matching cycle.

4.8. Object and Application Status

The framework tracks the entire load process, which results in a load status for every object, BDomain, and business date. This status can be seen in the view REDADM.STA_OBJECT_STATUS:

STATUS

MEANING

TSA_WAITING

the expected delivery time has not been reached yet; nothing is missing

TSA_MISSING

the expected delivery time has passed without data; the delivery is missing

PSA_MISSING

TSA data has arrived, but no PSA load has been created yet

NOT SCHEDULED

the PSA load is created but waits for prerequisite loads or the scheduler

SCHEDULED

the PSA load is scheduled

RUNNING

the PSA load is running

LOADED

the PSA load has finished for the business date; more deliveries for this business date may still arrive

FINISHED

the PSA load has finished and the TSA delivery of all feeds of the object is closed for the business date; downstream processing may start

ERROR

the PSA load failed or error rows were captured; see the alert reason

CANCELLED

the PSA load was cancelled

The column LAST_PSA_FINISHED shows the timestamp of the last completed load for the business date and BDomain. The columns ALERT (0 = OK, 1 = warning, 2 = error) and ALERT_REASON indicate whether action is needed, e.g. for TSA_MISSING or ERROR.

All status values, their severity, and their description are listed in the status catalogue REDSYS.VIS_STATUS_CODES.

The statuses of several objects can be combined into an application status.

4.8.1. Applications

Applications are logical combinations of database objects. An application has a name and assigned database objects (components). Objects are added to an application via search rules.

An application serves as a unit whose state can be queried. This state is the result of the states of all objects belonging to the application. If, for example, the data of all objects of an application has been loaded and historised successfully for a business date, the application state for this business date is ‘FINISHED’.

Furthermore, actions can be associated with applications, which are executed when a specific state is reached.

The view REDADM.RED_APP_STATUS (or REDADM.STA_APP_STATUS) contains the following status columns:

COLUMN

SWITCHES TO FINISHED WHEN …

TRIGGER TYPE

LOADED_STATUS

all components have reached LOADED or FINISHED for the business date, except those that are not yet expected according to their load cycle (TSA_WAITING); switches first

LOADED

PRELIM_STATUS

all tables expected on the calendar day have been loaded; components that are delivered days later (e.g. month-end data) may still be missing

PRELIM, PRELIM_DQ

FINAL_STATUS

all components of the business date have been loaded, including late deliveries; switches last

FINAL, FINAL_DQ

MATERIALIZED_STATUS

all materialisation actions have completed successfully since the last data change

MAT

DQ_STATUS

The result of the data quality checks for the business date: OK, NOK, or NULL (no check result).

The progress of an unfinished application can be seen in the following count columns:

APP_OBJECT_COUNT

Number of components of the application for the business date.

WAITING_COUNT

Number of components whose TSA delivery is not yet due (TSA_WAITING).

LOADED_COUNT

Number of components in status LOADED.

FINISHED_COUNT

Number of components in status FINISHED.

PRELIM_STATUS and FINAL_STATUS switch back to ‘UNFINISHED’ if at least one component of the application is reloaded. After a successful load they reach ‘FINISHED’ again (provided all other components are ‘FINISHED’ at that time). The same applies to LOADED_STATUS, except that the individual object status only needs to be ‘LOADED’.

4.8.2. Data Quality

Data quality (DQ) checks evaluate the business context and completeness of the data at application level, after the data has been loaded and historised.

  • A DQ check is a view APB_DQ.RDQ_<APP_NAME> for a registered application with exactly the columns BDATE, DQ_STATUS (OK or NOK), and DQ_REASON, one row per business date.

  • The framework evaluates the view as soon as the application reaches a preliminary or final state and stores the result in REDADM.RED_APP_DQ_STATUS.

  • A new NOK result is reported by mail to the recipients of the parameter dq_mail_recipients in REDADM.ADM_PARAMS.

  • An Application Manager can accept a NOK result for the current data version with API_REDADM.SET_DQ_APPROVAL; a justification is mandatory. The approval is valid until new data arrives.

Important

Actions with the trigger types PRELIM_DQ and FINAL_DQ only start if the DQ status is OK (or an approved NOK). A business date without a DQ result (NULL) blocks these actions as well and cannot be approved.

4.8.3. Triggering Actions (Finished Types)

Actions are executed automatically when the application reaches a specified state. When defining an action, the trigger condition is specified via the parameter finished_type:

FINISHED TYPE

TRIGGERS WHEN …

LOADED

LOADED_STATUS switches to ‘FINISHED’

PRELIM

PRELIM_STATUS switches to ‘FINISHED’

PRELIM_DQ

PRELIM_STATUS is ‘FINISHED’ and DQ_STATUS is ‘OK’

FINAL

FINAL_STATUS switches to ‘FINISHED’

FINAL_DQ

FINAL_STATUS is ‘FINISHED’ and DQ_STATUS is ‘OK’

MAT

MATERIALIZED_STATUS switches to ‘FINISHED’

The DQ-aware triggers (PRELIM_DQ and FINAL_DQ) are highly recommended for actions that export or transmit data to external systems (such as export_sftp or jexec). Data quality issues then halt the workflow and prevent poor or corrupted data from propagating.

The trigger type MAT allows multi-stage workflows: a materialize action is triggered by FINAL or FINAL_DQ, and the dependent exports or jobs are triggered by MAT once the materialisation has finished.

4.8.4. Application Actions

The following action types exist:

ACTION TYPE

DESCRIPTION

email

notification by mail, e.g. after a successful load of a business date

export_mail

notification by mail with an attachment, e.g. a CSV file with the data of the application

export_sftp

transfer of an exported file via sFTP

export_file

export of a file to a local directory on the RED server

jexec

execution of an external job, e.g. a Control-M job

sexec

execution of a LUA script

materialize

materialisation of tables and views for performance reasons; can only be configured in centrally managed application marts (APB)

The execution of an action can be controlled further:

run_once

If false, an action that has already run is executed again when the data of the application changes afterwards (status OUTDATED). Default true.

run_delay

Delay in seconds after the last data change before the action is queued. Default 0.

bdate_retention and bdate_retention_future

Trigger window in days backwards and forwards from today; business dates outside the window are not triggered. Defaults 30 and 0; negative values mean unlimited.

cal_name and cal_key

Restrict the execution to the days of a calendar, e.g. business days only.

At most one execution per action and business date is open (queued, scheduled, or running) at a time. Failed actions are retried automatically:

  • A failed action is retried after 300 seconds. After 99 failed attempts it waits until the error is reset with redctl we_reset_error -s REDADM -w app_act.

  • If new data arrives for the business date, the action is retried without delay.

  • With run_once true, a failed action is retried until it has succeeded once.

The status of the actions can be monitored in REDADM.STA_APP_ACTION_STATUS and REDADM.STA_APP_ACTION_DETAIL.

4.9. Partial Restore on Table Level

Another advantage of the bitemporal historisation is the ability to perform a partial restore on table level without having to restore the whole database. Selected PSA objects of selected BDomains are reset to their state at a point in time. Other objects and BDomains are not touched.

A partial restore works as follows:

  1. Initialise the restore with the point in time, a description, and whether the TSA data should be removed as well.

  2. Add the objects and BDomains to be restored (exact names or regular expressions).

  3. Execute the restore. During execution the framework is suspended; all changes are applied in a single transaction:

    • All PSA rows loaded at or after the restore point are deleted, and the versions that were valid at the restore point are reopened.

    • Optionally, the corresponding TSA data is deleted, so that the data can be delivered and loaded again.

    • Application actions created after the restore point are reset, so that they run again after the reload.

  4. After the restore the framework is resumed (optionally), and the PSA loads of the affected objects run again.

Only one restore can be open at a time. The restore history is kept in REDRST.RST_LOG and REDRST.RST_OBJECTS. The APIs are available in the schema API_REDRST.

4.10. Delete Logic

Over time the amount of data stored in a data warehouse inevitably grows. However, most likely not all data needs to be retained indefinitely: some data might only be needed for a few months, other data needs to be retained for a number of years for regulatory reasons.

The framework uses erase groups for deleting PSA data; TSA data is only retained for a few days anyway. Objects are assigned to an erase group via rules with include and exclude patterns (regular expressions) for BDomain, schema, and table.

Each erase group defines the following retention periods:

Standard retention

Rows whose functional validity ended (BDATE_TO, or BDATE for Type-3 tables) longer ago than this number of days are deleted.

Closed-row retention

Technically superseded rows (TDATE_TO) older than this number of days are deleted.

Special calendar retention (Type-3 tables only)

Some data, e.g. month-end data, may need to be kept longer. The days of a special calendar are kept for their own, longer retention period.

If a PSA table is not covered by an erase group, its data is never deleted. An object must not be covered by more than one erase group; otherwise the deletion run is aborted.

The deletion runs weekly (Sunday). A protocol of each run is written to REDADM.RED_PSA_DEL_CONTROL and REDADM.RED_PSA_DEL_CONTROL_DETAIL, including how many rows were deleted from which table and with which load run they had been loaded.

4.11. Data Lineage

The view REDADM.STA_PSA_OBJECT_LINEAGE shows the table lineage from the source via the TSA table to the PSA table.

Full technical column lineage from the source to the dashboard is available as a separate module. It shows in detail how individual data fields are created and transformed, which is crucial for data accuracy and quality. See the Data Lineage Guide for detailed information.

Additionally, an external governance tool can be integrated to ensure that all compliance requirements are met.

4.12. Data Patching (RED Correction Framework)

The patch framework is used to correct data that has already been persisted in the PSA layer. This is especially important if the source can no longer be changed or is no longer comprehensible.

Important

A patch never just updates existing data. Patched data undergoes exactly the same historisation process as data coming in through the TSA layer, without exception. Therefore, the original data is preserved by technical historisation.

The schema REDPAT contains the patch management API and the following metadata tables:

PAT_PATCHES

One row per patch with its lifecycle.

PAT_OBJECTS

The PSA objects (optionally restricted by a filter) that are patched by a patch.

PAT_USERS

The users who are allowed to edit the data of a patch.

PAT_AUDIT_LOGS

Every DML statement executed by the patch users on the patch data. This allows for full traceability of the changes. It requires Exasol auditing to be enabled.

4.12.1. The Patch Process

Patch lifecycle: STARTED, PREPARING, PREPARED, APPLY, APPLY_SCHEDULED, APPLYING, APPLIED; restart and abort transitions STARTED PREPARING PREPARED APPLY APPLY_SCHEDULED APPLYING APPLIED prepare all objects prepared users edit data, then apply ABORTED abort restart
Lifecycle of a patch
  1. Start the patch: a patch ID is assigned (status STARTED).

  2. Add the PSA objects to be patched and assign the users who may edit the patch data.

  3. Prepare the patch: asynchronous processes copy the currently valid data of the selected objects into a temporary patch schema PAT_<patch ID> (status PREPARING, then PREPARED).

  4. Edit the data: the assigned users change the patch tables with UPDATE, INSERT, or DELETE.

  5. Apply the patch: the patched data is processed like any other TSA load and appears in the PSA as new versions (status APPLY, APPLY_SCHEDULED, APPLYING, and finally APPLIED). The patch schema is dropped afterwards.

A patch in status PREPARING or PREPARED can be restarted, i.e. set back to STARTED to change its definition. A patch can be aborted in any status except APPLIED.

Important

A DELETE in a patch table does not end the history of a row, because a patch always acts like an incremental load. To end the validity of a row, set its column RED_ACTIVE to false.

Note

A patch object may be any PSA table as well as any ERR table.

Aborted patches and the data of applied patches are removed by a housekeeping job after a retention period (default 90 days); the patch metadata is kept.

4.13. The RED Registry

All metadata schemas have a view named RED_REGISTRY, which represents a configuration snapshot or, in other words, a logical backup. This view reverse-engineers all ADD operations that are needed to recreate the exact same metadata state for all tables of a schema in an empty framework. By executing all commands of the RED_REGISTRY view in the order described below, the same state can be recreated easily.

Important

As some ADD functions rely on other ADD functions being executed beforehand, select the list of ADD functions from the RED_REGISTRY view using order by order_col to fulfil these dependencies.