Hierarchical Link

A Hierarchical Link stores parent-child pairs of one Hub, such as an employee and their manager or an item and its component, with the hash key Link_HK, in a table of the Raw Vault layer. Use it for a hierarchy inside one business object. After each load, the result view shows one row per pair and the load that first delivered it.

Sources and relationships

A Hierarchical Link reads Stages, RawVault Composites and Store Entities of a linked Data Store project, in one or more dataflows. It has exactly two relationships, both to the same Hub, with roles, for example EMPLOYEE and MANAGER. The foreign-key terms are the business keys of the Hierarchical Link. See Use parent-child relationships.

Link Satellites, Record Tracking Link Satellites, Effectivity Link Satellites reference a Hierarchical Link. PIT, Current View, History View, BusinessVault Composite, Vault Out and the Business Vault types can read it as a source. A Current View on a Hierarchical Link and its Link Satellite is the usual source of a Hierarchy Flatten.

Create a Hierarchical Link

• With the wizard: open the menu of a Stage, RawVault Composite or Store Entity, choose Automate and then Create Hierarchical Link. Its steps are Create Model Object, Create Dataflow, Create Multiple Relations, Define Roles, Map Foreign Key Terms and Add to View. See Create a Model Object with the wizard.
• Manually: create a model object of type Hierarchical Link in the Raw Vault layer, add two relationships to the Hub with a role each, create a dataflow set, add the source model object and map the foreign-key terms.

Properties

• Implementation Type: Permanent only.
• Foreign Key Mode: whether the table gets foreign keys to the Hub table. With Create Disabled, the default, they are created and disabled.
• Load Caching: empty (default) or Hashing, which stores the hashing view in a table before the loader runs.
• Custom Singleton Values: values of the unknown-member row.
• Deduplication Method and Sort Order within Partition, per dataflow.
• Use Embryo Loader, per relationship: inserts business keys that the Hub does not hold yet.

Default Terms

• Link_HK: hash of the business keys of both relationships and primary key of the table.
• DVLINK_ForeignKey_Identity: names one hash-key column per relationship, <Hub name>_<role>_HK, for example common_employee_MANAGER_HK, with the key of the related Hub row.
• BG_LoadTimestamp and BG_SourceSystem: the load that inserted the row and its source system, empty unless you map it.
• BG_DataQuality_ID, BG_ErrorCount and BG_ErrorDescription: hidden terms of the data quality objects.

A row without a parent has a NULL parent key when that foreign-key term is nullable. The Hierarchical Link then stores the key of the Hub's unknown-member row for the parent. See Behaviour for Nulled Business Keys in Links and How biGENIUS-X builds hash values.

Generated objects

These are the names on Microsoft SQL Server for the Hierarchical Link load_employee_hlnk with one dataflow:

• Loaders are stored procedures; the Hierarchical Link table and the hashing table are tables; all other objects are views.
• The deduplication view exists only with a Deduplication Method other than None.
• The hashing table and hashing loader exist only with Load Caching Hashing. The delta view then reads the hashing table.
• The embryo loader exists only when Use Embryo Loader is true on a relationship. It runs before the loader.
• With several dataflows, all objects except the table and the result view exist once per dataflow, with the dataflow name in their names.
• The table script inserts the unknown-member row with hash keys of zeros. The result view hides it.
• The result view always exists, independent of Create Result Views.

With the default settings, a load runs this chain:

sources -> _Source -> _Hashing -> _Delta -> _Loader
_Loader -> RDV_HLNK_load_employee_hlnk -> _Result
_Result -> _Delta (pairs already stored)

The hashing view calculates Link_HK and the Hub key of each role. The delta view keeps the rows whose Link_HK is not in the result view, and the loader inserts them. The loader never updates or deletes rows, so a pair that the source no longer delivers stays in the table. A Link Satellite on the Hierarchical Link stores the history of each pair. With the optional objects, _Deduplication sits between _Source and _Hashing, _HashingLoader fills _HashingTable before the delta view reads it, and _EmbryoLoader writes missing keys to the Hub table.

Example

The Hierarchical Link load_employee_hlnk on Microsoft SQL Server reads the Stage EMPLOYEE. Both relationships go to the Hub common_employee, with the roles EMPLOYEE and MANAGER. MANAGER_ID is nullable in the source. The loads deliver these rows:

• Load 1 on 2026-10-28 08:00: employee 101 without a manager, employees 102 and 103 with manager 101.
• Load 2 on 2026-10-29 08:00: employee 103 now reports to 102, and employee 104 is new with manager 102.

Check the result

Query the result view, here in the schema rv:

SELECT FK_common_employee_EMPLOYEE_EMPLOYEE_ID AS EMPLOYEE_ID,
       FK_common_employee_MANAGER_EMPLOYEE_ID AS MANAGER_ID,
       common_employee_MANAGER_HK AS MANAGER_HK, BG_LoadTimestamp
FROM rv.RDV_HLNK_load_employee_hlnk_Result
ORDER BY EMPLOYEE_ID, BG_LoadTimestamp;

This is the result after load 2, with hash keys shortened to their first four bytes:

EMPLOYEE_ID  MANAGER_ID  MANAGER_HK  BG_LoadTimestamp
101          NULL        0x00000000  2026-10-28 08:00
102          101         0x6348AE9A  2026-10-28 08:00
103          101         0x6348AE9A  2026-10-28 08:00
103          102         0x8887BDD9  2026-10-29 08:00
104          102         0x8887BDD9  2026-10-29 08:00

• Employee 101 has no manager, so MANAGER_HK holds the key of the unknown-member row, all zeros.
• Employee 103 has two rows: the pair with manager 101 stays after load 2 adds the pair with manager 102.
• MANAGER_HK 0x6348AE9A is the Hub key of employee 101.

Platform differences

• Snowflake: names are in capitals, and the loader is a stored procedure. The load control task LC_RDV_HLNK_<model object name>_TASK calls it. The dataflow properties Use Stream and Warehouse Placeholder Variable Name apply.
• Databricks and Fabric Lakehouse: table and view names are in lowercase, and the loader is the notebook RDV_HLNK_<model object name>_Loader. The properties File Format, Optimization Method, Optimization Method Columns and Use Sequential Dataflow Execution apply. Fabric Lakehouse allows only Foreign Key Mode None.
• Databricks dbt and Snowflake dbt: the Hierarchical Link becomes the dbt models rdv_hlnk_<model object name>_source and rdv_hlnk_<model object name>. The model rdv_hlnk_<model object name>_result exists only when Create Result Views is true; the default on dbt is false. Load Caching and Use Embryo Loader are not available.
• Fabric Warehouse: the same objects as on Microsoft SQL Server, deployed by notebooks.
• Oracle: names are in capitals, and the loader is a PL/SQL package. Partition Clause adds its text to the CREATE TABLE statement. Table Compression, the tablespaces and the statistics properties also apply.
• Foreign Key Mode Create Enforced exists only on Microsoft SQL Server and Oracle.

Limitations

• A Hierarchical Link has exactly two relationships to the same Hub and no terms other than foreign keys and Default Terms. Validation reports an error otherwise.
• A Hierarchical Link holds one level, the direct parent. A Hierarchy Flatten resolves all levels.
• A dataflow that delivers the same pair twice in one load makes the loader fail with a primary key violation. A Deduplication Method removes such duplicates before hashing.

Release differences

• Generator 2.2: Use Embryo Loader is available.
• Generator 2.3: Custom Singleton Values is available.

Related articles

• Previous: Hub
• Next: Link Satellite, Hierarchy Flatten
• Reference: Link_HK, DVLINK_ForeignKey_Identity, Foreign Key Mode (Model Object), Load Caching (Model Object), Custom Singleton Values (Model Object), Behaviour for Nulled Business Keys in Links (Project), Create Result Views (Project)
• See also: Use parent-child relationships, Link, Embryo Loader, How biGENIUS-X builds hash values, Create a Model Object with the wizard