Business Central + Power BI: Making Sense of Dimensions

Dimensions in Business Central are one of those features that sound simple in theory. You 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).

• 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). This gets you quick slicing, and it’s fine for small setups.

Typical setup

• Global Dimension 1 = Department

• Global Dimension 2 = Customer Group

  
  

In Power BI, you usually see this on tables like G/L Entry:

• Global Dimension 1 Code

• Global Dimension 2 Code

Create two small lookup tables from the Dimension Value table:

• Filter where Dimension Code = 'DEPARTMENT'

• Filter where Dimension Code = 'CUSTOMERGROUP'

Model pattern

• G/L Entry[Global Dimension 1 Code] → Department[Code]

• G/L Entry[Global Dimension 2 Code] → Customer Group[Code]

This gives you:

• Clean slicers

• Fast performance

• Very little modelling work

  
  

Trade-off: you’re locked into two dimensions only.

Option 2: Bring in the Dimension Set Entries

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

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

Each transaction in Business Central stores a Dimension Set ID, and the actual dimension values live in Dimension Set Entry.

Core tables

• G/L Entry → Dimension Set ID

• Dimension Set Entry → Dimension Set ID, Dimension Code, Dimension Value Code

Power BI model

• G/L Entry[Dimension Set ID] → Dimension Set Entry[Dimension Set ID]

• Dimension Set Entry[Dimension Value Code] → Dimension Value[Code]

This creates a many-to-many style bridge, but it reflects the truth of how Business Central works.

What you gain

• Unlimited dimensions

• Accurate historical reporting

• One consistent pattern across G/L, sales, and purchases

What you pay

• More complex model

• Measures need care to avoid double counting

  
  

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.

This works well when finance wants flat tables.

Starting point: a query that returns:

• Entry No

• Dimension Code

• Dimension Value Code

Power Query outline

Source = DimensionSetEntries,
Filtered = Table.SelectRows(Source, each 
    [Dimension Code] = "DEPARTMENT" or 
    [Dimension Code] = "PROJECT" or 
    [Dimension Code] = "CUSTOMERGROUP"
),
Pivoted = Table.Pivot(
    Filtered,
    List.Distinct(Filtered[Dimension Code]),
    "Dimension Code",
    "Dimension Value Code"
)

Resulting columns:

• Department

• Project

• Customer Group

Join this back to your fact table on Entry No or Dimension Set ID.

Why teams like this

• Easy to read

• Simple measures

• Feels familiar to Excel users

Watch out

• Every new dimension means a model change

• Column count can grow fast

Option 4: Dimension Set Entries with a Reusable Power Query Function

This builds on the Dimension Set Entry approach above, but packages it into a reusable function so you don’t repeat logic for every dimension.

If you want flexibility without turning your model into a monster, the most reliable path is to work with Dimension Set Entries — because that’s where Business Central truly stores the dimension tags for each transaction.

Rather than duplicating steps for every dimension (Project, Department, Customer Group…), we use a single Power Query function that:

• connects to Business Central via ApiContentsWithOptions

• navigates to Web Services

• loads DimensionSetEntries

• filters by Dimension_Name

• shapes the output into a clean table

• optionally enriches with extra attributes when needed

Example: Function to return a clean dimension table

(companyName as text, dimensionName as text, valueColumn as text) as table =>
let
    Source      = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, null),
    ProdEnv     = Source{[Name="PRODUCTION"]}[Data],
    CompanyData = ProdEnv{[Name=companyName]}[Data],
    WebServices = CompanyData{[Name="WebServices"]}[Data],

    DSE_Table   = WebServices{[Name="DimensionSetEntries", Signature="table"]}[Data],
    DSE_Filter  = Table.SelectRows(DSE_Table, each [Dimension_Name] = dimensionName),
    DSE_Selected =
        if dimensionName = "Trade" then
            Table.SelectColumns(
                DSE_Filter,
                {"Dimension_Set_ID","Dimension_Value_Code","Dimension_Value_ID","Dimension_Name","Dimension_Value_Name"}
            )
        else
            Table.SelectColumns(
                DSE_Filter,
                {"Dimension_Set_ID","Dimension_Value_Name","Dimension_Value_Code"}
            ),
FetchDimensionValues =
    () as table =>
    let
        DimValsAny =
            try WebServices{[Name="DimensionValues", Signature="table"]}[Data]
            otherwise try WebServices{[Name="dimensionValues", Signature="table"]}[Data]
            otherwise WebServices{[Name="Dimension Values", Signature="table"]}[Data],
        Shaped = Table.SelectColumns(DimValsAny, {"Code","Blocked"}, MissingField.Ignore)
    in
        Shaped,
    WithBlocked =
        if Text.Upper(dimensionName) = "PROJECT" then
            let
                DimValuesTable = FetchDimensionValues(),
                Joined         = Table.NestedJoin(DSE_Selected, {"Dimension_Value_Code"}, DimValuesTable, {"Code"}, "DV", JoinKind.LeftOuter),
                Expanded       = Table.ExpandTableColumn(Joined, "DV", {"Blocked"}, {"Blocked"})
            in
                Expanded
        else
            Table.AddColumn(DSE_Selected, "Blocked", each null, type logical),
    Renamed     = Table.RenameColumns(WithBlocked, {{"Dimension_Value_Name", valueColumn}}),
    WithCompany = Table.AddColumn(Renamed, "Company", each companyName, type text),
    ChangedType =
        Table.TransformColumnTypes(
            WithCompany,
            List.Combine({
                { {"Dimension_Set_ID", type text}, {valueColumn, type text}, {"Company", type text}, {"Blocked", type logical} },
                if dimensionName = "Trade"
                then { {"Dimension_Value_Code", type text}, {"Dimension_Value_ID", Int64.Type}, {"Dimension_Name", type text} }
                else { {"Dimension_Value_Code", type text} }
            })
        )
in
    ChangedType

Example: Calling the function for Project

let
    Project = fnGetDimensionEntries(
        "CompanyName",
        "Project",
        "ProjectName"
    )
in
    Project

This returns a tidy table with fields you can rely on:

• Dimension_Set_ID

• Dimension_Value_Code

• ProjectName (renamed via valueColumn)

• Company

• Blocked (only populated when dimension = Project) - Change this to suit your environment

How to use this in your model

Pattern A: Join to transactions (recommended)

Most transactional tables (G/L Entries, Sales Invoice Lines, etc.) will have a Dimension Set ID (or similar). You can join using:

• Fact table [Dimension Set ID] → Dimension table [Dimension_Set_ID]

Then you can slice by ProjectName, DepartmentName, etc. without trying to maintain 10 different “dimension code” columns in every fact.

Pattern B: Pivot for finance-friendly columns (only when needed)

If finance wants Department/Project/Customer Group as columns, build separate tables using the same function (Project, Department, Customer Group), then merge them to a list of unique Dimension_Set_ID and pivot from there.

This keeps the heavy lifting in one place and prevents duplicated logic.

Watch Outs

• Large Dimension Set Entry tables can be heavy; filter early and only bring back what you need

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

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

  
  

The Importance of Data Insights

Understanding how to effectively use dimensions in Business Central can significantly enhance your reporting capabilities. By leveraging the right options, you can gain valuable insights into your operations. This can help you make informed decisions and drive your business forward.

Conclusion

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)

  
  

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