Data modelling in the context of BI and analytical environments is the design discipline that defines how data should be structured — in tables, relationships, and hierarchies — to serve the analytical queries that BI consumers need to run efficiently and accurately. It spans three abstraction levels: the conceptual model (the business concepts and their relationships — account, entity, period, their hierarchies), the logical model (the dimensional structure — fact tables, dimension tables, measures, keys, grain), and the physical model (the specific table definitions, indexes, partitioning strategies, and storage formats implemented in the target platform). In finance BI, data modelling is not a preliminary step that happens once at project start; it is a governed design artefact that evolves with the business’s analytical requirements and must be managed with the same rigour as application code.
How It Works
Finance data modelling begins with the analytical requirements — the specific questions the data model must answer. Rather than starting from the source data and modelling what is available, effective data modelling starts from the business questions: “what is our OPEX by entity and cost centre for the current year versus budget?” translates to a dimensional model with an OPEX fact, an Entity dimension, a Cost Centre dimension, a Time dimension, and a Scenario dimension. The model is designed to answer this question efficiently before any source data mapping is considered.
The semantic layer — Oracle Analytics Cloud’s subject areas, Power BI’s dataset model, or a dedicated semantic layer tool — translates the physical data model into business-friendly concepts. The semantic layer is where measures are defined (not just “amount” but “Net Revenue,” “Operating OPEX,” “Gross Margin”) with their correct aggregation rules, where hierarchies are configured for drill-down navigation, and where calculation logic is centralised rather than duplicated across reports.
Design Decisions and Trade-offs
The most consequential data modelling decision for finance BI is whether to build a single enterprise finance model (one data model serving all finance reporting consumers) or multiple domain-specific models (separate models for consolidation reporting, FP&A, cost management). A single enterprise model maximises consistency and reduces maintenance overhead but increases complexity and makes it harder for different teams to evolve their domain’s model independently. Multiple models enable faster domain-specific evolution but require conformed dimension management to ensure cross-domain consistency.
Common Implementation Errors
The most common data modelling error in finance BI is placing business logic in the report layer rather than the data model — duplicating the same calculated measure in fifteen different reports rather than defining it once in the semantic model. When the “Gross Margin” calculation is defined in each report’s formula bar independently, it is defined slightly differently in each report — some including one cost category, others excluding it. The governance problem surfaces when executives compare Gross Margin from two different reports and get different numbers. Business metric definitions belong in the semantic model, enforced at one location, not scattered across report-level calculations.
How Loop Wise Solutions Designs for This
We establish a metric governance register alongside the data model — documenting every measure, its business definition, its calculation formula, and the semantic model location where it is authoritatively defined. New measure requests go through a definition review before they are added to the semantic model, preventing the proliferation of near-duplicate measures with subtly different definitions that produce inconsistent results across reports.
