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

Microsoft SQL Server Data Mart

Applies to
Platform: Microsoft SQL Server
Architecture: Data Mart

Microsoft SQL Server Data Mart generates a Mart layer for SQL Server from the objects of linked projects, as T-SQL scripts with stored procedure loaders. Select the Generator Configuration Microsoft SQL Server DataMart when you add a project whose data marts run on SQL Server. This article lists what the configuration supports.

Scope and output

  • Platform: Microsoft SQL Server.
  • Layers: Mart, and the layers of the imported linked projects. Dataflow Modeling shows the imported objects in the Linked column in Silver and the Mart model objects in the Datamart column, which is Gold. See Understand the Layering concept.
  • Output: the layer script SQL\L010_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

The configuration reads only model objects of linked projects, for example of a Data Vault or Data Store project. It has no discovery of its own. Import a linked project before you model the Mart layer. You can also export this project as a linked project.

Mart source views read the layers of the linked project as source systems, by four-part name with their own server, database and schema placeholders.

Supported model objects

Capabilities and limitations

  • Keys: a Mart Dimension has the surrogate key <model object name>_SK INT IDENTITY as primary key and a unique constraint on its 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 each surrogate key column of a Mart Fact. Index management offers clustered and nonclustered rowstore and columnstore indexes, unique indexes and Included Columns.
  • Unknown member: the deployment adds a row with the surrogate key -1 to each Mart Dimension. Custom Singleton Values sets its values.
  • Change detection: Delta Detection on the Mart Dimension sets how rows are compared; the default is Hash Value Comparison.
  • Hashing: 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.
  • Data quality: data quality rules are not 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:

Data types

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

  • Timestamps, datetimeoffset: BG_LoadTimestamp, BG_UpdateTimestamp, BG_ValidFromTimestamp, BG_ValidToTimestamp, BG_EffectiveTimestamp
  • Row hashes, binary(20): BG_RowHash, BG_RowHashScd1, BG_RowHashScd2
  • Text, nvarchar: BG_SourceSystem, BG_DataflowName and BG_DataflowSetName with length 255, BG_IncrementLoadStatus with 20
  • Numbers: BG_ID, BG_MartDimension_Identity, BG_MartFact_ForeignKey_Identity int
  • Flags, bit: BG_IsDeleted
  • 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 common_pos with the business key term POS_ID generates this table. Its surrogate key common_pos_SK is an identity column. The excerpt shows the example schema mart in place of the placeholder and adds line breaks:

CREATE TABLE [mart].[DM_MD_common_pos] (
     [BG_SourceSystem] NVARCHAR(255) NULL
    ,[BG_LoadTimestamp] DATETIMEOFFSET NOT NULL
    ,[common_pos_SK] INT IDENTITY NOT NULL
    ,[POS_ID] INT NOT NULL
    ,[CASHBOX_NO] INT NOT NULL
    ,[BRANCH_ID] INT NOT NULL
    ,[Hub_HK] BINARY(20) NOT NULL
    ,[BG_RowHashScd1] BINARY(20) NOT NULL
    ,CONSTRAINT [PK_DM_MD_common_pos]
        PRIMARY KEY CLUSTERED ([common_pos_SK])
    ,CONSTRAINT [UC_DM_MD_common_pos]
        UNIQUE NONCLUSTERED ([POS_ID])
)
;

Hub_HK is a term from the linked Data Vault project. The deployment then inserts the unknown-member row with common_pos_SK -1, using SET IDENTITY_INSERT.

Release differences

Project Settings shows the Generator Version of your project.

  • Generator 2.2: 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 on Mart Fact.
  • Generator 2.3: the model object property Custom Singleton Values on Mart Dimension.
  • Generator 1.10: the project property Delta Detection. Since Generator 2.0, Delta Detection is a property of the Mart Dimension.

Related articles