Configuring a Data Mart
The metadata required to build the Data Mart is similar to any other source to staging project.
For on-premises loads it is possible to use either the ETL load pattern or the ELT load pattern. For cloud-based implementations, such as using Azure Data Factory (ADF) or targeting Azure Synapse or Snowflake, BimlFlex provides an in-database ELT pattern.
Sample metadata for Data Mart load scenarios for the supported project types is included in BimlFlex.
More information: Load Sample Metadata
Project Configuration
The Data Mart load pattern is configured in a separate project.
This project uses 3 main connections:
- Source Connection, normally the Staging layer or Data Vault layer
- Target Stage Connection, normally the Data Mart Layer. A staging layer for the Data Mart where delta loads are prepared before they are merged into the target. Only used for ETL patterns. ELT Stored Procedures uses internal staging.
- Target Connection, The Data Mart connection. The target for the Facts and Dimension loads
Example: On-Premises SQL Server (ETL)
| Connection | Role | Connection Type | System Type | Integration Stage | Connection String |
|---|---|---|---|---|---|
BFX_DV | Source | OLEDB | SQL Server | Data Vault | Data Source=localhost;Initial Catalog=BFX_DV;... |
BFX_DM_STG | Target Stage | OLEDB | SQL Server | Data Mart Staging | Data Source=localhost;Initial Catalog=BFX_DM;... |
BFX_DM | Target | OLEDB | SQL Server | Data Mart | Data Source=localhost;Initial Catalog=BFX_DM;... |
In the ETL pattern, the Target Stage Connection acts as an intermediate area where delta loads are prepared before being merged into the final Data Mart tables. The staging and target connections typically share the same database.
Example: Cloud with ADF + Synapse (ELT)
| Connection | Role | Connection Type | System Type | Integration Stage |
|---|---|---|---|---|
BFX_DV | Source | ADONET | Azure Synapse | Data Vault |
BFX_DM | Target | ADONET | Azure Synapse | Data Mart |
In the ELT pattern, there is no separate Target Stage Connection — BimlFlex uses internal staging within the stored procedures. This simplifies the connection setup for cloud implementations.
Settings That Affect Data Mart Behavior
The following settings control how Data Mart objects are generated:
| Setting | Description | Default |
|---|---|---|
DmInferDim | When enabled, BimlFlex infers dimension members from fact records when the dimension key is not found | N |
DmAppendDim | The string appended to Dimension table names | Dim |
DmAppendFact | The string appended to Fact table names | Fact |
For advanced configuration including Type 1/Type 2 dimension overrides and custom effectivity dates, see Data Mart Overrides.