The short answer
Start by writing down what one row means. Put measurable events or balances with the same grain in a fact table, reusable descriptive context in dimensions, and prefer clear one-to-many relationships from dimensions to facts. Do not force invoices, lines, payments and financial-report balances into one universal accounting fact.
Grain is the first design decision
Microsoft's Power BI star-schema guidance emphasises consistent fact-table grain. In accounting models that means deciding whether a row is an invoice, an invoice line, a payment, a ledger/report cell, an activity or a period-end balance before creating relationships or measures.
Microsoft Power BI star-schema guidance →
Use separate facts for separate accounting events
| Fact | Example grain | Useful for |
|---|---|---|
| Financial summary | Account/report row at a period or as-at date | P&L and balance-sheet reporting |
| Document | One invoice or bill | Status, due date, document totals |
| Document line | One line within a document | Item, tax, tracking or category analysis |
| Payment | One settlement/allocation event | Cash collection and payment timing |
| CRM activity | One supported activity | Calls, meetings, duration and work volume |
Why one general-ledger fact is not the answer to every finance question
A general-ledger fact can be central to financial analysis, but it does not automatically replace invoice status, payment timing, ageing, item detail or a provider's own financial-report output. If the question is “which invoices are overdue?”, the document or ageing grain is usually more direct than reconstructing that state from ledger entries. If the question is “what did the provider report on the balance sheet at month end?”, preserve the provider-report semantics rather than assuming a transaction sum is identical.
A good accounting model can therefore contain several facts that share dimensions while keeping their measures separate.
Dimensions describe, filter and group
Date, account, contact, item, owner, company and tracking dimensions can provide reusable descriptive context. A dimension key used on the one side of a relationship should be unique for the intended model scope.
Why flat joins inflate totals
One invoice can have four lines. One deal can have three activities. If a header-level amount is copied onto each child row and summed, the result multiplies even though all source records are technically present. This is a modelling error, not necessarily an extraction error.
Period movements and point-in-time balances need different treatment
A P&L measure normally represents movement over a period. A balance-sheet figure represents a position at a date. Summing repeated month-end balances across months does not mean the same thing as summing monthly revenue. Preserve those semantics in the fact design and measure logic.
Prefer a smaller first model
Load only the facts and dimensions required for the first reporting question. A smaller model is easier to reconcile and gives fewer opportunities for ambiguous relationships. Add another fact only when a defined question requires its grain.
Reconcile before adding presentation complexity
- Pick a source total and period.
- Load the relevant fact without optional relationships.
- Confirm the base measure.
- Add dimensions one at a time.
- Recheck the total after each relationship.
- Only then add complex DAX and visuals.
See Datplan's BI package → See the Xero reconciliation example →
Keys should identify the dimension member you think they identify
Do not create relationships from display names merely because they look unique in a test file. Names can change, collide or differ between organisations. Prefer stable source or transformed keys where the reporting contract defines them, and check uniqueness on the dimension side before creating a one-to-many relationship.
Avoid fact-to-fact joins as a shortcut
Invoices and payments may share a document identifier, while deals and activities may share a CRM association. That does not make a direct fact-to-fact relationship the safest model. A direct join can create many-to-many behaviour or repeat amounts. Use the documented relationship path or an explicit bridge/dimension when the business question requires it.
A simple accounting model can answer several questions without one giant table
| Measure | Fact | Dimensions |
|---|---|---|
| Revenue by month/account | Financial summary/activity appropriate to the source question | Date, account |
| Overdue invoices by customer | Invoice/document | Date, contact/customer |
| Sales by item | Document line | Date, item, tracking/category |
| Cash collected by week | Payment | Date, contact/customer |
Each measure can share descriptive dimensions where appropriate while remaining on the fact grain that matches the question.
A cleaner model also helps usability
Separating dimensions from facts reduces repeated descriptive values and makes the model easier to navigate. More importantly, a user selecting an account, customer or period can understand which facts should respond. Performance is useful, but clarity and correct aggregation are the first goals for finance reporting.
Keep filter direction easy to explain
For a straightforward star schema, dimensions normally filter the facts they describe. Bidirectional filtering can be useful in specific models, but it also creates extra filter paths that are harder to reason about when several facts share dimensions. Start with the simplest relationship direction that answers the reporting question and only add complexity after the base totals still reconcile.
This is particularly important when finance, invoice and payment facts coexist. A customer filter may validly affect invoices and payments, while a payment fact should not become an accidental bridge that changes a financial-summary measure.