Home › Reference Guide › Packages & Workflow › Historization package

Historization package

This package is used to historicize data. One package can be used to define multiple historizations.

Note:
Historicizing Data refers to the process of tracking and storing changes to data over time. Instead of just storing the current state of the data, historicizing data ensures that previous versions or states are preserved. This allows organizations to analyze how data has evolved, which is useful for trend analysis, auditing, and reporting.

Below is a typical historization definition:

Historization Packages

Historization Options

  1. Missing record behavior: Describes the behavior when a primary key is missing in the source table:
    • Close: Closes the validity period of the corresponding key in the historicized table.
    • Add empty record: Closes the period and adds a new record with default "Empty value" columns.
    • Do not close: No action is taken; the key remains in the actual data.
  2. Insert only: If set, the source data is appended without historization (used when no primary key exists).
  3. Type: Selects the historization algorithm:
    • SSIS Package: Historization is done via an SSIS package.
    • Automatically created stored procedure: Procedure named [CFG].[HIST_TableName] is generated and executed.
    • Manually created stored procedure: Procedure with same name is manually editable. Use auto-generated procedure as a starting point.
  4. Optional statement to calculate ValidFrom date: Define a custom SQL expression (returns date or datetime) to calculate the validity start date for new/existing keys.
  5. Insert Filter and Delete Filter:
    • Insert Filter: Restrict which source records get historicized.
    • Delete Filter: Restrict which records can be "closed" when primary keys are missing.
  6. SCD Type: Choose historization logic per field:
    • None (SCD 0): No change tracking; current value only.
    • SCD 1: Changes overwrite historical values.
    • SCD 2: Adds new records for changed values, maintaining validity periods.
  7. Calculated columns: Define derived columns using previous ([S]) and current ([I]) values, e.g.: 
    ISNULL(I.Amount, 0) - ISNULL(S.Amount, 0)
  8. SSIS variables: Use @VariableName format to reference variables for filters. Define values via [CFG].[SSIS_Configuration].
  9. Scripts: Define pre- or post-historization SQL scripts using the Scripts tab.