MODLR

Aggregation & Hierarchies ​

Rolling individual accounts up into EBITDA, Net Profit or any other subtotal is one of the most repeated calculations in finance. SQL is a relational engine - it has tables and rows, not hierarchies - so a rollup like EBITDA isn't a concept the database knows about; it's a query, a mapping table, or a view that has to be written, tested and kept in sync everywhere it's used. In MODLR the same rollup is defined once, as a hierarchy on the Account Dimension, and every Cube, Workview and report that uses that Dimension inherits it automatically.

Aggregating in SQL ​

To produce EBITDA from a table of GL transactions, a query has to know which account codes belong to it. That's usually done with a CASE expression against account ranges, or by joining out to a separate mapping table:

sql
select
    entity,
    period,
    sum(case when account_code between '4000' and '4999' then -amount end) as revenue,
    sum(case when account_code between '5000' and '5999' then -amount end) as cogs,
    sum(case when account_code in (select account_code from ebitda_accounts) then -amount end) as ebitda
from gl_transactions
group by entity, period;

That logic exists nowhere except inside this query. A second dashboard that also needs EBITDA either joins back to the same ebitda_accounts mapping table - if whoever built it knew that table existed - or writes its own account list from scratch. A new account added to the chart of accounts doesn't appear in EBITDA until every one of those queries and mapping tables is found and updated by hand.

A hierarchy with more than one level - accounts rolling into sub-groups, sub-groups into EBITDA, EBITDA into Net Profit - usually means a recursive CTE or a chain of joins against a parent/child mapping table, rebuilt and re-tested every time the structure changes:

sql
with recursive account_tree as (
    select account_code, parent_code, account_code as root
    from chart_of_accounts
    where parent_code is null
    union all
    select c.account_code, c.parent_code, t.root
    from chart_of_accounts c
    join account_tree t on c.parent_code = t.account_code
)
select root, sum(amount)
from gl_transactions g
join account_tree t on g.account_code = t.account_code
group by root;

Aggregating in MODLR ​

In MODLR, EBITDA and Net Profit aren't queries - they're Parent elements on a Hierarchy in the Account Dimension, as covered in About Dimensions. Individual accounts are the leaf elements; the Hierarchy groups them into subtotals - Employee Costs, Occupancy, Other Operating Expenses - up through EBITDA to Net Profit. Because that structure is defined once, on the Dimension, every Cube that uses Account inherits it, and every Workview, Card and Excel Add-in report reading from that Cube shows the correct subtotal with no formula or query behind it at all.

More than one view of the same accounts ​

A Dimension can hold multiple Hierarchies over the same leaf elements, the same way Aggregation / SUM covers for Account and Date. A Statutory Hierarchy can group costs by their nature, while a Management Hierarchy groups the same accounts by function - Sales & Marketing, G&A, R&D - and both still roll up to the same EBITDA and Net Profit. In SQL, a Statutory view and a Management view of the same P&L means two separate mapping tables or two branches of CASE logic, both needing updating whenever an account moves. In MODLR it's a second Hierarchy on the same Dimension, built from the same leaf accounts.

Illustrating the improvement ​

  • A hierarchy change - a new account, a reorg, a moved cost centre - is made once on the Dimension and every Cube, Workview and report that uses it picks it up immediately. There's no second mapping table, view or dashboard to find and update to match.
  • EBITDA and Net Profit can't drift apart between reports, because there's only one definition of them - the Hierarchy - rather than one definition per query that happens to reference it.
  • Alternate rollups of the same accounts - Statutory vs Management, or any other grouping the business needs - sit side by side as separate Hierarchies on one Dimension, rather than as duplicated table structures or increasingly nested CTEs.

Copyright © 2026 MODLR