Key takeaways
- A semantic layer holds entities and keys, dimensions and hierarchies, and metric definitions as versioned code. Where it lives matters less than having exactly one definition per metric.
- Set the fact table grain at the journal line. Aggregating earlier feels efficient and forces a new pipeline for every question that needs detail.
- Finance needs both type 1 and type 2 slowly changing dimensions, and the ability to report results as originally reported and restated to today's organisation structure.
- Choose a BI tool on modelling capability, spreadsheet interoperability, row-level security, cost at full rollout and the skills you already have.
- Buying the tool first, lifting the existing report pack into dashboards and skipping reconciliation to the trial balance are the three failures that stop adoption.
The board pack takes nine days to produce. Three analysts maintain overlapping workbooks, each with its own version of revenue. When the CEO asks why the sales dashboard shows a different number from the management accounts, the answer takes two days and involves a reconciliation that nobody wants to repeat. Meanwhile a business intelligence tool sits licensed and largely unused, because the first dashboards built on top of the same tangled sources produced the same disagreements faster.
The missing component is almost never the visualisation tool. It is the layer between the raw data and the chart — the place where grain, hierarchy and metric definitions are decided once and enforced everywhere. Building that layer is unglamorous and it is where the entire value of finance analytics is created.
What a semantic layer actually is
A semantic layer is a governed definition of a business’s entities, dimensions and metrics, expressed once and consumed by every downstream tool. It translates between how data is stored — journal lines, subledger transactions, master records — and how the business speaks: gross margin, net revenue retention, EBITDA by business unit, receivables ageing by region.
Concretely it holds three things. Entities and their keys, so that “customer” means the same object whether the query starts in the general ledger or the CRM. Dimensions and hierarchies, so that a cost centre rolls into a function and a function into a segment, consistently. And metric definitions as code — the exact expression, filters, aggregation rule and time grain for each measure, version-controlled and reviewable.
What it is not: a data warehouse, a BI tool, or a licence purchase. It can live inside a BI platform’s modelling layer, in a dedicated headless product, or in the transformation layer of the warehouse. The architectural choice matters far less than the discipline of having exactly one definition of each metric.
Why finance is a hard first domain
Finance data looks structured and behaves awkwardly. Six characteristics cause most of the difficulty.
- The chart of accounts is a hierarchy that changes. Accounts are added, merged and remapped, and management reporting groupings differ from statutory groupings.
- History gets restated. Periods reopen, prior-period adjustments post, and a report run today for last March may legitimately differ from the same report run in April. The model must handle that deliberately rather than by accident.
- Multiple entities and currencies. Translation uses closing rates for balance sheet items and average rates for income statement items, with the difference landing in a translation reserve. That logic belongs in the model, not in each report.
- Multiple versions of truth are legitimate. Actual, budget, forecast version one, forecast version two, and statutory versus management view all coexist and must be dimensioned, not stored in separate tables.
- Allocations. Shared cost pushed to business units by a driver is a modelled calculation with its own lineage requirement.
- The reconciliation obligation. Unlike a marketing dashboard, a finance model that is approximately right is wrong. It must tie to the trial balance, exactly, every period.
Designing the model
Choose the grain first
The single most consequential decision is the grain of the core fact table — the level of detail one row represents. For finance, the default should be the journal line: one row per posting, with account, entity, cost centre, date, document reference, amount in transaction currency and in functional currency, and the source subledger. Aggregating earlier feels efficient and is almost always regretted, because every question that requires more detail then requires a new pipeline.
Around that central fact, subledger facts sit at their own grain: receivables transactions at invoice and receipt line, payables likewise, inventory movements at transaction line. Each connects to the general ledger fact through the document reference, which is what makes drill-through from a summarised margin figure to the underlying invoice possible.
The dimensions finance needs
A star schema places the fact table at the centre with dimension tables radiating from it, each joined by a surrogate key. Snowflaking — normalising dimensions into further tables — is technically tidier and usually makes query performance and comprehensibility worse. Keep dimensions wide and flat.
| Dimension | Contains | Design note |
|---|---|---|
| Date | Calendar date, fiscal period, quarter, year, period-close flags, working day number | Must carry the fiscal calendar, not just the calendar year. Include a flag for adjustment periods. |
| Account | Account code, description, statutory grouping, management grouping, sign convention, statement type | Hold both the statutory and management hierarchies as separate attributes of the same account. |
| Entity | Legal entity, functional currency, ownership percentage, consolidation group, elimination flag | Consolidation and eliminations are model logic; do not push them to the report layer. |
| Cost centre and organisation | Cost centre, department, function, business unit, segment | Track effective dates; reorganisations are the main source of broken comparatives. |
| Customer and supplier | Master record, group parent, segment, geography, industry, credit terms | Group parent enables true exposure and concentration analysis. |
| Product or service | SKU or service line, family, margin category | Grain must be consistent with how revenue is posted, or margin analysis will not tie. |
| Scenario | Actual, budget, forecast with version and as-of date | A dimension, never a set of parallel tables. |
| Currency and rate | Transaction currency, functional currency, rate type, rate value, effective period | Store rates as data. Hard-coded rates are the most common source of consolidation error. |
Handling change over time
Dimensions change, and how you handle that determines whether comparatives make sense. A type 1 slowly changing dimension overwrites the old value, so history is restated under the current structure — appropriate for correcting a misspelt customer name. A type 2 dimension adds a new row with effective-from and effective-to dates and a current flag, preserving what was true at the time — appropriate for a cost centre moving between business units, or for a customer’s credit terms.
Finance usually needs both, and needs to be able to report either way: results as originally reported, and results restated to today’s organisation structure. Making that a deliberate, documented capability rather than an accident of the load process avoids a recurring and corrosive argument about whose numbers are right.
The stack
| Layer | Purpose | Common options | Decision criteria |
|---|---|---|---|
| Ingestion | Move data from ERP, CRM, banking and subledgers into storage | Managed connectors, open-source pipelines, or vendor-supplied extracts | Connector coverage for your ERP; incremental load support; cost at your row volumes |
| Storage | Central warehouse or lakehouse | Cloud warehouse platforms; a well-tuned relational database at smaller volumes | Data volume, concurrency, data residency obligations, existing cloud relationship |
| Transformation | Cleanse, conform, build the star schema, apply business logic | SQL-based transformation frameworks with version control and testing | Testability, lineage documentation, ability of the finance team to read the code |
| Semantic layer | Define entities, dimensions and metrics once | BI-native modelling layers, or headless semantic platforms consumed by several tools | How many consuming tools; whether definitions must be reused outside BI |
| Consumption | Dashboards, self-service exploration, scheduled reporting, spreadsheet access | Enterprise BI suites, lighter self-service tools, notebook environments | Existing skills, licensing model, spreadsheet integration quality |
| Orchestration and quality | Schedule runs, test outputs, alert on failure | Workflow orchestrators; testing built into the transformation framework | Dependency complexity; how quickly a broken load must be detected |
At modest data volumes, a single cloud database, a transformation framework and one BI tool cover all six layers adequately. Elaborate architecture at small scale is a common and expensive error.
Choosing a BI tool
Assess against five criteria rather than feature lists. First, modelling capability — can it hold governed metric definitions, or does every dashboard re-implement the logic. Second, spreadsheet interoperability, because finance users will export, and a tool that makes export a live, governed connection beats one that produces a static file. Third, row-level security, which finance needs from day one so that a business unit head sees their own results. Fourth, total cost at full rollout, modelled on the number of viewers you will eventually have, not the pilot group. Fifth, the skills you already have. A capable tool your team can maintain outperforms a superior tool that depends on a contractor.
Governance that is proportionate
- Certified metrics. A defined list of metrics with owner, definition, source and refresh cadence. Anything not on the list is explicitly marked as exploratory.
- Change control on definitions. Metric changes go through review and are versioned, because a silent change to the revenue definition invalidates every historical comparison.
- Access by role. Row-level and column-level security defined in the model, particularly for payroll and margin data.
- Automated tests. Uniqueness of keys, referential integrity, non-negative balances where expected, and a standing test that the model’s trial balance ties to the source system for every period.
- Data dictionary. Plain-language definitions accessible from the dashboard itself, not in a document nobody opens.
- Dashboard lifecycle. Usage monitoring, and a retirement process. Unused dashboards are not harmless; they carry stale definitions.
Sequencing mistakes finance teams make
- Buying the BI tool first. The tool becomes the project, and the modelling work that creates the value is deferred indefinitely.
- Lifting the existing report pack into dashboards. Reproducing thirty static reports digitally produces thirty digital static reports. Start from the decisions the reports are meant to support.
- Boiling the ocean. A two-year enterprise data programme loses sponsorship. One domain, end to end, in a quarter, builds it.
- Aggregating too early. Loading monthly summaries because they are smaller, then discovering every second question needs transaction detail.
- Leaving metric definitions to whoever builds the dashboard. This is how three versions of revenue appear.
- Skipping reconciliation to the trial balance. The first time a number is shown to be wrong, adoption stops, and it does not restart easily.
- Treating it as an IT project. Finance must own the definitions, the hierarchy and the acceptance criteria. IT owns the plumbing.
- Ignoring the source. If cost centres are entered inconsistently in the ERP, no downstream layer repairs that. Master data governance is part of the project.
A realistic sequence
Phase one: pick one domain with a real decision attached — most often the general ledger and management P&L, or receivables. Define the grain, build the star schema, reconcile to the trial balance, and publish a small number of governed metrics. Phase two: add the adjacent subledger and the scenario dimension so that actual against budget works. Phase three: extend to other domains reusing the conformed date, entity and cost centre dimensions. Phase four: retire the workbooks that the model has replaced, deliberately, so the old versions stop circulating.
Judge progress by two things: the number of certified metrics that reconcile to source, and the number of hours the monthly reporting cycle no longer takes. Dashboard count is not a measure of progress.
Artham Fintech works with finance teams on data model design, semantic layer definition and the reporting build that follows, including the reconciliation controls that make the output trustworthy enough to replace the workbooks. If you are at the point of choosing a tool, it is usually worth spending an hour on the model first.