Fabric Lakehouse - Dimensional and Mart

Fabric Lakehouse - Dimensional and Mart generates a Stage, a Core and a Mart for Microsoft Fabric, as notebooks on Delta tables. Select the Generator Configuration Fabric Lakehouse Dimensional & Mart when you add a project for a dimensional model with data marts. With the project property Target Platform set to Azure Synapse, it generates for Azure Synapse Analytics instead.

Scope and output

• Platform: with Target Platform Fabric, the default, each layer is a schema of a Microsoft Fabric lakehouse. With Azure Synapse, each layer is a database in Azure Synapse Analytics, which the deployment creates with CREATE DATABASE IF NOT EXISTS ... LOCATION.
• Dataflow Modeling columns: Stage in Bronze, dimensional model objects in Silver, Mart model objects in the Datamart column. See Understand the Layering concept.
• Deployment layers: Stage, Cleanse, Core, Mart.
• Output: notebooks in the folders Jupyter, Helpers and LoadControl, the notebook <project>_deploy and, with Target Platform Fabric, deploy.ps1; described in Fabric Lakehouse - Artifacts.

Prerequisites

• Target Platform Fabric: a Fabric workspace with a lakehouse with schemas. The deployment creates a schema per layer.
• Target Platform Fabric: a service principal as Member of the workspace, and PowerShell 7 for deploy.ps1.
• Target Platform Azure Synapse: an Azure Synapse workspace and a storage location per layer.

The setup is described in Fabric Lakehouse - Target environment and Azure Synapse - Target environment, the placeholders in Fabric Lakehouse - Placeholder values.

Sources and linked projects

The source system has the type Spark. Stage source views read each source table as <workspace>.<lakehouse>.<schema>.<table> with placeholders of the source system. Discover Parquet or Delta files on OneLake creates the source model objects; Source Systems per Generator Configurations lists the discovery types.

You can import a linked project, for example a Fabric Lakehouse - Stage Files or Fabric Lakehouse - Stage JDBC project, and export this project as a linked project.

Supported model objects

Source and Stage

• Source
• Stage

Dimensional

• Entity
• Fact
• Core Composite

Mart

• Mart Dimension
• Mart Fact
• Mart Composite
• Mart Hierarchy Flatten
• Mart Out, since Generator 2.2

Capabilities and limitations

• Loaders: one notebook per loader in the folder Jupyter, for example COR_EN_bk_multipart_not_nullable_Loader, with the load timestamp as the parameter bg_loadtimestamp.
• Tables: Delta tables. File Format offers only delta, and Optimization Method offers Partitioned By and Cluster By.
• Keys: the tables get no primary key, unique or foreign key constraints. Foreign Key Mode offers only None.
• Indexes: index management is not available.
• Surrogate keys: the loader computes <model object name>_sk of a new row as a Long from the current time and monotonically_increasing_id(), without identity columns.
• Unknown member: Entity and Mart Dimension tables get a row with surrogate key -1; Custom Singleton Values sets its values.
• Data quality: Entity and Fact support data quality rules.
• Hashing: row hashes are UNHEX(SHA2(..., 256)) in Binary columns, see How biGENIUS-X builds hash values.
• Validity: with Persist Validity on (default), historized tables store bg_validtotimestamp; off, the result view computes it.
• Deployment: with the default Deployment Options, each deployment replaces the tables and the load control log tables with empty ones, see Redeploy and keep data. With Target Platform Fabric, it also creates a schema per layer.
• Loading: LoadControl Options offers Multi-Threading Notebook and Airflow.

Project properties

The configuration offers these project properties, with their defaults:

• Hash Algorithm: SHA-256, the only value
• Hash Casing: None; also Upper and Lower
• Hash Concatenation Separator: _
• Hash Null Placeholder: _NULL_
• Hash Trim: off
• Begin of Universe: 1900-01-01
• End of Universe: 9999-12-31
• Persist Validity: on
• Create Result Views: on
• Target Object Comment Type: None; also Tables and Tables and Views
• Target Platform: Fabric; also Azure Synapse
• Deployment Options: Generate Drop Table Statements and Generate Create Schema If Not Exists Statements
• Notebook Deployment Objects Limit: 100
• Configure LoadControl: on
• Deploy LoadControl: on
• LoadControl Options: Multi-Threading Notebook; also Airflow
• Logging Method: Delta Table; also Python Logger

Data types

The Default Terms get these data types:

• Timestamps, Timestamp: BG_LoadTimestamp, BG_UpdateTimestamp, BG_ValidFromTimestamp, BG_ValidToTimestamp, BG_EffectiveTimestamp
• Row hashes, Binary: BG_RowHash, BG_RowHashScd1, BG_RowHashScd2
• Text, String: BG_SourceSystem, BG_DataflowName, BG_DataflowSetName, BG_ErrorDescription, BG_IncrementLoadStatus
• Numbers, Long: BG_ID, BG_DataQuality_ID, BG_Entity_Identity, BG_Entity_ForeignKey_Identity, BG_Fact_ForeignKey_Identity, BG_MartDimension_Identity, BG_MartFact_ForeignKey_Identity; Integer: BG_ErrorCount
• Flags, Integer: BG_IsDeleted

Platform differences

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

Both platforms offer the LoadControl Option Airflow. The native load control is Multi-Threading Notebook on Fabric Lakehouse.

Example

The lookup view of a Fact relationship_fk_multipart_not_nullable_fa joins the version of the historized Entity bk_nullable valid at bg_effectivetimestamp. The excerpt uses the example lakehouse my_lakehouse and omits one relationship:

SELECT
     `bg_source`.`bg_sourcesystem` AS `bg_sourcesystem`
    ,`bg_source`.`bg_effectivetimestamp` AS `bg_effectivetimestamp`
    ,`bg_source`.`order_as_free_text` AS `order_as_free_text`
    ,COALESCE(`r2`.`bk_nullable_sk`, -1)
        AS `bk_nullable_bdc_employee_nullablebk_sk`
FROM `my_lakehouse`.`cleanse`.
    `cls_f_relationship_fk_multipart_not_nullable_fa_source`
    AS `bg_source`
LEFT OUTER JOIN `my_lakehouse`.`core`.`cor_en_bk_nullable_result` AS `r2`
   ON (`r2`.`bdc_employee_id` <=> `bg_source`.
       `fk_bk_nullable_bdc_employee_nullablebk_bdc_employee_id`)
  AND (`bg_source`.`bg_effectivetimestamp` >= `r2`.`bg_validfromtimestamp`)
  AND (`bg_source`.`bg_effectivetimestamp` < `r2`.`bg_validtotimestamp`)

Without a match the row gets -1, the unknown member.

Release differences

Project Settings shows the Generator Version of your project.

• Generator 2.0: Target Object Comment Type replaced the value Target Platform with Tables and Tables and Views, default None. Added the model object property Foreign Key Mode on Entity, Fact and Mart Fact, with the only value None.
• Generator 2.2: added Mart Out, the project properties Persist Validity, Create Result Views and the four Hash properties, and the model object property Increment Management Type on Stage, Fact and Mart Fact. Deployment Options added Generate Create Schema If Not Exists Statements, selected by default, and LoadControl Options added Airflow.
• Generator 2.3: added the model object property Custom Singleton Values on Entity and Mart Dimension. The Optimization Method of Entity, Fact and Core Composite applies to the table since Generator 2.3.0; up to Generator 2.2.1 it was ignored.
• 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

• Previous:
  • Fabric Lakehouse: Target environment
  • Azure Synapse: Target environment
• Next: Generate artifacts, Replace the placeholders in the artifacts
  • Fabric Lakehouse: Artifacts, Deployment, Load data - Native Load Control, Load data - Apache Airflow Load Control
  • Azure Synapse: Deployment, Load data - Native Load Control
• Reference: Foreign Key Mode (Model Object), Custom Singleton Values (Model Object), Delta Detection (Model Object), Target Platform (Project), Deployment Options (Project), Notebook Deployment Objects Limit (Project), LoadControl Options (Project), Optimization Method (Model Object), BG_Entity_Identity, BG_MartDimension_Identity, BG_MartFact_ForeignKey_Identity
  • Fabric Lakehouse: Placeholder values
• See also: Available Generator Configurations, Understand the Generator Configuration, Source Systems per Generator Configurations, Microsoft SQL Server - Dimensional and Mart
  • Fabric Lakehouse: Dimensional, Datamart, DataVault and Mart