Financial analytics has requirements that most analytics implementations are not designed for: exact reconciliation to the general ledger, strict data access controls, audit trails, and the close-process timing that requires financial data to be available at specific points in time. This guide covers the architecture decisions specific to the financial analytics domain.
Financial analytics operates under constraints that most analytics domains do not face. Numbers must reconcile exactly to the general ledger — not approximately, not close enough, but exactly. Data access must be strictly controlled because financial data is material. Changes to historical figures must be auditable. And the close process requires financial data to be available at specific points in time, in a specific state, regardless of what other data processing is occurring.
These requirements are often treated as edge cases and handled with workarounds in general-purpose analytics architectures. The result: financial teams that do not trust the analytics, manual reconciliation that consumes finance staff time, and analytical capabilities that are technically available but not usable because the numbers cannot be defended to a CFO.
The Reconciliation Requirement
Every financial metric in an analytics environment must reconcile to the source of truth, which is always the general ledger or ERP. Revenue in the analytics platform must match revenue in the ERP. Headcount costs must match payroll. Asset values must match the fixed asset register.
Reconciliation is the foundational quality requirement for financial analytics. Without it, the CFO and the finance team will not use the analytics — they will maintain their own spreadsheets and produce their own numbers, bypassing the analytics investment.
To achieve reconciliation:
**Identify the financial source tables in the ERP**: Every ERP (NetSuite, SAP, Oracle Financials, Dynamics 365, Sage) has a set of tables that represent the official financial record. For P&L analytics, these are typically the journal entry tables, the account hierarchy tables, and the period mapping tables. The analytics pipeline must integrate these tables, not calculated derivatives.
**Preserve the granularity of the source**: Journal entries are the atomic unit of the general ledger. Aggregating journal entries before loading loses the ability to drill from a summary figure back to the underlying transactions. Load journal entries at transaction grain; aggregate in the transformation layer.
**Define reconciliation tests that run on every pipeline run**: A reconciliation test compares the sum of all analytics revenue records for a period to the sum of all ERP revenue records for the same period, using the same account codes and the same period definition. If the test fails, the pipeline run fails; the data does not reach the consumption layer until the discrepancy is investigated and resolved.
Reconciliation tolerances should be zero or near-zero for financial data, unlike operational analytics where small percentage discrepancies may be acceptable. A $1 discrepancy in total revenue is worth investigating; it is not worth accepting as a rounding error.
Chart of Accounts Architecture
The chart of accounts (COA) is the taxonomy of account codes that every financial transaction is assigned to. The COA structure determines how P&L line items are computed: which account codes roll up to Revenue, which to Cost of Goods Sold, which to Operating Expenses.
**Store the COA as a dimension, not as hard-coded logic**: ERP-exported P&L reports embed the COA hierarchy as report formatting. Analytics implementations that replicate this by hard-coding account code ranges ("Revenue is accounts 4000-4999") break when the COA is restructured. Build the COA as a dimension table with account codes, account names, and rollup hierarchy. P&L analytics joins financial transactions to the COA dimension to assign each transaction to the correct P&L line.
**Handle COA changes over time**: COA changes — new account codes, restructured categories, renamed cost centres — require historical data to be reclassified or accounted for. Decide and document the policy: does a cost centre restructure retroactively reclassify historical transactions (restatement), or does historical data retain the original classification (as-reported)? Both are valid; the choice must be explicit and consistently applied.
**Currency handling for multi-entity organisations**: Multi-currency organisations require consistent currency conversion for consolidated P&L reporting. Functional currency vs reporting currency, translation dates (transaction rate vs period-end rate vs average rate for different income statement line items), and intercompany elimination all require explicit data architecture decisions.
Period Management
Financial analytics must respect accounting periods precisely. The close process produces finalised data for each period at a specific point in time; that data must be preserved and clearly distinguished from preliminary or unfinished period data.
**Period status dimension**: A period table that records whether each financial period is: open (in progress), closed (finalised, audited), or reforecast (updated with a budget revision). Analytics queries that reference closed-period data should read from the finalised snapshot, not from a live feed that might change.
**Close process data cutoff**: When the monthly close occurs, financial data for the closed period must be snapshotted and frozen. Any subsequent adjustments (journal entry revisions, reclassifications) produce new entries but the period-end snapshot for reporting purposes must be reproducible at any future point. Implement period snapshots that capture the state of financial data at close.
**Comparison period consistency**: A P&L comparison between Q2 this year and Q2 last year must use the same accounting basis for both periods. If the COA was restructured between years, restating prior periods to the current COA structure is necessary for a valid comparison. Analytics tools that pull live data for both periods without handling this produce misleading comparisons.
Access Control for Financial Data
Financial data requires strict access controls because it is frequently material:
**Account-level access controls**: Not all finance staff need access to all account types. HR-related expense accounts should be accessible to HR and finance management but not to operational managers. Legal-related accruals should be accessible to finance leadership but not widely shared. Implement row-level or column-level security based on account type, not just at the table level.
**Entity-level access controls for multi-entity organisations**: Finance staff at a subsidiary should see their entity's data; corporate finance should see all entities. Row-level security based on entity and role ensures each user sees only the data appropriate to their position.
**Audit trail for data access**: Financial analytics environments should log who accessed what financial data and when, in a form that can be retrieved for audit purposes. This is typically an audit log at the data warehouse level, not just the BI tool level.
Budgeting and Forecasting Data Architecture
Actual-vs-budget analysis is one of the most common financial analytics use cases. The architecture requires:
**Budget data loaded at the same grain as actuals**: If actuals are at account-period grain, the budget must be at the same grain for meaningful comparison. A budget loaded at annual level requires allocation to months before comparison to monthly actuals — the allocation methodology (equal distribution, seasonality-adjusted, or activity-based) must be documented and consistently applied.
**Version management for budgets**: Finance teams produce multiple budget versions: the original budget, mid-year forecasts, and revised projections. The analytics layer must track which version is the "current budget" and which are historical versions. A single budget dimension with version attribute allows filtering to any historical version for tracking forecast accuracy over time.
Our data architecture practice builds financial analytics architectures with reconciliation, period management, and access controls that the CFO and finance team can depend on — contact us to discuss your financial analytics requirements.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →