EU Finance — Solution Architecture

Environments

Four environments — Dev → UAT → Support → Production

Fabric artifacts are promoted using native Fabric Deployment Pipelines. Configuration differences (workspace GUIDs, connection GUIDs, storage endpoints) are handled via post-deployment scripts. See the Connections page for environment-specific GUIDs and endpoints.

Source Systems

D365 Finance & Operations

System-of-record transactional data. Exported via Synapse Link to Azure Data Lake Storage in Delta format. Accessed in the Bronze Lakehouse via One Lake shortcuts — no data duplication.

Manual Mapping Data (Excel)

Reference and mapping data maintained in Excel files on Azure File Storage. Ingested into the Bronze Lakehouse as managed Delta tables by Azure Data Factory, orchestrated via the Azure SQL orchestration database.

IBM PA Data (IBMPAX)

IBM Planning Analytics forecast data in Excel format, stored in Azure Blob Storage. Ingested into the Bronze Lakehouse as managed Delta tables via Fabric Pipelines, authenticated using Workspace Identity.

Workspace Structure

Bronze Workspace — isolated

Fabric Workspaceisolated per env

EU_FO_Bronze_LHFabric Lakehouse

Load Mapping DataDataPipeline

Manage ShortcutsNotebooks

Post DeploymentDataPipeline

Silver / Gold Workspace — shared

EU_FO_Silver_LHFabric Lakehouse

EU_FO_Gold_WHFabric Warehouse

EU_FO_Gold_WH.SemanticModelPower BI

EU_FO_Silver_LH.SemanticModelPower BI

Orchestration pipelines & notebooksSilverGold/

ℹ Silver and Gold share a workspace so Gold warehouse stored procedures can reference Silver Lakehouse tables directly using three-part naming — no data duplication between layers.

Silver Layer — EU_FO_Silver_LH

Silver Lakehouse Schemas

SQL Endpoint views are deployed via CREATE/ALTER scripts in SQL Notebooks (not native deployment pipelines — platform limitation).

Schema	Object Type	Purpose
dbo	Shortcut & persisted table	One Lake shortcuts to Bronze Lakehouse. Persisted tables: `lkp_gl_transactions`, `lkp_subledgervouchergeneraljournalentrylandingSQL`, age, dates.
landing	SQL Endpoint View	Views over dbo tables — columns cast to Synapse Link-defined types. Applies `recid IS NOT NULL` filter. Only includes columns required downstream.
staging	SQL Endpoint View	Views over landing tables that reshape and apply business logic. Used to populate persisted DIM and FACT tables in the Gold Warehouse.
stagingnonpersisted	SQL Endpoint View	Views over landing tables for non-persisted DIM and FACT views in the Gold Warehouse.

Silver Notebooks

usp_dbo_age

Builds source data for DimAge

usp_dbo_dates

Builds source data for DimDate

usp_dbo_lkp_gl_transactions

Persists GL transaction data for query performance

usp_dbo_subledgervoucher...

Persists sub-ledger voucher data for query performance

Gold Layer — EU_FO_Gold_WH

Gold Warehouse Schemas

Schema	Contains	Notes
DIM	Persisted Dimension tables	37 dimension tables. Loaded by LOAD schema stored procedures referencing Silver staging views.
FACT	Persisted Fact tables	27 fact tables. Loaded by LOAD schema stored procedures.
DIMNONPERSISTED	Non-persisted Dimension views	Views directly over Silver Lakehouse — no physical storage in the warehouse.
FACTNONPERSISTED	Non-persisted Fact views	Views directly over Silver Lakehouse — used where persistence is not justified.
REPORTINGNONPERSISTED	Non-persisted reporting views	Pre-shaped datasets for specific reporting use cases — not part of the DIM/FACT model.
STAGINGNONPERSISTED	Non-persisted staging views	Exposed externally (ADF, orchestration). Not consumed by the semantic model.

Gold Orchestration Pipeline Sequence

Master pipeline — executed in strict dependency order

1

Load Staging

Lands raw D365 data. Runs Silver notebooks (usp_dbo_age, usp_dbo_dates, lkp persisted tables). Prerequisite for all downstream steps.
2

Load Dimensions 1

Loads core dimension tables with no inter-dimension dependencies — Company, Date, Department, Office, Division, ServiceLine, PostingLayer, and similar.
3

Load Dimensions 2

Loads dimension tables that depend on dimensions created in step 2 — e.g. ChartofAccounts (depends on Company), Project (depends on Company, Office, Department, Division), Worker (depends on Office, Department, Division, Company).
4

Load Facts 1

Loads fact tables with no dependencies on other fact tables — GeneralLedgerTransaction, Budget, Forecast, InvoiceLines, PayAways, PurchaseOrderHeader/Line, ReceivablesSnapshot, PayablesSnapshot, and others.
5

Load Facts 2

Loads fact tables that depend on facts from step 4 — NetRevenue, ForecastNetRevenue, and other derived tables that reference previously loaded facts.
6

Process Data Models

Processes semantic models (Power BI dataset refreshes) that use this workspace as their data source. Runs after all warehouse tables are fully loaded.
7

Create SQL Endpoint Objects

Deploys SQL Endpoint views (non-persisted schemas) via SQL notebooks. Runs post-deployment — views are not natively supported by Fabric deployment pipelines.

Deployment & Promotion

Hybrid deployment strategy

Artifact Type	Deployment Method	Notes
Fabric Pipelines	Native Fabric Deployment Pipelines	Promoted Dev → UAT → SUP → Prod
Fabric Notebooks	Native Fabric Deployment Pipelines	Promoted Dev → UAT → SUP → Prod
Fabric Warehouse (tables, procs)	Native Fabric Deployment Pipelines	Silver dependencies must exist first or deployment fails
Lakehouse Shortcuts	Post-Deployment Script pipeline	Connection GUIDs differ per environment — cannot use native promotion. Script resolves correct GUID per env.
SQL Endpoint Views	CREATE/ALTER scripts in SQL Notebooks	Not supported by native deployment. Manual execution of post-deployment pipeline required.

⚠ All Silver layer objects (Lakehouse, shortcuts, SQL endpoint views) must be deployed and verified before deploying the Gold Warehouse. Missing Silver dependencies will cause the Gold deployment to fail.