When an organization runs several plants, branches or business units, each often has its own systems, codes and habits. An Enterprise Data Warehouse (EDW) exists to give leadership one consistent view while keeping each site's detail available.
Design decisions that matter most
1. Conformed dimensions
Supplier, item, customer, warehouse, cost centre and calendar must mean the same thing everywhere. Build conformed dimensions that map site-specific codes to enterprise keys and keep the original codes for traceability.
2. Subject-area facts
Organize gold models by business process, for example purchasing, stock movement, sales and general ledger, rather than by source system. Dashboards then ask business questions, not technical ones.
3. A site or unit attribute on every fact
A plant or branch attribute on every fact enables consolidated and site-level reporting from the same model. It is also the natural column for row-level security.
4. Clear layer boundaries
A layered approach (raw → cleansed → business-ready) separates concerns: ingestion teams own raw data, modelers own the middle layer, and the business owns definitions in the gold layer.
5. Performance by design
Choose an engine suited to analytical workloads, partition and order data by the most common filters, and pre-aggregate where dashboards repeatedly scan large histories.
Documentation is part of the architecture
Architecture diagrams, a business glossary and source-to-target mappings aimed at both management and technical teams turn a warehouse from a black box into a shared asset. They are also the fastest way to onboard new analysts.
Common pitfalls
- Copying source schemas straight into reporting
- Letting each dashboard define its own KPI logic
- Ignoring deletes and cancellations in transactional systems
- Treating security as a dashboard setting and not as part of the model
Fiaz Anjum
Senior Data Engineering & Business Intelligence Professional | Digital Transformation Specialist
- Data Warehouse
- Architecture
- Data Modeling