Using Data Vault Tools

Go Up to Using ER/Studio Data Architect

The ER/Studio Data Architect Data Vault toolset allows users to create a related warehouse. While this toolset does include simple templates for Data Vault objects, it also aims to:

• leverage the Enterprise Data Model
• avoid the pitfall of a "source system driven" Data Vault

Leveraging the Enterprise Data Model which forms the model of the business allows you to refactor a third-normal form business model into Hubs and Links. This step forms the backbone of a Data Vault. As a good enterprise model lists the primary keys and alternate keys of entities based on agreed attributes across the organization, it provides an ideal starting point to define Hubs. This toolset helps the user through the process.

Avoiding the pitfall of a "source system driven" Data Vault helps circumvent issues down the road. For example, where you have a number of systems that contain customer data, there is a temptation to create multiple hubs for each system's customer data, and then deal with the consolidation and reconciliation later. This idea goes against the principles of Data Vault. The Data Architect toolset helps you review tables in a source system, and then select from an existing palette of hubs and links to create satellites. It may be that a separate group of architects manage the core palette of hubs and links to the group that creates satellites and the loading of source data.

This toolset includes automation code that takes away the laborious aspects of Data Vault creation and focuses on the design and architecture to deliver a well-planned, business-driven data warehouse consistent with the wider organization.

Getting Started with Data Vault Tools

ER/Studio Data Architect provides data vault tools for you to customize as needed. You can add to or change macros to best suit your environment and workflow.

Adding and Deleting Macros

ER/Studio Data Architect 20.6 and later include a set of workflow macros that generate Data Vault assets from model objects. Once installed, you can view this list from the Macro tab in the product, as shown in the following image.

You can add and delete macros as desired using the right-click menu options. Note that these macros carry names that are descriptive, but may not fit into your vernacular. For this reason, you can easily change macro names by right-clicking the name you want to change, selecting Rename Macro, and then typing the new name before pressing the Enter key to save.

You also can edit a macro using the same approach, but select Edit Macro instead. The ER/Studio Data Architect Macro Editor allows you to customize a macro to suit your needs.

Adding to Shortcuts

Another important step in setting up your Data Vault tools is to add options for hubs, links, and satellites to your shortcuts. We suggest you use the Entity Right Mouse option so that you can quickly right-mouse click on an Entity, and then compete the dialog to create the desired hub, link, or satellite. Make sure that you add the shortcut for all three items.

To add an item to your shortcuts

1. In the Macros directory tree, right click the item you want to add.
2. Select Add/Remove to Shortcuts > Entity Right Mouse. A checkmark appears next to the selected item. Note that this is a toggle, so when you want to remove an item from the menu, simply follow the same steps and verify that the checkmark is no longer next to that item.

   

Creating a Hub

You can use a logical model to create a hub and link structure, and/or select a physical model to create satellites. The common workflow is to use an existing logical in third normal form, such as the enterprise logical data model which forms an agreed business model. This toolset allows you to refactor entities from this model into Data Vault Hubs and Links.

Choosing the business keys is vital to this process, as you want to make sure that you identity data by the most commonly-accepted method.

To create a Hub

1. From your model, locate the Entity/Table that you want to form a Hub.
2. Right-click on that entity, and then select Macros > Hub. The Hub creation dialog appears.

   

3. Complete the appropriate fields including the following items:
   1. Change the Hub Name and Prefix to add more descriptive information, if desired.
   2. In Select Business Key(s) Columns, select the Column(s) you want to form the business key. You can browse the existing primary and alternative keys of the entity to decide which attributes you wish to use to form your business key.
   3. In Select Dictionary, choose the Dictionary you want to extend to include Domains for Data Vault. You can use the default settings.
   4. Check Generate Visual Data Lineage if you want to generate a diagram for the mappings. Generally, you do not include lineage mappings for Hubs.
4. Click Start to execute the macro. From the selected Entity/Table, the macro:

   • Creates a Hub object in the selected submodel.
   • Produces a standard name for the Hub composed of the prefix of "h_" to denote that it is a hub, and the user-selected name for the hub.
   • Creates the business key(s) from selected attributes.
   • Creates a hash key using a standard name ("hk_" + hubname) as the Primary Key.
   • Creates the standard Data Vault timestamps and source data from the standard DV Domains:
     • dss_record_source
     • dss_load_date
   • Displays details of the source object used to create this hub, including Model, submodel, and object that was used to design this object.
   • Links the Hub to the original Entity/Table via a simple line.
   • Generates Visual Data Lineage components, if requested.

Creating a Satellite

Select the Entity/Table that you want to use to form a Satellite. This process creates a well-formed Satellite object, and then attaches it appropriately to a hub.

You can select a hub from a list already created.

To create a Satellite

1. From your model, locate the Entity/Table that you want to form a Satellite.
2. Right-click on that entity, and then select Macros > Satellite. The Satellite creation dialog appears.

   

3. Complete the appropriate fields including the following items:
   1. In the Hubs| Links field, select the Hub you want to attach to from a list of all available hubs. The default is Select Hub/Link unless a Hub was already created from the Entity/Table, and is thus linked via a line using the Hub Creation macro.
   2. Change the Satellite Name, Prefix, and Suffix to add more descriptive information, if desired.
   3. In Select Changing Columns, select the Column(s) you want to include in the Satellite.
   4. In Select Dictionary, choose the Dictionary you want to extend to include Domains for Data Vault. You can use the default settings.
   5. Check Generate Visual Data Lineage if you want to generate a diagram for the mappings.
4. Click Start to execute the macro. From the selected Entity/Table, the macro:

   • Creates a Satellite object.
   • Produces a standard name for the Satellite composed of the prefix of "s_" to denote that it is a Satellite, the user-selected name for the Satellite, and the user-selected suffix concerning the nature of the contents of the Satellite.
   • Adds the selected attributes.
   • Creates a foreign key hash key from the selected Hub as the PK.
   • Creates the standard Data Vault timestamps and source data, from the standard DV Domains:
     • dss_change_hash
     • dss_record_source
     • dss_load_date
     • dss_version
   • Links the Satellite to the original Entity/Table via a simple line.
   • Links the Satellite to the Hub Entity/Table via an identifying relationship.
   • Generates Visual Data Lineage components.
   • Provides details on the source object used to create this Satellite, including model, submodel, and object used to design this object.

Creating a Link

Select a pair or group of Hub Entity/Tables that you want to connect to form a Link. This creates a well-formed Link object, and then attaches it appropriately to the hubs.

To create a Link

1. From your model, locate the Hub Entities/Tables that you want to connect to form a Link.
2. Click the first Entity, and then press the Ctrl key and click to add each additional Entity.
3. Right-click on one of the selected Entities, and then select Macros > Link. The Link creation dialog appears.

   

4. Complete the appropriate fields including the following items:
   1. Change the Link Name and Prefix to add more descriptive information, if desired.
   2. In Selected Hubs, select the Hubs you want to include in the link.
   3. In Select Dictionary, choose the Dictionary you want to extend to include Domains for Data Vault. You can use the default settings.
   4. Check Generate Visual Data Lineage if you want to generate a diagram for the mappings.
5. Click Start to execute the macro. From the selected Entity/Table, the macro:

   • Creates a Link object.
   • Produces a standard name for the Link composed of the prefix of "l_" to denote that it is a Link, the user-selected name for the Satellite which, by default, is the name of the two Hubs.
   • Creates foreign key hash keys from the selected Hubs.
   • Creates the standard Data Vault timestamps and source data, from the standard DV Domains:
     • dss_record_source
     • dss_load_date
   • Links the Satellite to the Hub Entity/Tables via non-identifying relationships.
   • Generates Visual Data Lineage components.

See Also

• Adding Macros to GUI Modeling Menus