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

Fabric Warehouse - Dimensional

Applies to
Platform: Fabric Warehouse
Architecture: Dimensional

Fabric Warehouse - Dimensional generates a Stage and a dimensional Core for Fabric Warehouse as T-SQL notebooks with a notebook load control. Select the Generator Configuration Fabric Warehouse Dimensional when you add a project whose Entities and Facts run in a Fabric Warehouse. This article lists what the configuration supports and where it differs from Microsoft SQL Server - Dimensional.

Scope and output

  • Platform: Microsoft Fabric Warehouse. The configuration is available since Generator 2.2.
  • Dataflow Modeling columns: Source; Stage in the Bronze column; Entity, Fact and Core Composite in the Silver column Dimensional. See Understand the Layering concept.
  • Deployment layers: Stage, Cleanse and Core. Cleanse holds the source, deduplication and data quality objects; Core holds the tables, loaders and result views.
  • Mart model objects: not available; use Fabric Warehouse - Dimensional and Mart.
  • Output: T-SQL deployment notebooks, a deployment pipeline definition and Python load control notebooks, described in Fabric Warehouse - Artifacts.

Prerequisites

  • A Fabric workspace with a warehouse for the target layers. The deployment scripts switch to the existing database with USE and create missing schemas; they create no database.
  • A lakehouse for the load control execution log. The load control deployment notebook creates the log as a Delta table there.
  • An Azure Key Vault with the tenant ID, client ID and client secret of a service principal. The load control notebook uses them to connect to the warehouse.

A setup example is in Fabric Warehouse - Target environment. Each layer has its own placeholders <project name>#<layer>#database_name and <project name>#<layer>#schema_name, where <layer> is stage, cleanse or core. The generated replacement_config.json presets the database names to warehouse. The load control placeholders are listed in Fabric Warehouse - Placeholder values.

Sources and linked projects

Stage source views read each source table by its three-part name [<database>].[<schema>].[<table>]. The source system has the type Microsoft Fabric Warehouse, and its database and schema names are placeholders. Discovery types are listed in Source Systems per Generator Configurations.

You can import a linked project as a source and export this project as a linked project. Entity, Fact and Core Composite read from a Stage, a Core Composite or a Store Entity of a linked Data Store project. Relationships of an Entity or a Fact point to an Entity.

Supported model objects

Implementation Type offers Permanent for Entity and FactLoadStrategy for Fact. Core Composite offers Virtual (default) and Cached, and Stage offers Cached (default) and Virtual.

Capabilities and limitations

  • Keys: an Entity table has a primary key on its surrogate key and a unique constraint on its business key terms. A Fact table has no primary key. All constraints are created as NONCLUSTERED and NOT ENFORCED.
  • Foreign keys: Foreign Key Mode on Entity and Fact offers None and Create Disabled, and the default is Create Disabled. Create Disabled creates the foreign key as NOT ENFORCED; None creates no constraint. Create Enforced is not available.
  • Indexes: index management is not available, and the generated tables have no indexes.
  • Identity values: the tables have no IDENTITY columns. The Entity loader computes the surrogate key <model object name>_SK (BG_Entity_Identity) as the current maximum in the target plus ROW_NUMBER(), inside its transaction. BG_ID and BG_DataQuality_ID get their values the same way.
  • Unknown member: the deployment adds a row with the surrogate key -1 to each Entity table when the row is missing. Custom Singleton Values sets its values.
  • Data quality: Entity and Fact support data quality rules.
  • Deployment: a deployment notebook holds at most the number of objects set in Notebook Deployment Objects Limit; further objects go to the next notebook. The pipeline definition runs the load control deployment first, then the deployment notebooks in order. See Fabric Warehouse - Deployment.
  • Loading: LoadControl Options offers only Multi-Threading Notebook, a Python notebook that runs the loaders in dependency order. See Fabric Warehouse - Load data - Native Load Control.
  • Data types: validation reports warning ID-35000 when the length, precision or scale of a term does not fit its Fabric Warehouse data type. Warning ID-35001 flags a business key term of type varchar(max) or varbinary(max), because constraints on it could block the deployment.

Project properties

Project Settings shows these properties of the configuration, with their defaults:

Platform differences

The table compares the configuration with Microsoft SQL Server - Dimensional of the same release:

ItemFabric WarehouseMicrosoft SQL Server
Timestamp Default Termsdatetime2(6)datetimeoffset
Hash Default Termsvarbinary(20)binary(20)
Text Default Termsvarcharnvarchar
Surrogate key columnINT, set by the loaderINT IDENTITY
Primary keynonclustered, not enforcedclustered, enforced
Persist Validity defaultonoff
Enforced foreign keysnot availableavailable
Index managementnot availableavailable
Artifact Output Modenot availableavailable
Target Object Comment Typenot availableavailable

LoadControl Options offers Multi-Threading Notebook on Fabric Warehouse and Native Load Control or Airflow on Microsoft SQL Server. Notebook Deployment Objects Limit exists only on Fabric Warehouse.

These Default Terms belong to each group:

  • Timestamps: BG_LoadTimestamp, BG_UpdateTimestamp, BG_ValidFromTimestamp, BG_ValidToTimestamp, BG_EffectiveTimestamp
  • Row hashes: BG_RowHash, BG_RowHashScd1, BG_RowHashScd2
  • Text, same lengths on both platforms: BG_SourceSystem, BG_DataflowName, BG_DataflowSetName, BG_ErrorDescription, BG_IncrementLoadStatus

Example

An Entity common_customer_scd1 with the business key term CUSTOMER_ID generates this table in the Core layer. The excerpt shows the example schema core in place of the placeholder, leaves out six address columns and adds line breaks:

CREATE TABLE [core].[COR_EN_common_customer_scd1] (
     [BG_SourceSystem] VARCHAR(255) NULL
    ,[BG_LoadTimestamp] DATETIME2(6) NOT NULL
    ,[BG_UpdateTimestamp] DATETIME2(6) NOT NULL
    ,[common_customer_scd1_SK] INT NOT NULL
    ,[CUSTOMER_ID] INT NOT NULL
    ,[CUSTOMER] VARCHAR(100) NOT NULL
    ,[CUSTOMER_TYPE] VARCHAR(100) NOT NULL
    ,[CUSTOMER_SINCE] DATE NULL
    ,[BG_RowHashScd1] VARBINARY(20) NOT NULL
)
;
ALTER TABLE [core].[COR_EN_common_customer_scd1]
    ADD CONSTRAINT [PK_COR_EN_common_customer_scd1]
    PRIMARY KEY NONCLUSTERED ([common_customer_scd1_SK]) NOT ENFORCED;
ALTER TABLE [core].[COR_EN_common_customer_scd1]
    ADD CONSTRAINT [UC_COR_EN_common_customer_scd1]
    UNIQUE NONCLUSTERED ([CUSTOMER_ID]) NOT ENFORCED;

The loader COR_EN_common_customer_scd1_Loader reads the current maximum, with the example database warehouse:

DECLARE @MaxIdentityValue INT = (
    SELECT ISNULL(MAX([common_customer_scd1_SK]), 0)
    FROM [warehouse].[core].[COR_EN_common_customer_scd1]);

Its insert statement then numbers the new rows from there:

@MaxIdentityValue + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    AS [common_customer_scd1_SK]

With Microsoft SQL Server - Dimensional, the key column is INT IDENTITY and the primary key is clustered.

Release differences

Project Settings shows the Generator Version of your project.

  • Generator 2.2: first release of the configuration.
  • Generator 2.3: the model object property Custom Singleton Values on Entity.

Related articles