Transactional link
Transactional links are used for storing non-historized transaction or event data.
Transactional links do not store change history. Transactions or events stored to a transactional link should not receive changes.
The primary key (dv_id) is hashed from a unique business identifier for the transaction or event.
Transactional links contain references to hubs like a regular link but can also contain descriptive and measurable attributes; they are comparable to fact tables and are commonly used as the basis for a fact.
A transactional link has performance benefits over a hub + satellite + link combination especially when the data volume is high as fewer joins are required when loading data from the Data Vault to the publish zone.
Creating a transactional link
Follow these steps to create a transactional link:
Select or create a package and create a new entity, choose type LINK and physical type TABLE.
Navigate to the source entity (staging table) and create an outgoing load selecting the hub as target entity.
Create an entity mapping as follows:
Source attribute | Transformation | Target attribute | Notes |
|---|---|---|---|
Transaction business key attribute(s) in source entity. | HASH | dv_id | Map the unique business identifier attribute(s) for the transaction/event into the dv_id. For example, this could be the order id & order line id if you are creating a transactional link for order line. Another example could be a combination of a business key and a timestamp if you are creating a transactional link for an event. |
- | CURRENT_TS | dv_load_time | Load timestamp, does not need any modifications by default. |
- | RUN_ID | dv_run_id | Run ID (see Using Run ID Logic), does not need any modifications by default. |
Source name attribute in source entity. | NONE / CUSTOM | dv_source | Best practice is to have the source name as a metadata attribute on staging entities and to map it to Data Vault entities without any transformations. Alternatively, a CUSTOM transformation can be used e.g. with a source name or an environment variable given in single quotes. However, it is best to avoid the CUSTOM transformation type if possible. |
Business key attribute(s) in source entity. | HASH | dv_id_hub_1 | Create a new attribute for each hub reference and map the source attributes. Note that if you add a foreign key reference from the link keys to the hub dv_ids beforehand, you can use the same mapping as in the primary key automatically by selecting Copy by Reference. |
dv_id_hub_2 | |||
dv_id_hub_n | |||
Descriptive or measureable attributes from the source entity. | NONE | … | Map all the required descriptive and measurable attributes.
|
Use the Export SQL feature to preview entity SQL & load SQL.
Naming conventions
Note that you can modify the default entity and attribute naming used in the examples in the CONFIG_ENTITY_DEFAULTS configuration package to fit your naming conventions. See more in Customizing default attributes and transformations
Notes
You can use the default LINK entity type for transactional links as well.
Define a naming convention for transactional links, for example: L_T_<descriptive_name>, L_T_ORDER_LINE.
For use cases with high volumes of data, see the example for Using Run ID Logic together with Timeslot Logic. This load pattern can greatly reduce load times and enable even high-frequency near real-time use cases.