Converting Header & Line Tables to a Star Schema for Reporting

A guide to merging these special tables for a better report authoring & consuming experience.

Introduction

When you report on ERP data, you often work with two types of tables: Header and Line. These tables split the data as to avoid storing the same information multiple times.

For example, in Microsoft’s Business Central, Sales Invoice Header records information that applies to the whole invoice (like date and salesperson) and Sales Invoice Line holds information that applies to each item in the invoice (like quantity and price).

If you use a reporting tool like Power BI and import this data model exactly as it is, you end up with more than one fact table:

Normalized header & line tables — Sales Invoice Header & Sales Invoice Line are both fact tables

Combining these tables into a single one can yield benefits:

Smaller model size (as fewer columns are needed to support table relationships)
Faster report filtering logic (as fewer tables need to be traversed)
More intuitive report authoring experience (as related columns are found under a single table)

Walkthrough

This can easily be achieved in Power Query Editor by joining the two tables together. Taking the “Sales Invoice” example from above:

Select the Sales Invoice Header query and click “Merge Queries as New” under the Home tab
Merge it with Sales Invoice Line based on the No and Document_No columns

Merging header & line tables by their document number (primary/foreign key)

Expand the Sales Invoice Line column of the resulting query to choose columns relevant to the report

Expanding line table column — Expanding the Sales Invoice Line column

Rename this new query as Sales Invoice
Right-click and uncheck “Enable load” for Sales Invoice Header and Sales Invoice Line. Data from these queries will still be refreshed, but they will no longer appear in the model view.

Updated list of queries — Only the query merging the two tables is exposed to the report (queries in italics aren't)

Apply changes and re-create necessary relationships with dimension tables in the report’s model view

Star schema structure — Updated model structure with a single fact table in a star schema design

Conclusion

Joining fact tables is straightforward and becomes increasingly attractive as a report builds on multiple sets of header & line tables.

A recent report I worked on queried these 3 sets of tables:

Purchase Header & Purchase Line
Purchase Receipt Header & Purchase Receipt Line
Purchase Invoice Header & Purchase Invoice Line

Going through these steps reduced the number of fact tables from 6 to 3, already simplifying model relationships and DAX queries.

In general and on large datasets in particular, it is recommended to measure performance before & after the change, as it is possible for this technique to adversely impact model size and query timings as well.

Converting Header & Line Tables to a Star Schema for Reporting

Introduction

Walkthrough

Conclusion

More reading