Business Central + Power BI: Making Sense of Dimensions

Dimensions in Business Central are one of those features that sound simple in theory: just tag transactions with extra info like Department, Project, or Customer Group. Easy, right?

Until you try to report on them in Power BI.

The out-of-the-box model gives you the basics, but as soon as you want to slice a P&L by Department, or see sales by Project, you start to feel the cracks.

Why dimensions are tricky

Here’s the catch:

• Business Central lets you have two global dimensions (e.g. Department, Customer Group).

• Then you’ve got shortcut dimensions (up to 8 more you can make available).

• And finally, you can have additional dimensions on transactions that don’t neatly fit into those slots.

In Power BI, this quickly turns into:

• Tables with mysterious “Global Dimension 1 Code” and “Global Dimension 2 Code” fields.

• Shortcut dimensions that may or may not show up depending on how you connected.

• A mess if you try to pivot them all into separate columns.

So you end up asking: why does something so simple feel so complicated?

Option 1: Stick with the globals

If your reporting needs are simple, you can just map Global Dimension 1 and Global Dimension 2 to your preferred fields (say Department and Customer Group).

That gets you quick slicing, and it’s fine for small setups.

Example: simple lookup

let
    Sales = OData.Feed("https://api.businesscentral.dynamics.com/v2.0/{tenant}/Production/ODataV4/Company('CRONUS USA, Inc.')/SalesInvoices"),
    WithDept = Table.ExpandRecordColumn(Sales, "Global_Dimension_1_Code", {"Value"}, {"Department"})
in
    WithDept

Option 2: Bring in the Dimension Set Entries

For more flexibility, you want to look at the Dimension Set Entries table. This is where BC actually stores all the dimension values tagged to a transaction.

Pulling this into Power BI gives you a transaction → dimension relationship, which you can model properly.

Example: Dimension Set Entry query

let
    DimEntries = OData.Feed("https://api.businesscentral.dynamics.com/v2.0/{tenant}/Production/ODataV4/Company('CRONUS USA, Inc.')/DimensionSetEntries")
in
    DimEntries

From here you can join Dimension Set Entries to transactions like G/L Entries, Sales Invoices, or Purchases.

Option 3: Pivot dimensions into columns

If your finance team really wants to see Department, Project, and Customer Group as neat columns in a fact table, you can do it in Power Query.

Example: Pivot dimension values

let
    DimEntries = OData.Feed(".../DimensionSetEntries"),
    Pivoted = Table.Pivot(DimEntries, List.Distinct(DimEntries[DimensionCode]), "DimensionCode", "DimensionValueCode")
in
    Pivoted

Option 4: Expose dimensions with an AL query

If you’re comfortable with AL development (or have a partner who is), you can create a custom AL query object that joins transactions to the dimension values you need.

That way, you hit Power BI with a ready-made table — no messy joins, no pivoting in Power Query.

Example AL Query (simplified):

query 50100 "Sales with Dimensions"
{
    QueryType = Normal;
    DataItem("Sales Invoice Header"; "Sales Invoice Header")
    {
        Column("No."; "No.");
        Column("Posting Date"; "Posting Date");
        Column("Amount"; Amount);

        // Join to dimensions
        DataItem("Dimension Set Entry"; "Dimension Set Entry")
        {
            DataItemLink = "Dimension Set ID" = "Sales Invoice Header"."Dimension Set ID";
            Column("Dimension Code"; "Dimension Code");
            Column("Dimension Value Code"; "Dimension Value Code");
        }
    }
}

Publish this extension, and you can point Power BI directly at the query via OData.

Why it’s nice:

• You decide which dimensions matter (no carrying all of them).

• Clean schema for Power BI users — no complex M transformations.

• Better performance if you’re always slicing by the same few dimensions (e.g. Department + Project).

Watch outs

• Pivoting dimensions can explode your column count if you’ve got lots of them.

• Not every report needs all dimensions — pick your battles.

• Be clear with finance which version of “the truth” you’re building (sometimes BC consolidations differ from Power BI logic).

Wrap Up

Dimensions are powerful, but they’re also one of the first areas where Power BI users hit friction with Business Central. You’ve got a few paths:

• Stick with globals (simple, quick wins)

• Pull Dimension Set Entries (full flexibility)

• Pivot dimensions in Power Query (friendly columns)

• Or go the AL query route (custom, clean, high-performance)

Which one’s best? Depends on whether you’d rather keep things flexible, or keep them simple.