Enterprise BI Platform — Documentation

A multi-source analytics platform consolidated in Snowflake and served in Power BI. This page documents how data moves from operational systems to a single governed reporting model.

Purpose

Give an organisation one trusted version of its numbers. Instead of each team reconciling exports by hand, every source is ingested, consolidated, modelled, and exposed through a single semantic model so all dashboards agree and every figure is traceable.

Data sources

The reference build ingests five representative source types:

Dynamics 365 Business Central  → ERP / finance (GL, AR/AP, sales orders)
CRM (Dynamics / Salesforce)    → accounts, opportunities, pipeline
On-prem SQL Server             → operational / legacy systems
Excel / CSV field files        → country teams, manual trackers
REST APIs                      → FX rates, web analytics, external refs

Ingestion (Azure Data Factory)

ADF orchestrates extraction. Database sources use change-tracking or modified-date watermarks for incremental loads; the ERP and CRM use their connectors with delta queries; files are picked up by event/schedule triggers; APIs are paged and rate-limited. Pipelines are parameterised and reusable, with retry policies, failure alerts, and per-run logging so any single source can fail and be re-run in isolation.

Snowflake medallion

Snowflake holds three layers, each in its own schema, with compute (warehouses) sized per workload so transforms never compete with reporting:

BRONZE  raw, immutable landing zone + load metadata (source, batch, ts)
SILVER  cleaned, typed, deduplicated, conformed entities (dbt)
GOLD    business marts — star schema fact + dimensions (dbt)

Keeping Bronze immutable means transforms can be replayed and audited without re-pulling from source systems.

Data model

Gold is a classic star schema. A central fact table at order-line grain joins to conformed dimensions on surrogate keys:

FACT_SALES (grain: one row per order line)
  ├─ date_key      → DIM_DATE
  ├─ product_key   → DIM_PRODUCT
  ├─ customer_key  → DIM_CUSTOMER
  ├─ region_key    → DIM_REGION
  └─ channel_key   → DIM_CHANNEL
  measures: quantity, gross_amount, cost_amount, discount_amount

The sandbox renders this model visually so you can see exactly how the datasets are linked.

Power BI semantic model

A single Power BI semantic model connects to the Gold marts (Import or Direct Lake/Query depending on volume), defines relationships once, and centralises DAX measures so a metric is defined in exactly one place:

Revenue        = SUM(FACT_SALES[gross_amount])
Cost           = SUM(FACT_SALES[cost_amount])
Gross Margin % = DIVIDE([Revenue] - [Cost], [Revenue])
Revenue YoY %  = DIVIDE([Revenue] - [Revenue PY], [Revenue PY])

Row-level security filters by region/role, and the model is certified so report authors build on trusted definitions rather than re-implementing logic per report.

Tech stack

Azure Data Factory · Snowflake · dbt · Power BI (semantic model, DAX, RLS) · SQL. Optional: Microsoft Fabric (Dataflows Gen2, Direct Lake), Airflow for orchestration, and Git-based CI for dbt and pipeline definitions.