Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Microsoft SQL Server - DataVault

Applies to
Platform: Microsoft SQL Server
Architecture: Data Vault

Microsoft SQL Server - DataVault generates a Stage, Raw Vault and Business Vault for SQL Server as T-SQL scripts with stored procedure loaders. Select the Generator Configuration Microsoft SQL Server DataVault when you add a project whose Data Vault runs on SQL Server. This article lists what the configuration supports.

Scope and output

  • Platform: Microsoft SQL Server.
  • Layers: Source, Stage, Raw Vault and Business Vault. Dataflow Modeling shows Stage in the Bronze column and both vault layers in the Silver column. See Understand the Layering concept.
  • Output: the layer scripts SQL\L010_Stage.sql, SQL\L020_Raw Vault.sql and SQL\L030_Business Vault.sql, the load control scripts or an Airflow DAG, and the PowerShell 7 scripts that deploy and load, described in Microsoft SQL Server - Artifacts.

Prerequisites

  • A SQL Server instance and a login that can create the databases, schemas and objects of the layers and the load control. Each layer script creates its database with the recovery model SIMPLE and its schema when they are missing.
  • PowerShell 7 and sqlcmd on the machine that deploys.

The setup is described in Microsoft SQL Server - Target environment. The placeholders for the server, database and schema names are listed in Microsoft SQL Server - Placeholder values.

Sources and linked projects

Stage source views read each source table by its four-part name [<server>].[<database>].[<schema>].[<table>]. The server name stays empty for a source database on the same instance; otherwise it is the name of a linked server. The discovery types are listed in Source Systems per Generator Configurations, and Discover a Microsoft SQL Server database creates source model objects from a SQL Server database.

You can import a linked project as a source and export this project as a linked project.

Supported model objects

Source and Stage

Raw Vault

Business Vault

Capabilities and limitations

  • Keys: Hubs and Links have their hash key as primary key and a unique constraint UC_<table name> on the business key terms. Primary keys are clustered. When a model object has a user-defined clustered index, its primary key is nonclustered and validation reports a warning.
  • Foreign keys: Foreign Key Mode offers None, Create Disabled (default) and Create Enforced. Create Disabled adds the constraint and disables it with NOCHECK CONSTRAINT.
  • Indexes: the deployment creates a nonclustered index IX_<table name>_<related object> on the hash key of each relationship. Index management offers clustered and nonclustered rowstore and columnstore indexes, unique indexes and Included Columns.
  • Unknown member: the deployment adds a row with the hash key 0x00 and the source system Unknown to Hubs, Links, Satellites, Link Satellites and Multi-Active Satellites. Custom Singleton Values sets its values.
  • Hashing: hash keys and row hashes are HASHBYTES('SHA1', ...) with the default Hash Algorithm, cast to VARBINARY(20) and stored in BINARY(20) columns. See How biGENIUS-X builds hash values.
  • Validity: with Persist Validity off, historized tables have no BG_ValidToTimestamp column. The result view computes it from the BG_ValidFromTimestamp of the next version.
  • Change detection: with Excluded from Delta Detection, changes to the selected terms of a Satellite alone create no new version.
  • Loading order: Use Embryo Loader on a relationship adds a loader that inserts missing keys into the related Hub or Link before the Satellite loader runs.
  • Data quality: data quality rules are available.
  • Deployment: with the default Deployment Options, each deployment drops and creates the tables again. See Microsoft SQL Server - Deployment.
  • Loading: LoadControl Options offers Native Load Control and Airflow.

Project properties

The configuration offers these project properties, with their defaults:

Older projects can still show the legacy property Behaviour for Nulled Business Keys in Links; the data quality rules replace it, see Understand Data Quality rules overview.

Data types

The Default Terms get these SQL Server data types. DATETIMEOFFSET and DATETIME2 are written without precision, so SQL Server uses precision 7:

  • Timestamps, datetimeoffset: BG_LoadTimestamp, BG_UpdateTimestamp, BG_ValidFromTimestamp, BG_ValidToTimestamp, BG_EffectiveTimestamp, DVPIT_ForeignKey_LoadDate
  • Effectivity, datetime2: BG_Effectivity_Begin, BG_Effectivity_End
  • Hash keys and row hashes, binary(20): Hub_HK, Link_HK, DrivingKeyLink_HK, PIT_HK, DVLINK_ForeignKey_Identity, DVPIT_ForeignKey_Identity, BG_RowHash, BG_RowHashScd1, BG_RowHashScd2
  • Text, nvarchar: BG_SourceSystem, BG_DataflowName and BG_DataflowSetName with length 255, BG_ErrorDescription with 4000, BG_IncrementLoadStatus with 20
  • Numbers: BG_ID, BG_ErrorCount, BG_Sequence int; BG_DataQuality_ID bigint
  • Flags, bit: BG_IsDeleted, BG_Appearance, BG_MAS_HubDifferenceFound
  • BG_IncrementalFilter, BG_LowerThreshold and BG_UpperThreshold: the data type of the term that you map to BG_IncrementalFilter

Platform differences

These profiles compare their configuration with this one, including data types and properties:

Example

A Hub common_customer with the business key term CUSTOMER_ID generates this table. The excerpt shows the example schema rdv in place of the placeholder and adds line breaks:

CREATE TABLE [rdv].[RDV_HUB_common_customer] (
     [BG_LoadTimestamp] DATETIMEOFFSET NOT NULL
    ,[Hub_HK] BINARY(20) NOT NULL
    ,[BG_SourceSystem] NVARCHAR(255) NULL
    ,[CUSTOMER_ID] INT NOT NULL
    ,CONSTRAINT [PK_RDV_HUB_common_customer]
        PRIMARY KEY CLUSTERED ([Hub_HK])
    ,CONSTRAINT [UC_RDV_HUB_common_customer]
        UNIQUE NONCLUSTERED ([CUSTOMER_ID])
)
;

The view RDV_HUB_common_customer_Hashing computes the hash key with the default hash properties:

CAST(HASHBYTES('SHA1', CAST('' AS VARCHAR(MAX))
    + ISNULL(CAST([CUSTOMER_ID] AS NVARCHAR(4000)), N'_NULL_'))
    AS VARBINARY(20)) AS [Hub_HK]

The deployment inserts the unknown-member row with Hub_HK 0x00, and the result view RDV_HUB_common_customer_Result filters it out with WHERE [Hub_HK] <> 0x00.

Release differences

Project Settings shows the Generator Version of your project.

  • Generator 2.2: Vault Out; the project properties Persist Validity, Create Result Views, Hash Casing, Hash Concatenation Separator, Hash Null Placeholder and Hash Trim; the model object property Increment Management Type; the relationship property Use Embryo Loader, see Embryo Loader.
  • Generator 2.3: the model object property Custom Singleton Values.
  • Generator 2.4: Non-Historized Satellite and Non-Historized Business Satellite; the model object property Excluded from Delta Detection.
  • Generator 1.10: the project property Delta Detection. Generator 2.0 no longer offers it.

Related articles