One source of truth for SAP data
An enterprise data design that carries SAP data through Bronze, Silver and Gold layers to executive dashboards.
- 01SAPSource
- 02Data FactoryIngest
- 03ADLS Gen2Bronze / raw
- 04DatabricksSilver / clean
- 05SynapseGold / model
- 06Power BIDecisions
Governance / Purview / Entra ID / Key Vault / Azure Monitor
The chart
01 / Symptom
A manufacturer's SAP systems ran operations well, but leadership couldn't get consistent answers to questions that cross functions.
02 / Tests
Mapped how SAP data spreads across modules and how each new report created another extract, then compared Azure services for each job.
03 / Diagnosis
Reports were built on separate, inconsistent extracts with no shared, governed layer underneath.
04 / Prescription
A medallion design on Azure: raw data kept in Bronze, cleaned in Silver with Databricks, modeled in Gold for Power BI, with governance built in.
05 / Outcome
A design paper and presentation. It wasn't built or costed, so no performance results are claimed.
How the tests ran
- Bronze: scheduled Azure Data Factory pipelines land an unmodified copy of SAP data in ADLS Gen2, so any later step can be audited or re-run.
- Silver: Azure Databricks and Delta Lake cleanse, validate, deduplicate and join tables, and apply business rules.
- Gold: Azure Synapse Analytics models the data as a star schema, ready for Power BI dashboards.
- Governance is part of the design from the start: Microsoft Purview for lineage, Entra ID for access, Key Vault for secrets and Azure Monitor for observability.
Tools
- SAP
- Azure Data Factory
- ADLS Gen2
- Azure Databricks
- Azure Synapse Analytics
- Power BI
Findings
3
layers, each with one job
Ingestion, transformation and reporting can each change without breaking the others.
Limits
- This is a design. It wasn't built, load-tested or costed against real data volumes.
- The company and its data are hypothetical, so service choices are reasoned from documentation rather than measured.