Most finance reporting comes down to three numbers: the balance at the start of a period, the movements during it, and the balance at the end. A general ledger, a bank account, a loan book, or a work-in-progress (WIP) balance all follow this shape.
This is simple when your warehouse already stores daily balance snapshots. It gets harder when your source is a raw ledger of individual transactions, with no pre-computed balance anywhere. You cannot just sum the amounts, because a balance is not additive across time.
This guide shows a complete pattern for solving this in Omni. You start with raw transactions and finish with self-service opening and closing balances that stay correct no matter which dimensions a user picks, or in what order.
Where this comes up
The trigger is always the same. Your data records individual movements, but the question people ask is about a position at a point in time.
You will hit this pattern whenever someone asks for a “balance,” a “position,” or “where we stood” as of a date, and the underlying table only holds transactions:
- Finance closes the month and needs opening and closing cash per entity, with the movements reconciling between them.
- A controller wants WIP or accrued revenue at the start and end of a period, sliced by client, service line, or owner.
- A lending team reports loan book balance by product and vintage, built from drawdowns, repayments, and write-offs.
- An ops lead wants stock on hand by warehouse, built from purchase orders, sales, and adjustments.
- A RevOps team reports ARR or MRR at period start and end, built from subscription change events.
In every case the source is a movement log, and the requirement is a cumulative position that has to roll up cleanly across any combination of dimensions.
The same pattern across domains
| Domain | Balance metric | Transaction source | Entity key |
|---|
| Finance and treasury | Cash or account balance | Payments, receipts, transfers | account_id |
| Professional services | Work-in-progress (WIP) | Time entries, disbursements, invoicing | engagement_id |
| Manufacturing and construction | Work-in-progress (WIP) | Material, labour, and overhead postings | job_id |
| SaaS and subscriptions | ARR or MRR | New, renewal, churn, expansion events | subscription_id |
| Lending and credit | Loan book balance | Drawdowns, repayments, write-offs | loan_id |
| Inventory and supply chain | Stock on hand | Purchase orders, sales, adjustments | sku_id + warehouse_id |
| Insurance | Claims reserves | New claims, payments, re-estimates | claim_id |
| HR and workforce | Active headcount | Hires, terminations, transfers | employee_id |
Work-in-progress shows up in two different industries, and both fit this pattern. In professional services such as accounting, legal, and consulting, WIP is unbilled time and disbursements: work that has been delivered but not yet invoiced. In manufacturing and construction, WIP is the cost of partially finished goods or a job mid-build, the materials, labour, and overhead accrued before completion. The inputs differ, but the accounting is the same. Each is a running balance built from a transaction log, so both report opening and closing WIP using the steps below.
The problem: semi-additive measures
Most measures are fully additive. You can sum them across any dimension and get a correct result. Revenue, deposits, hours worked, and order count are all additive.
Balances are different. They are semi-additive. You can sum them across dimensions like account, region, or customer, but you cannot sum them across time. A balance of $10,000 on Monday and $10,500 on Tuesday is not $20,500. The balance grew by $500.
To report a balance you have to do two things in order:
- Pick a single point in time, the first or last date in the period.
- Then sum across the other dimensions.
This is exactly what omni_dimensionalize is built for.
Requirements
To follow this guide you will need:
- A transactional source table with a transaction date, an entity key, the descriptive dimensions you want to slice by, and a signed amount per movement.
- A transformation layer (dbt, scheduled SQL, or similar) to build the snapshot table in Step 1.
- Connection permissions to edit the shared model, or a development branch to work in.
The three-step pattern
Build a daily snapshot table
Your transformation layer computes a daily snapshot with a running balance per entity. The key design decision is the partition key.Partition by the entity key, not by the descriptive dimensions. The account_id (or loan_id, wip_id, and so on) is the thing that accumulates balance. Customer, region, and account type are attributes of that entity. Partitioning by the entity key is simpler, faster, and correct. If an attribute changes, for example an account moves to a new region, the running balance still tracks against the entity.Materialize this in the transformation layer, do not compute it live. Build the snapshot once as a table in dbt or a scheduled job and let Omni query the result. A window function that scans the full ledger is expensive to re-run on every query, and materializing it keeps the running balance deterministic and fast. Refresh it on the same cadence as your reporting, which is daily for most balance use cases.Snapshot at the lowest grain that accumulates a balance, which may be finer than the account. Here, one row per account_id per day is enough. In other domains the entity sits below the account: inventory is per sku_id + warehouse_id, WIP is per job_id or engagement_id, and a multi-currency account is per account_id + currency. Build the snapshot at that grain. Omni rolls it up to whatever a user groups by, but it can never recover detail the snapshot has already aggregated away.Take a ledger of account transactions:| date | account_id | region | account_type | customer | amount |
|---|
| 2025-01-01 | ACC-1001 | APAC | Savings | Acme Corp | $5,000 |
| 2025-01-01 | ACC-1002 | EMEA | Current | Globex Inc | $12,000 |
| 2025-01-01 | ACC-1003 | APAC | Current | Acme Corp | $8,000 |
| 2025-01-15 | ACC-1001 | APAC | Savings | Acme Corp | $2,500 |
| 2025-01-15 | ACC-1002 | EMEA | Current | Globex Inc | -$3,000 |
| 2025-02-01 | ACC-1001 | APAC | Savings | Acme Corp | $1,000 |
| 2025-02-01 | ACC-1003 | APAC | Current | Acme Corp | -$2,000 |
| 2025-02-15 | ACC-1002 | EMEA | Current | Globex Inc | $7,500 |
| 2025-03-01 | ACC-1001 | APAC | Savings | Acme Corp | -$500 |
| 2025-03-01 | ACC-1003 | APAC | Current | Acme Corp | $4,000 |
account_id:-- account_daily_snapshot: materialize in your transformation layer
-- (e.g. a dbt model, or a scheduled table in Snowflake or Databricks)
with daily_movements as (
select
transaction_date as snapshot_date,
account_id,
region,
account_type,
customer,
sum(amount) as daily_movement
from stg_account_transactions
group by all
)
select
*,
sum(daily_movement) over (
partition by account_id
order by snapshot_date
rows between unbounded preceding and current row
) as running_balance
from daily_movements
| snapshot_date | account_id | region | account_type | customer | daily_movement | running_balance |
|---|
| 2025-01-01 | ACC-1001 | APAC | Savings | Acme Corp | $5,000 | $5,000 |
| 2025-01-01 | ACC-1002 | EMEA | Current | Globex Inc | $12,000 | $12,000 |
| 2025-01-01 | ACC-1003 | APAC | Current | Acme Corp | $8,000 | $8,000 |
| 2025-01-15 | ACC-1001 | APAC | Savings | Acme Corp | $2,500 | $7,500 |
| 2025-01-15 | ACC-1002 | EMEA | Current | Globex Inc | -$3,000 | $9,000 |
| 2025-02-01 | ACC-1001 | APAC | Savings | Acme Corp | $1,000 | $8,500 |
| 2025-02-01 | ACC-1003 | APAC | Current | Acme Corp | -$2,000 | $6,000 |
| 2025-02-15 | ACC-1002 | EMEA | Current | Globex Inc | $7,500 | $16,500 |
| 2025-03-01 | ACC-1001 | APAC | Savings | Acme Corp | -$500 | $8,000 |
| 2025-03-01 | ACC-1003 | APAC | Current | Acme Corp | $4,000 | $10,000 |
Model the balances with omni_dimensionalize
This is where Omni does the work. omni_dimensionalize converts a measure into a dimension within the current query’s grouping context. Use it to find the first and last date in any filtered period, per group, then filter the running balance to only those dates.dimensions:
# First and last date WITHIN each GROUP BY combination
first_date_in_period:
hidden: true
sql: omni_dimensionalize(min(${snapshot_date}))
last_date_in_period:
hidden: true
sql: omni_dimensionalize(max(${snapshot_date}))
is_opening_date:
hidden: true
sql: ${snapshot_date[date]} = ${first_date_in_period[date]}
is_closing_date:
hidden: true
sql: ${snapshot_date[date]} = ${last_date_in_period[date]}
measures:
# Semi-additive: sum the running balance, but only on the opening date
opening_balance:
label: Opening Balance
sql: ${running_balance}
aggregate_type: sum
format: currency_2
filters:
is_opening_date:
is: true
# Semi-additive: sum the running balance, but only on the closing date
closing_balance:
label: Closing Balance
sql: ${running_balance}
aggregate_type: sum
format: currency_2
filters:
is_closing_date:
is: true
# Additive: movements can be summed freely across time
period_movement:
label: Period Movement
sql: ${daily_movement}
aggregate_type: sum
format: currency_2
- They pick dimensions, for example Region and Account Type, and filter to a date range such as Q1 2025.
omni_dimensionalize(min(${snapshot_date})) finds the earliest date within each group in the result set.is_opening_date is true only for rows on that group’s first date.- The
opening_balance measure sums running_balance only where is_opening_date is true.
- Because the snapshot is at the entity grain, Omni’s
sum() rolls up correctly across entities inside each group.
The hierarchy a user selects is just a GROUP BY ordering. As long as the snapshot sits at the lowest grain, one row per entity per day, Omni handles any roll-up automatically.The opening and closing measures are filtered measures. The movement measures need no special handling. Separate additive from semi-additive measures
This is the distinction to make clear to your users.| Measure | Type | Sum across time? | Sum across dimensions? | Omni approach |
|---|
| Daily movement | Additive | Yes | Yes | Standard aggregate_type: sum |
| Deposits | Additive | Yes | Yes | Standard aggregate_type: sum |
| Withdrawals | Additive | Yes | Yes | Standard aggregate_type: sum |
| Opening balance | Semi-additive | No | Yes | Filtered measure with omni_dimensionalize |
| Closing balance | Semi-additive | No | Yes | Filtered measure with omni_dimensionalize |
closing_balance - opening_balance. Growth percent is (closing_balance - opening_balance) / opening_balance. Define these as standard measures that reference the others. Why dimension order does not matter
A common worry is whether the numbers change when a user reorders dimensions. They do not. Here is the proof using the account data above, filtered to Jan 1 to Mar 1 2025.
Hierarchy A: Region, then Customer, then Account Type
| Region | Customer | Account Type | Opening | Closing | Movement |
|---|
| APAC | Acme Corp | Savings | $5,000 | $8,000 | $3,000 |
| APAC | Acme Corp | Current | $8,000 | $10,000 | $2,000 |
| EMEA | Globex Inc | Current | $12,000 | $16,500 | $4,500 |
| Customer | Account Type | Region | Opening | Closing | Movement |
|---|
| Acme Corp | Current | APAC | $8,000 | $10,000 | $2,000 |
| Acme Corp | Savings | APAC | $5,000 | $8,000 | $3,000 |
| Globex Inc | Current | EMEA | $12,000 | $16,500 | $4,500 |
GROUP BY contains the same columns, so Omni generates identical SQL.
Rolled up to Region only
| Region | Opening | Closing | Movement |
|---|
| APAC | $13,000 | $18,000 | $5,000 |
| EMEA | $12,000 | $16,500 | $4,500 |
| Grand total | $25,000 | $34,500 | $9,500 |
omni_dimensionalize finds the min and max date across all accounts in each region. APAC opening is ACC-1001 ($5,000) plus ACC-1003 ($8,000) on Jan 1, which is $13,000. APAC closing is ACC-1001 ($8,000) plus ACC-1003 ($10,000) on Mar 1, which is $18,000.
The balances aggregate correctly at every level, because the snapshot is at the entity grain and omni_dimensionalize adapts to whatever the current GROUP BY is.
Variations and advanced patterns
Reconciliation: closing equals next period’s opening
If users need to confirm that the closing balance for one period equals the opening balance for the next, build a reconciliation view that materializes both values side by side, per period and per dimension combination.
All-time balance alongside a filtered period
To show an all-time balance next to the filtered period’s opening and closing, use cancel_query_filters on a level of detail field:
dimensions:
all_time_balance:
sql: ${running_balance}
group_by:
aggregate_type: sum
fixed: [ account_id ]
cancel_query_filters: true
When you already have snapshot data
If your warehouse already stores daily balance snapshots, skip Step 1 and go straight to the omni_dimensionalize pattern in Step 2.
Next steps
