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

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.
  • 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.

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

Core

Mart

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 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 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 sets their values.
  • Data quality: Entity and Fact support data quality rules.
  • Change detection: Delta Detection 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.
  • 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, 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_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:

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 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 on Entity and Mart Dimension.
  • Generator 1.10: the project property Delta Detection. Since Generator 2.0, Delta Detection is a property of the Entity and the Mart Dimension.

Related articles