SQL - DataVault example

In this example we create a DataVault structure. If you haven't done already, please read the Simple staging example first for a basic explanation of concepts used in the template and config. The tables implemented in this example are used in the SSIS DataVault example.

Model

In this example we will be generating DataVault tables for each entity and relation defined in the DWH model.

Templates

For generating DataVault tables we need multiple templates, namely a SQL template file for each table type (Hub, Link, Hub-Sat and Link-Sat) since each table type is a different technical pattern.

Entity_owner.H_Entity_name.sql

In the Hub template a section KeyAttribute is defined and used for column specification as well as for the unique constraint.

CREATE TABLE [Entity_owner].[H_Entity_name] (
  [H_Entity_name_SQN]        BIGINT NOT NULL IDENTITY(1,1),
  -- @XGenTextSection(name="KeyAttribute")
  [KeyAttribute_name]        KeyAttribute_fulldatatype    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  CONSTRAINT [PK_Entity_owner_H_Entity_name] PRIMARY KEY NONCLUSTERED ([H_Entity_name_SQN]),
  CONSTRAINT [UK_Entity_owner_H_Entity_name] UNIQUE(
    -- @XGenTextSection(name="KeyAttribute" suffix=",")
    [KeyAttribute_name]
  )
);
GO

Entity_owner.HS_Entity_name.sql

The Hub-Sat template has a section defined named NonKeyAttribute that is used to apply the non-key columns to the sattelite table.

CREATE TABLE [Entity_owner].[HS_Entity_name] (
  [H_Entity_name_SQN]        BIGINT NOT NULL,
  -- @XGenTextSection(name="NonKeyAttribute") 
  [NonKeyAttribute_name]     NonKeyAttribute_fulldatatype    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  [EndDateTime]              datetime2(2) NOT NULL,
  [RecordHash] VARBINARY(16) NOT NULL, 
  CONSTRAINT [PK_Entity_owner_HS_Entity_name] PRIMARY KEY NONCLUSTERED ([H_Entity_name_SQN], [LoadDateTime]),
  CONSTRAINT [FK_Entity_owner_HS_Entity_name_Entity_owner_H_Entity_name] FOREIGN KEY ([H_Entity_name_SQN]) REFERENCES [Entity_owner].[H_Entity_name] ([H_Entity_name_SQN])
);
GO

Relation_owner.L_Relation_name.sql

The Link table template has a section RelatedEntity that is used to define the appropriate technical Hub keys as columns to the table as well as to the unique constraint.

CREATE TABLE [Relation_owner].[L_Relation_name] (
  [L_Relation_name_SQN]      BIGINT NOT NULL IDENTITY(1,1),
  -- @XGenTextSection(name="RelatedEntity") 
  [H_RelatedEntity_parentReferenceName_SQN] BIGINT    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  CONSTRAINT [PK_Relation_owner_H_Relation_name] PRIMARY KEY NONCLUSTERED ([L_Relation_name_SQN]),
  CONSTRAINT [UK_Relation_owner_H_Relation_name] UNIQUE(
    -- @XGenTextSection(name="RelatedEntity" suffix=",")
    [H_RelatedEntity_parentReferenceName_SQN]
  )
);
GO

Relation_owner.LS_Relation_name.sql

Similar to the Hub-Sat table, the Link-Sat template also has a section named NonKeyAttribute.

CREATE TABLE [Relation_owner].[LS_Relation_name] (
  [L_Relation_name_SQN]      BIGINT NOT NULL,
  -- @XGenTextSection(name="NonKeyAttribute") 
  [NonKeyAttribute_name]     NonKeyAttribute_fulldatatype    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  [EndDateTime]              datetime2(2) NOT NULL,
  [RecordHash] VARBINARY(16) NOT NULL, 
  CONSTRAINT [PK_Relation_owner_LS_Relation_name] PRIMARY KEY NONCLUSTERED ([L_Relation_name_SQN], [LoadDateTime]),
  CONSTRAINT [FK_Relation_owner_LS_Relation_name_Relation_owner_L_Relation_name] FOREIGN KEY ([L_Relation_name_SQN]) REFERENCES [Relation_owner].[L_Relation_name] ([L_Relation_name_SQN])
);
GO

Documentation

For documentation on templates, please see Template.

Config

To generate the DataVault structure we need two configurations, one for the Hub and Hub-Sat tables that binds on entity and one for the Link and Link-Sat tables that binds on relation.

Config for Hub and Hub-Sat tables

The config is very similar to the one used to generate the staging table, however note that in this config multiple SectionModelBinding entries are added to bind NonKeyAttribute to attributes that have not been marked as primary and KeyAttribute to the attributes that are marked as primary. In other words, SectionModelBinding is used to route parts of the model to the appropriate sections in a template. In this case this enables creating a DataVault structure from a relational model (the model itself not being in DataVault structure).

<?xml version="1.0" encoding="UTF-8"?>
<XGenConfig>
  <Model/>
  <TextTemplate rootSectionName="Entity">
    <FileFormat currentAccessor="_" childAccessor="$" singleLineCommentPrefix="--" annotationPrefix="@XGen" annotationArgsPrefix="(" annotationArgsSuffix=")" />
    <Output type="output_per_element" />
  </TextTemplate>
  <Binding>
        <SectionModelBinding section="Entity" modelXPath="/modeldefinition/system/mappableObjects/entity" placeholderName="Entity">
            <Placeholders>
                <Placeholder name="System" modelXPath="../.." />
            </Placeholders>
            <SectionModelBinding section="Attribute" modelXPath="attributes/attribute" placeholderName="Attribute" />
            <SectionModelBinding section="NonKeyAttribute" modelXPath="attributes/attribute[not(boolean(@primary))]" placeholderName="NonKeyAttribute" />
            <SectionModelBinding section="KeyAttribute" modelXPath="attributes/attribute[boolean(@primary)]" placeholderName="KeyAttribute" />
        </SectionModelBinding>
  </Binding>
</XGenConfig>

Config for Link and Link-Sat tables

The config for Link and Link-Sat is similar to the config for Hub and Hub-Sat tables. The differences are that in this config, the sections are bound to relation instead of entity and an additional SectionModelBinding is defined for binding RelatedEntity to the references within a relation.

<?xml version="1.0" encoding="UTF-8"?>
<XGenConfig>
  <Model/>
  <TextTemplate rootSectionName="Relation">
    <FileFormat currentAccessor="_" childAccessor="$" singleLineCommentPrefix="--" annotationPrefix="@XGen" annotationArgsPrefix="(" annotationArgsSuffix=")" />
    <Output type="output_per_element" />
  </TextTemplate>
  <Binding>
        <SectionModelBinding section="Relation" modelXPath="/modeldefinition/system/mappableObjects/relation" placeholderName="Relation">
            <Placeholders>
                <Placeholder name="System" modelXPath="../.." />
            </Placeholders>
            <SectionModelBinding section="Attribute" modelXPath="attributes/attribute" placeholderName="Attribute" />
            <SectionModelBinding section="NonKeyAttribute" modelXPath="attributes/attribute[not(boolean(@primary))]" placeholderName="NonKeyAttribute" />
            <SectionModelBinding section="KeyAttribute" modelXPath="attributes/attribute[boolean(@primary)]" placeholderName="KeyAttribute" />
            <SectionModelBinding section="RelatedEntity" modelXPath="references/reference" placeholderName="RelatedEntity" />
        </SectionModelBinding>
  </Binding>
</XGenConfig>

Documentation

For documentation on the configuration, please see Config.

Output

When running CrossGenerate the output using the given Model, Templates and Configs will be as follows:

Hub tables

The Hub tables generated using the Hub template and Hub/Hub-Sat config.

BusinessVault.H_Country.sql

CREATE TABLE [BusinessVault].[H_Country] (
  [H_Country_SQN]        BIGINT NOT NULL IDENTITY(1,1),
  [Code]        nvarchar(3)    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  CONSTRAINT [PK_BusinessVault_H_Country] PRIMARY KEY NONCLUSTERED ([H_Country_SQN]),
  CONSTRAINT [UK_BusinessVault_H_Country] UNIQUE(
    [Code]
  )
);
GO

BusinessVault.H_Customer.sql

CREATE TABLE [BusinessVault].[H_Customer] (
  [H_Customer_SQN]        BIGINT NOT NULL IDENTITY(1,1),
  [Id]        int    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  CONSTRAINT [PK_BusinessVault_H_Customer] PRIMARY KEY NONCLUSTERED ([H_Customer_SQN]),
  CONSTRAINT [UK_BusinessVault_H_Customer] UNIQUE(
    [Id]
  )
);
GO

BusinessVault.H_Order.sql

CREATE TABLE [BusinessVault].[H_Order] (
  [H_Order_SQN]        BIGINT NOT NULL IDENTITY(1,1),
  [Id]        int    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  CONSTRAINT [PK_BusinessVault_H_Order] PRIMARY KEY NONCLUSTERED ([H_Order_SQN]),
  CONSTRAINT [UK_BusinessVault_H_Order] UNIQUE(
    [Id]
  )
);
GO

Hub-Sat tables

Generated using the Hub-Sat template and Hub/Hub-Sat config.

BusinessVault.HS_Country.sql

CREATE TABLE [BusinessVault].[HS_Country] (
  [H_Country_SQN]        BIGINT NOT NULL,
  [Description]     nvarchar(100)    NULL,
  [Continent]     nvarchar(100)    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  [EndDateTime]              datetime2(2) NOT NULL,
  [RecordHash] VARBINARY(16) NOT NULL, 
  CONSTRAINT [PK_BusinessVault_HS_Country] PRIMARY KEY NONCLUSTERED ([H_Country_SQN], [LoadDateTime]),
  CONSTRAINT [FK_BusinessVault_HS_Country_BusinessVault_H_Country] FOREIGN KEY ([H_Country_SQN]) REFERENCES [BusinessVault].[H_Country] ([H_Country_SQN])
);
GO

BusinessVault.HS_Customer.sql

CREATE TABLE [BusinessVault].[HS_Customer] (
  [H_Customer_SQN]        BIGINT NOT NULL,
  [FirstName]     varchar(50)    NULL,
  [LastName]     varchar(100)    NULL,
  [City]     varchar(50)    NULL,
  [Country_Code]     varchar(3)    NULL,
  [Country_Description]     nvarchar(100)    NULL,
  [Country_Continent]     nvarchar(100)    NULL,
  [Phone]     varchar(20)    NULL,
  [FullName]     varchar(151)    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  [EndDateTime]              datetime2(2) NOT NULL,
  [RecordHash] VARBINARY(16) NOT NULL, 
  CONSTRAINT [PK_BusinessVault_HS_Customer] PRIMARY KEY NONCLUSTERED ([H_Customer_SQN], [LoadDateTime]),
  CONSTRAINT [FK_BusinessVault_HS_Customer_BusinessVault_H_Customer] FOREIGN KEY ([H_Customer_SQN]) REFERENCES [BusinessVault].[H_Customer] ([H_Customer_SQN])
);
GO

BusinessVault.HS_Order.sql

CREATE TABLE [BusinessVault].[HS_Order] (
  [H_Order_SQN]        BIGINT NOT NULL,
  [OrderDate]     datetime    NULL,
  [OrderNumber]     varchar(50)    NULL,
  [CustomerId]     int    NULL,
  [TotalAmount]     decimal(12,2)    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  [EndDateTime]              datetime2(2) NOT NULL,
  [RecordHash] VARBINARY(16) NOT NULL, 
  CONSTRAINT [PK_BusinessVault_HS_Order] PRIMARY KEY NONCLUSTERED ([H_Order_SQN], [LoadDateTime]),
  CONSTRAINT [FK_BusinessVault_HS_Order_BusinessVault_H_Order] FOREIGN KEY ([H_Order_SQN]) REFERENCES [BusinessVault].[H_Order] ([H_Order_SQN])
);
GO

Link tables

Generated using the Link template and Link/Link-Sat config.

BusinessVault.L_Order_Customer.sql

CREATE TABLE [BusinessVault].[L_Order_Customer] (
  [L_Order_Customer_SQN]      BIGINT NOT NULL IDENTITY(1,1),
  [H_Customer_SQN] BIGINT    NULL,
  [H_Order_SQN] BIGINT    NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  CONSTRAINT [PK_BusinessVault_H_Order_Customer] PRIMARY KEY NONCLUSTERED ([L_Order_Customer_SQN]),
  CONSTRAINT [UK_BusinessVault_H_Order_Customer] UNIQUE(
    [H_Customer_SQN],
    [H_Order_SQN]
  )
);
GO

Link-Sat tables

Generated using the Link-Sat template and the Link/Link-Sat config.

BusinessVault.LS_Order_Customer.sql

CREATE TABLE [BusinessVault].[LS_Order_Customer] (
  [L_Order_Customer_SQN]      BIGINT NOT NULL,
  [LoadDateTime]             datetime2(2) NOT NULL,
  [EndDateTime]              datetime2(2) NOT NULL,
  [RecordHash] VARBINARY(16) NOT NULL, 
  CONSTRAINT [PK_BusinessVault_LS_Order_Customer] PRIMARY KEY NONCLUSTERED ([L_Order_Customer_SQN], [LoadDateTime]),
  CONSTRAINT [FK_BusinessVault_LS_Order_Customer_BusinessVault_L_Order_Customer] FOREIGN KEY ([L_Order_Customer_SQN]) REFERENCES [BusinessVault].[L_Order_Customer] ([L_Order_Customer_SQN])
);
GO

Attachments

The entire DataVault example, including SQL and SSIS templates, can be found in the SSIS DataVault example.