Creating Data Vault Stage Tables

The following describes the steps for creating a Data Vault Stage table:

  1. Browse to the Data Warehouse (click Browse > Data Warehouse) connection to create the Data Vault Stage table.
  2. Double-click the Stage object group in the left pane, the middle pane displays a list of existing Stage tables. 
  3. Click the source Load table from the right pane and drag it to the middle pane. The selected Load table must have the required DSS columns (dss_record_source and dss_load_date).

  4. The Add a New Metadata Object screen appears and classifies the new object as a Stage table. It provides a default name based on the Load table name. Accept the name or enter a new name for the Stage table and click ADD to continue.
  5. On the Table Properties screen, select the Data Vault Stage option from the Table Type drop-down.

    1. You can set this table type to be the default by configuring the Default Sub Type for Stage Table Objects setting in the Tools > Options > Object Types > Object Sub Types screen.
    2. RED displays the name of the previously used update procedure template by default, below the Update Procedure drop-down.
  6. Click OK in the table Properties screen to launch the Wizard that enables you to define the source columns for the Hub, Link, and Change hash key columns.

  7. Select the source column(s) to define the first Hub hash key. The Hash Key Name is formed based on the prefix (defined in the Tools > Options > Global Naming Conventions > Global Naming of Key Columns settings) and the source column(s) name. You can manually change the name if required.

  8. Click Add to create the first Hub hash key. Repeat the same steps as required. Use the Modify button to edit a selected hash key. The Delete button enables you to delete a selected hash key. Repeat the same steps as required.  

    A confirmation message is displayed when you attempt to modify or delete a hash key that is being used by other objects in RED.

  9. Once all the required Hub Hash Keys are created, click Next to progress to the Link Hash key generation screen.

  10. Creating the Link Hash Keys involves the same process, follow the previous steps (6 and 7) to select multiple source columns to combine to create the Link hash key. Click Add to create the first Link hash key. Repeat the same steps for any subsequent keys. Once all the required Link Hash Keys are created, click Next to progress to the Change hash key generation screen.

    1. Custom aliases for the dss_change_hash column can be defined in the Tools > Options > DSS Tables and Columns > DSS Columns to Include and Naming window. If custom DSS aliases are defined then the aliases are used for the column names added to the new Load tables. Refer to DSS Columns for details.
    2. The data type defined for the dss_change_hash column in Tools > Options > Data Vault window is applied when creating a satellite change hash key. Refer to Data Vault Hash Keys setting for details.


  11. Follow the same steps to select the columns to use for the Change Hash Key. Select the Show hash key source columns check-box below the screen to display and use hash key source columns in defining the Change Hash keys. Also, select from the Satellite Type drop-down list the option to define your Multi-Active key:
    1. Satellite: Select the columns from the Available Columns list by moving them to the Selected Columns box, type a name in the Hash Key Name, and click Add. Also, any Hash Key can be edited, by clicking Modify; otherwise, you can click Delete to remove it from the list.


    2. Multi-Active Natural: Select the columns from the Available Columns list by moving them to the Selected Columns box, type a name in the Hash Key Name box, and click Add. Also, any Hash Key can be edited, by clicking Modify; otherwise, you can click Delete to remove it from the list.
      When you select this option, the Multi-Active Key drop-down list displays, allowing you to select any stage table column except for any DSS or selected column for the Change Hash Key sources.


    3. Multi-Active Sequence: Select the columns from the Available Columns list by moving them to the Selected Columns box, type a name in the Hash Key Name option, and click Add. Also, any Hash Key can be edited, by clicking Modify; otherwise, you can click Delete to remove it from the list.

      By default, this option displays the Multi-Active Key box with the 'ma_sequence_key' name. When a Multi-Active Sequence key is created, the Sequence Options tab appears.


    4. Sequence Options: Select a Hash Key Multi-Active Key Sequence and click Modify. Add the columns you want to sort from the Available Columns, select Update, and click Finish.


  12. Click Finish, once you have defined the required list of descriptive columns for the Change hash key. The new Data Vault Stage table is added to the Stage Table objects list in the left pane and the columns included in the table are listed in the middle pane.

    In addition to the columns defined from the Load table, the following columns and their metadata have been added to the Data Vault Stage table:

    • The Hub Hash Keys
    • The Link Hash Keys
    • The Change Hash Key
    • The DSS_CREATE_TIME column
    • The DSS_UPDATE_TIME column

    The metadata for the hash columns includes the source columns that were used to create them (used to generate the hash keys).

    The hash keys created are used in the subsequent Data Vault object (Hub, Links, and Satellites) creation Wizards. 

    You can review the composition of existing hash keys for a Data Vault Stage table (Hub, Link, and Satellite) and create additional hash keys by selecting the Maintain DV Hash Key Columns option from the selected Data Vault Stage table’s context menu. This launches the hash key generation Wizard which enables you to maintain the source columns defined for the hash keys.

    To remove or change a hash key column, you need to delete it first, e.g. right-click the column listed in the middle pane and then select Delete Column from the context menu.


  13. Right-click the new Data Vault Stage table you defined from the left pane, under the Stage Table objects list, and select Create (ReCreate) from the context menu to create the table. The Results pane displays confirmation that the Data Vault Stage table was successfully created.

    Once the new Data Vault Stage table is defined and created, clicking the Rebuild button in the Table Properties screen launches the Wizard to generate the procedure to populate the table. This Wizard utilizes a template to create the procedure.

    The detailed steps for using this Wizard are described in the next section, Generating Update Procedures for the Data Vault Stage Table.

  • No labels