A data warehouse is a centralised repository that integrates structured data from multiple source systems — ERP, CRM, HRMS, treasury platforms — into a single, consistently modelled analytical environment optimised for query and reporting rather than transaction processing. Unlike a database designed for transactional write operations (insert, update, delete), a data warehouse is designed for analytical read operations: querying large volumes of historical data across multiple dimensions simultaneously, with consistent definitions and governed transformation logic applied at the point of loading. In a finance technology context, the data warehouse is the layer that sits between the ERP’s operational data and the BI platform’s reporting layer — it provides the consistent, reconciled financial data that EPM actuals, BI dashboards, and management reports all draw from.
Core Architectural Patterns
Two schema patterns dominate finance data warehouse design. The star schema organises data into a central fact table (containing measurable values — revenue amount, cost amount, headcount) surrounded by dimension tables (containing descriptive attributes — entity, account, period, geography). The snowflake schema normalises dimension tables further — splitting a Geography dimension into Country, Region, and City tables — reducing storage redundancy but increasing query complexity. For finance reporting, the star schema is almost always the correct choice: it produces simpler queries, better BI tool performance, and more intuitive dimensional navigation for finance users who think in terms of “revenue by entity by period” rather than in relational join paths.
| Component | Purpose | Finance Example |
|---|---|---|
| Staging layer | Raw source data, unmodified | GL trial balance extract from Oracle EBS |
| Integration layer | Cleansed, mapped, conformed | Account codes mapped to group COA; entities standardised |
| Presentation layer | Star schema — fact + dimension tables | FactGL with dimensions: DimAccount, DimEntity, DimPeriod |
| Semantic layer | Business-friendly metric definitions | EBITDA calculation; constant-currency revenue measure |
GCC-Specific Architecture Considerations
Multi-currency architecture is a non-negotiable requirement in any GCC finance data warehouse. Data must be stored in both the functional currency (SAR for Saudi entities, AED for UAE, EGP for Egyptian) and the group reporting currency — and the exchange rates used for translation must be traceable to a governed rate table, not hard-coded in the warehouse transformation. For Saudi entities reporting under ZATCA Phase 2, the warehouse must maintain invoice-level traceability from the GL summary through to the source e-invoice reference — a requirement that the standard star schema’s aggregation logic must not destroy. Arabic-language dimension member descriptions must be stored in a parallel alias table, not as the primary dimension key, to preserve join performance while supporting bilingual reporting.
What Goes Wrong in Practice
The most damaging data warehouse design error in finance environments is the absence of a conformed dimension — a single, authoritative definition of a dimension (Account, Entity, Period) that is shared across all fact tables. When the revenue fact table uses an Account dimension that defines “Revenue” differently from the Account dimension used by the cost fact table, revenue-less-cost calculations produce results that do not match the ERP’s trial balance. Conformed dimensions are an architectural discipline, not a configuration preference — they must be enforced by governance, not assumed to emerge from individual model developers working independently.
How Loop Wise Solutions Approaches This
In BI and data architecture engagements, we design the finance data warehouse from the reporting requirements — working backward from the management pack, the consolidation output, and the KPI framework to the dimensional model that must exist to produce them consistently. Warehouse design that starts from the source systems and works forward to reporting almost always produces a model that serves the data’s structure rather than the business’s analytical needs.
