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

Microsoft SQL Server - DataVault and Mart

Applies to
Platform: Microsoft SQL Server
Architecture: Data Vault · Data Mart

Microsoft SQL Server - DataVault and Mart generates a Stage, Raw Vault, Business Vault and Mart for SQL Server as T-SQL scripts with stored procedure loaders. Select the Generator Configuration Microsoft SQL Server DataVault & Mart when you add a project whose Data Vault and data marts run on SQL Server.

Scope and output

  • Platform: Microsoft SQL Server.
  • Layers: Source, Stage, Raw Vault, Business Vault and Mart. Dataflow Modeling shows Stage in the Bronze column, both vault layers in the Silver column and the Mart model objects in the Datamart column, which is Gold. See Understand the Layering concept.
  • Output: the layer scripts SQL\L010_Stage.sql to SQL\L040_Mart.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 the source model objects.

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

Mart

Capabilities and limitations

  • Keys: the primary key of a Hub or Link is its hash key, and of a Mart Dimension its surrogate key <model object name>_SK INT IDENTITY. Both get a unique constraint on the business key terms; a Mart Fact has no primary key. 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. With Create Enforced on a Mart Fact, the procedure DM_HeaderAction disables the constraints before the loaders of the layer run, and DM_FooterAction checks them again afterwards.
  • Indexes: the deployment creates a nonclustered index IX_<table name>_<related object> on the referencing column of each relationship. Index management offers clustered and nonclustered rowstore and columnstore indexes, unique indexes and Included Columns.
  • Unknown member: Hubs, Links, Satellites, Link Satellites and Multi-Active Satellites get a row with the hash key 0x00, and each Mart Dimension gets a row with the surrogate key -1. Custom Singleton Values sets their 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, except on Mart model objects.
  • Deployment: with the default Deployment Options, each deployment drops and recreates the tables. 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, BG_MartDimension_Identity, BG_MartFact_ForeignKey_Identity 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 Mart Dimension bk_multipart_not_nullable reads the Business Current View BDV_Current_BDC_CUSTOMER of the same project. The excerpt uses the example database DWH and the schemas bv and mart for the placeholders and leaves out two name columns:

CREATE VIEW [mart].[DM_MD_bk_multipart_not_nullable_Source]
AS
SELECT
     CAST(NULL AS NVARCHAR(255)) AS [BG_SourceSystem]
    ,[s1].[BDC_SHOP_ID] AS [BDC_SHOP_ID]
    ,[s1].[BDC_CUSTOMER_ID] AS [BDC_CUSTOMER_ID]
    ,[s1].[DATE_OF_BIRTH] AS [DATE_OF_BIRTH]
    ,[s1].[LOCATION] AS [LOCATION]
FROM [DWH].[bv].[BDV_Current_BDC_CUSTOMER] AS [s1]
;

The view DM_MD_bk_multipart_not_nullable_Hashing builds the row hash of the SCD1 terms FIRST_NAME, LAST_NAME, DATE_OF_BIRTH and LOCATION. It joins them with the separator _ and replaces NULL with _NULL_:

CAST(HASHBYTES('SHA1', CAST('' AS VARCHAR(MAX))
    + ISNULL([FIRST_NAME], N'_NULL_') + N'_'
    + ISNULL([LAST_NAME], N'_NULL_') + N'_'
    + ISNULL(CAST([DATE_OF_BIRTH] AS NVARCHAR(4000)), N'_NULL_') + N'_'
    + ISNULL([LOCATION], N'_NULL_'))
    AS VARBINARY(20)) AS [BG_RowHashScd1]

Release differences

Project Settings shows the Generator Version of your project.

  • Generator 2.2: Vault Out and Mart 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.
  • 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. Since Generator 2.0, Delta Detection is a property of the Mart Dimension.

Related articles