cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lachlanP
Helper II
Helper II

Best practice for structuring data with Header and Lines tables

I am looking for some feedback on structuring data in Power Bi which is organized via header and lines tables. Specifically, this data is coming from Business Central - almost all of the tables are structured like this.

Example:

Order Header Table

Order No       Order Description       
ORD-0001Order 1
ORD-0002Order 2
ORD-0003Order 3

 

Order Lines Table

Line No       Order No       Type            No.           Description     QTY      UOM   
1000ORD-0001ItemI-001Item 11Each
2000ORD-0001ResourceR-001Resource 11Hour
1000ORD-0002ItemI-002Item 21Each
2000ORD-0002ResourceR-002Resource 21Hour
1000ORD-0003ItemI-003Item 31Each
2000ORD-0003ResourceR-003Resource 31Hour

 

Item Table

Item No.      Description     Cost    UOM      
I-001Item 1$10Each
I-002Item 2$15Each

 

Resource Table

Resource No     Description       Cost    UOM     
R-001Resource 1$25Hour
R-002Resource 2$30Hour

 

The way I have structured this in the past is with the following relationships:

  1.  'Order Header Table'[Order No] - 'Order Lines Table'[Order No] , One-to-many
  2.  'Order Lines Table'[No] - 'Item Table'[Item No] , one-to-many
  3.  'Order Lines Table'[No] - 'Resource Table'[Resource No] , one-to-many

 

My questions/issues with this structure:

  1.  Multiple relationships from the 'Order Lines Table'[No] field to related tables. 
    • Which relationship to make active vs inactive? They are both equally important.
    • Maybe the Lines table should be split by the 'Type' column? But then the same issue occurs between these new tables and the order header table
  2.  I want to be able to set slicers for 'Item' and 'Resource' and filter a table of the order headers based on these. If 'Item 1' is selected in the slicer, I want to only see 'Order 1' in the above example. This doesnt work the way I have my table relationships.

 

If you have worked with data like this (or even better, Business Central data), please let me know what the best practice is.

 

Thanks!

1 ACCEPTED SOLUTION
jeroendekk
Resolver IV
Resolver IV

Hi Iachlan
I have worked a lot with Microsoft Dynamics data like this.

The awnser is, Best practice, create a star schema. I could explain why but Albero did it way better.
https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/

I believe this should solve most of your issues.
Best regards,
Jeroen Dekker


View solution in original post

4 REPLIES 4
jeroendekk
Resolver IV
Resolver IV

Hi Iachlan
I have worked a lot with Microsoft Dynamics data like this.

The awnser is, Best practice, create a star schema. I could explain why but Albero did it way better.
https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/

I believe this should solve most of your issues.
Best regards,
Jeroen Dekker


This is exactly what I was hoping to find, thank you @jeroendekk 

 

I am looking right now but do you have any sources which go over how to convert a Header/Details tale set into a single fact table? Would it simply be adding a column to the header table for every piece of information in the details table? I wonder how this could be done dynamically in Power Query as the number of lines linked to an order is not static or pre defined.

 

Thanks!

I found this video helpful, 

https://www.youtube.com/watch?v=iK0uKo2G8tA

 

What I didnt expect is that the end fact table is at the Line level of granularity.

Gabriel_Walkman
Responsive Resident
Responsive Resident

I think you just have the direction of the relationships the wrong way around.

The order description is a dimension of the item or resource fact tables, not the other way around.

Changing the direction will take care of your problems with having multiple relationships from Order Lines Table, as they can both be active.

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.