---
title: Microsoft SQL Server - Dimensional and Mart
description: Microsoft SQL Server - Dimensional and Mart generates a Stage, a Core and a Mart for SQL Server as T-SQL scripts with stored procedure loaders.
---

[Skip to content](https://knowledge.bigenius-x.com/microsoft-sql-server-dimensional-and-mart#main-content)

[Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en)

[![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/)

Open main navigation

Close main navigation

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new)
- [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal)

 How can we help you?

- There are no suggestions because the search field is empty.

1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en)
2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en)
3. [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server)

# Microsoft SQL Server - Dimensional and Mart

**Applies to**  
Platform: Microsoft SQL Server  
Architecture: Dimensional · Data Mart

Microsoft SQL Server - Dimensional and Mart generates a Stage, a Core and a Mart for SQL Server as T-SQL scripts with stored procedure loaders. Select the Generator Configuration `Microsoft SQL Server Dimensional & Mart` when you add a project whose dimensional model and data marts run on SQL Server. This article lists what the configuration supports.

### Scope and output

- Platform: Microsoft SQL Server.
- Dataflow Modeling columns: Source; Stage in the Bronze column; Entity, Fact and Core Composite in the Silver column Dimensional; the Mart model objects in the Datamart column, which is Gold. See [Understand the Layering concept](https://knowledge.bigenius-x.com/understand-the-layering-concept?hsLang=en).
- Deployment layers: Stage, Cleanse, Core and Mart. Cleanse holds the source, deduplication and data quality objects of Entity, Fact and Core Composite; Core holds their tables, loaders and result views.
- 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](https://knowledge.bigenius-x.com/microsoft-sql-server-artifacts?hsLang=en).

### 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](https://knowledge.bigenius-x.com/microsoft-sql-server-target-environment?hsLang=en). The placeholders for the server, database and schema names are listed in [Microsoft SQL Server - Placeholder values](https://knowledge.bigenius-x.com/microsoft-sql-server-placeholder-values?hsLang=en).

### 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](https://knowledge.bigenius-x.com/source-systems-per-generator-configurations?hsLang=en), and [Discover a Microsoft SQL Server database](https://knowledge.bigenius-x.com/discover-a-microsoft-sql-server-database?hsLang=en) creates source model objects from a SQL Server database.

You can [import a linked project](https://knowledge.bigenius-x.com/import-a-project-to-be-used-as-a-linked-project?hsLang=en) as a source and [export this project as a linked project](https://knowledge.bigenius-x.com/export-a-project-to-be-used-as-linked-project?hsLang=en).

### Supported model objects

#### Source and Stage

- [Source](https://knowledge.bigenius-x.com/create-a-source?hsLang=en)
- [Stage](https://knowledge.bigenius-x.com/create-a-stage?hsLang=en)

#### Core

- [Entity](https://knowledge.bigenius-x.com/create-an-entity?hsLang=en)
- [Fact](https://knowledge.bigenius-x.com/create-a-fact?hsLang=en)
- [Core Composite](https://knowledge.bigenius-x.com/create-a-core-composite?hsLang=en)

#### Mart

- [Mart Dimension](https://knowledge.bigenius-x.com/create-a-mart-dimension?hsLang=en)
- [Mart Fact](https://knowledge.bigenius-x.com/create-a-mart-fact?hsLang=en)
- [Mart Composite](https://knowledge.bigenius-x.com/create-a-mart-composite?hsLang=en)
- [Mart Hierarchy Flatten](https://knowledge.bigenius-x.com/create-a-mart-hierachy-flatten?hsLang=en)
- [Mart Out](https://knowledge.bigenius-x.com/create-a-mart-out?hsLang=en), since Generator 2.2

### Capabilities and limitations

- Keys: an Entity or Mart Dimension table has the surrogate key `<model object name>_SK INT IDENTITY` as primary key and a unique constraint on its business key terms. A Fact or Mart Fact table 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](https://knowledge.bigenius-x.com/foreign-key-mode-model-object?hsLang=en) 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. Entity and Fact in the Core layer use `COR_HeaderAction` and `COR_FooterAction` the same way.
- Indexes: the deployment creates a nonclustered index `IX_<table name>_<related object>` on each surrogate key column of a relationship. [Index management](https://knowledge.bigenius-x.com/add-an-index?hsLang=en) 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 Entity and Mart Dimension table. [Custom Singleton Values](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en) sets their values.
- Data quality: Entity and Fact support [data quality rules](https://knowledge.bigenius-x.com/understand-data-quality-rules-overview?hsLang=en).
- Change detection: [Delta Detection](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en) on Entity and 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](https://knowledge.bigenius-x.com/how-bigenius-x-builds-hash-values?hsLang=en).
- 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.
- Deployment: with the default [Deployment Options](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en), each deployment drops and creates the tables again. See [Microsoft SQL Server - Deployment](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment?hsLang=en).
- Loading: [LoadControl Options](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en) offers `Native Load Control` and `Airflow`.

### Project properties

The configuration offers these project properties, with their defaults:

- [Hash Algorithm](https://knowledge.bigenius-x.com/hash-algorithm-project?hsLang=en): `SHA1`; also `SHA2_256` and `MD5`
- [Hash Casing](https://knowledge.bigenius-x.com/hash-casing-project?hsLang=en): `None`; also `Upper` and `Lower`
- [Hash Concatenation Separator](https://knowledge.bigenius-x.com/hash-concatenation-separator-project?hsLang=en): `_`
- [Hash Null Placeholder](https://knowledge.bigenius-x.com/hash-null-placeholder-project?hsLang=en): `_NULL_`
- [Hash Trim](https://knowledge.bigenius-x.com/hash-trim-project?hsLang=en): off
- [Begin of Universe](https://knowledge.bigenius-x.com/begin-of-universe-project?hsLang=en): `19000101`
- [End of Universe](https://knowledge.bigenius-x.com/end-of-universe-project?hsLang=en): `99991231`
- [Persist Validity](https://knowledge.bigenius-x.com/persist-validity-project?hsLang=en): off
- [Create Result Views](https://knowledge.bigenius-x.com/create-result-views-project?hsLang=en): on
- [Target Object Comment Type](https://knowledge.bigenius-x.com/target-object-comment-type-project?hsLang=en): `None`; also `Tables` and `Tables and Views`
- [Artifact Output Mode](https://knowledge.bigenius-x.com/artifact-output-mode-project?hsLang=en): `Layer Composites`; also `Deployment Objects`
- [Deployment Options](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en): `Generate Drop Table Statements`
- [Configure LoadControl](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en): on
- [Deploy LoadControl](https://knowledge.bigenius-x.com/deploy-loadcontrol-project?hsLang=en): on
- [LoadControl Options](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en): `Native Load Control`; also `Airflow`

### 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_ErrorDescription` with 4000, `BG_IncrementLoadStatus` with 20
- Numbers: `BG_ID`, `BG_ErrorCount`, `BG_Entity_Identity`, `BG_Entity_ForeignKey_Identity`, `BG_Fact_ForeignKey_Identity`, `BG_MartDimension_Identity`, `BG_MartFact_ForeignKey_Identity` `int`; `BG_DataQuality_ID` `bigint`
- 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:

- [Fabric Warehouse - Dimensional and Mart](https://knowledge.bigenius-x.com/fabric-warehouse-dimensional-and-mart?hsLang=en)

### Example

A Mart Fact `salestransaction_by_item_and_year` has a relationship to the Mart Dimension `item` with Foreign Key Mode `Create Enforced`. It generates this table, foreign key and index. The excerpt shows the example schema `mart` in place of the placeholder, leaves out the existence checks and adds line breaks:

```
CREATE TABLE [mart].[DM_MF_salestransaction_by_item_and_year] (
     [BG_LoadTimestamp] DATETIMEOFFSET NOT NULL
    ,[BG_SourceSystem] NVARCHAR(255) NULL
    ,[SALES_PRICE] DECIMAL(18,2) NOT NULL
    ,[QUANTITY] DECIMAL(18,3) NOT NULL
    ,[SALES_AMOUNT] DECIMAL(18,2) NOT NULL
    ,[item_item_SK] INT NOT NULL
    ,[calendar_by_year_year_SK] INT NOT NULL
)
;
ALTER TABLE [mart].[DM_MF_salestransaction_by_item_and_year]
    ADD CONSTRAINT
    [FK_DM_MF_salestransaction_by_item_and_year_DM_MD_item_item]
    FOREIGN KEY ([item_item_SK])
    REFERENCES [mart].[DM_MD_item] ([item_SK]);
CREATE NONCLUSTERED INDEX
    [IX_DM_MF_salestransaction_by_item_and_year_item]
    ON [mart].[DM_MF_salestransaction_by_item_and_year]
    ([item_item_SK]);
```

The load control runs `DM_HeaderAction` before the Mart loaders. It disables the constraint with `NOCHECK CONSTRAINT`. `DM_FooterAction` runs after them and enables it again with `WITH CHECK CHECK CONSTRAINT`. [Fabric Warehouse - Dimensional and Mart](https://knowledge.bigenius-x.com/fabric-warehouse-dimensional-and-mart?hsLang=en) offers no enforced foreign keys.

### 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 Stage, Fact and Mart Fact.
- Generator 2.3: the model object property [Custom Singleton Values](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en) on Entity and Mart Dimension.
- Generator 1.10: the project property [Delta Detection](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en). Since Generator 2.0, Delta Detection is a property of the Entity and the Mart Dimension.

### Related articles

- Previous: 
    - Microsoft SQL Server: [Target environment](https://knowledge.bigenius-x.com/microsoft-sql-server-target-environment?hsLang=en)
- Next: [Generate artifacts](https://knowledge.bigenius-x.com/generate-artifacts?hsLang=en), [Replace the placeholders in the artifacts](https://knowledge.bigenius-x.com/replace-the-placeholders-in-the-artifacts?hsLang=en) 
    - Microsoft SQL Server: [Artifacts](https://knowledge.bigenius-x.com/microsoft-sql-server-artifacts?hsLang=en), [Deployment](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment?hsLang=en), [Load data - Native Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-native-load-control?hsLang=en), [Load data - Apache Airflow Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-apache-airflow-load-control?hsLang=en)
- Reference: [Foreign Key Mode (Model Object)](https://knowledge.bigenius-x.com/foreign-key-mode-model-object?hsLang=en), [Custom Singleton Values (Model Object)](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en), [Delta Detection (Model Object)](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en), [Hash Algorithm (Project)](https://knowledge.bigenius-x.com/hash-algorithm-project?hsLang=en), [Artifact Output Mode (Project)](https://knowledge.bigenius-x.com/artifact-output-mode-project?hsLang=en), [Deployment Options (Project)](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en), [LoadControl Options (Project)](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en), [BG\_Entity\_Identity](https://knowledge.bigenius-x.com/bg_entity_identity?hsLang=en), [BG\_MartDimension\_Identity](https://knowledge.bigenius-x.com/bg_martdimension_identity?hsLang=en), [BG\_Fact\_ForeignKey\_Identity](https://knowledge.bigenius-x.com/bg_fact_foreignkey_identity?hsLang=en), [BG\_MartFact\_ForeignKey\_Identity](https://knowledge.bigenius-x.com/bg_martfact_foreignkey_identity?hsLang=en) 
    - Microsoft SQL Server: [Placeholder values](https://knowledge.bigenius-x.com/microsoft-sql-server-placeholder-values?hsLang=en)
- See also: [Available Generator Configurations](https://knowledge.bigenius-x.com/available-generator-configurations?hsLang=en), [Understand the Generator Configuration](https://knowledge.bigenius-x.com/understand-the-generator-configuration?hsLang=en), [Source Systems per Generator Configurations](https://knowledge.bigenius-x.com/source-systems-per-generator-configurations?hsLang=en), [Fabric Warehouse - Dimensional and Mart](https://knowledge.bigenius-x.com/fabric-warehouse-dimensional-and-mart?hsLang=en) 
    - Microsoft SQL Server: [Dimensional](https://knowledge.bigenius-x.com/microsoft-sql-server-dimensional?hsLang=en), [Datamart](https://knowledge.bigenius-x.com/microsoft-sql-server-datamart?hsLang=en), [DataVault and Mart](https://knowledge.bigenius-x.com/microsoft-sql-server-datavault-and-mart?hsLang=en)

- [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content)

    - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines)
    - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working)
    - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account)
- [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en)
- [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en)
- [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content)

    - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration)
    - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions)
    - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features)
    - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects)
    - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches)
    - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections)
    - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview)
    - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps)
    - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms)
    - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping)
    - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships)
    - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality)
    - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes)
    - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling)
    - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts)
    - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings)
    - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace)
- [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content)

    - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse)
    - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse)
    - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks)
    - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake)
    - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server)
    - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle)
    - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts)
    - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders)
    - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment)
    - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment)
    - [Deployment with an Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-devops-pipeline)
    - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment)
    - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment)
    - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control)
    - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow)
    - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type)
    - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties)
    - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms)
- [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content)

    - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations)
- [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content)

    - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches)
    - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases)
    - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules)
    - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules)
- [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en)
- [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content)

    - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application)
    - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application)
    - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators)
- [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content)

    - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs)
    - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits)

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en)
- [Customer Portal](https://knowledge.bigenius-x.com/customer-portal)
- [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login)

[![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com)

<https://www.linkedin.com/company/bigenius> <https://www.youtube.com/@biGENIUS_DWA>

All rights reserved © 2025, biGENIUS AG