--- title: Microsoft SQL Server - Datamart description: Microsoft SQL Server - Datamart generates a Mart layer for SQL Server from the objects of linked projects, as T-SQL scripts with stored procedure loaders. --- [Skip to content](https://knowledge.bigenius-x.com/microsoft-sql-server-datamart#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 - Datamart **Applies to** Platform: Microsoft SQL Server Architecture: Data Mart Microsoft SQL Server - Datamart 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](https://knowledge.bigenius-x.com/understand-the-layering-concept?hsLang=en). - 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](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 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](https://knowledge.bigenius-x.com/import-a-project-to-be-used-as-a-linked-project?hsLang=en) before you model the Mart layer. You can also [export this project as a linked project](https://knowledge.bigenius-x.com/export-a-project-to-be-used-as-linked-project?hsLang=en). 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 - [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: a Mart Dimension has the surrogate key `_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](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. - Indexes: the deployment creates a nonclustered index `IX__` on each surrogate key column of a Mart Fact. [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 Mart Dimension. [Custom Singleton Values](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en) sets its values. - Change detection: [Delta Detection](https://knowledge.bigenius-x.com/delta-detection-model-object?hsLang=en) 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](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. - Data quality: [data quality rules](https://knowledge.bigenius-x.com/understand-data-quality-rules-overview?hsLang=en) are not available. - 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_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: - [Oracle - Datamart](https://knowledge.bigenius-x.com/oracle-datamart?hsLang=en) ### 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](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en) on 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 Mart Dimension. ### Related articles - Previous: [Import a Project to be used as a Linked Project](https://knowledge.bigenius-x.com/import-a-project-to-be-used-as-a-linked-project?hsLang=en) - 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\_MartDimension\_Identity](https://knowledge.bigenius-x.com/bg_martdimension_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), [Use Linked Projects](https://knowledge.bigenius-x.com/use-linked-projects?hsLang=en), [Oracle - Datamart](https://knowledge.bigenius-x.com/oracle-datamart?hsLang=en) - Microsoft SQL Server: [Data Store](https://knowledge.bigenius-x.com/microsoft-sql-server-data-store?hsLang=en), [DataVault](https://knowledge.bigenius-x.com/microsoft-sql-server-datavault?hsLang=en), [DataVault and Mart](https://knowledge.bigenius-x.com/microsoft-sql-server-datavault-and-mart?hsLang=en), [Dimensional and Mart](https://knowledge.bigenius-x.com/microsoft-sql-server-dimensional-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) All rights reserved © 2025, biGENIUS AG