Business Central holds department, cost centre and project as dimensions, and most Power BI models lose them. How to model them properly.
The symptom
A finance manager opens the Power BI income statement, sees the right total, and then exports the general ledger entries to Excel to split it by department. That export is the clearest sign a Business Central reporting build has gone wrong, and it almost always comes down to one thing: dimensions.
Dimensions are how Business Central holds everything that is not an account or a date. Department, cost centre, project, region, product group, customer group, whatever the business decided mattered at implementation. Finance thinks in them. Budgets are set by them. And they are the first thing a Power BI model loses, because of how Business Central stores them.
How Business Central actually stores dimensions
A general ledger entry does not carry a department column. It carries a dimension set ID, a number that points to a combination of dimension values held in a separate table of dimension set entries. One entry might point to set 4,812, which means Department = Sales, Region = North, Project = blank. Two global dimensions are also copied directly onto the entry as shortcut columns for convenience, and up to six more shortcut dimensions can be exposed on documents, but the full picture is always the set.
This is efficient for the ERP and awkward for reporting. A model that pulls general ledger entries and ignores the set table gets totals with no analysis. A model that joins to the set table naively gets one row per dimension value and doubles the ledger. Most of the broken Business Central reports we see sit in one of those two states.
Microsoft's own Power BI apps solve this with a flattened table that a job queue keeps in step, which is why the apps show no dimensions when that job stops. A model of your own needs the same idea, built deliberately.
The model that works
Flatten the sets once. Build one table with one row per dimension set ID and one column per dimension the business uses: Department, Cost Centre, Project, Region and so on. Every ledger, sales, purchase and job entry joins to it on the set ID. This is the single most important table in a Business Central model and it is worth an afternoon to get right.
Make each dimension a proper dimension table. Department is not just a code on the flattened table; it is its own table with the code, the name, the hierarchy if there is one (departments roll up to divisions), and a sort order finance agrees. That is what lets a report show divisions with departments beneath them without a DAX workaround.
Handle blanks on purpose. A large share of entries have no value for a given dimension, and finance needs to see that as a line called Unallocated rather than a blank that drops off a chart. Decide the label and apply it in the model, not in each report.
Treat changes as history. Dimension values get renamed, merged and retired. A cost centre that was closed in March still has entries in February. Keep the historic values, map them to the current structure with a dated mapping table, and let the report show either view. Doing this in the model is the difference between a report finance trusts and one they argue with every month end. The same principle applies across companies, as our consolidation post describes.
Bring budgets in on the same keys. Business Central budgets are held by account and dimension too. If the budget table joins to the same dimension tables as actuals, variance by department is a measure rather than a spreadsheet. If it does not, the Excel export comes back.
Why this matters beyond finance
Sales by region, margin by product group, project profitability by job and spend by cost centre are all dimension questions. Get the dimension model right once and every departmental report on top of it is a day's work. Get it wrong and each report is its own reconciliation exercise. It is also what makes a Business Central model useful to AI: an agent asked about margin in the Northern region can only answer if the region exists as a thing in the model rather than a code buried in a set ID. Data model or ontology? is the longer version of that point.
Where to start
List the dimensions your finance team actually uses in month end, not the ones set up at go-live and never touched. Usually it is three or four. Build the flattened set table for those, make each a dimension table with the hierarchy finance recognises, label the blanks, and date the mapping. Then rebuild the income statement by department on top and ask the finance manager whether they still need the export. If the answer is no, the rest of the reporting follows. The Business Central reports a finance team needs sets out what to build next, and our Business Central analytics page describes how we approach the whole model.
Related reading
The Business Central reports a finance team needs
Consolidating multiple Business Central companies in one model
Power BI on Business Central in practice: pitfalls, embedding, and upgrades
Why your Power BI report is slow, and the five things to fix first
Kiran Sahiba
Analytics Solutions Consultant
Part of the Hopton Analytics team, delivering governed analytics programmes for UK mid-market organisations.
