Best practice for structuring data with Header and Lines tables

[lachlanP]

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-0001	Order 1
ORD-0002	Order 2
ORD-0003	Order 3

Order Lines Table

Line No	Order No	Type	No.	Description	QTY	UOM
1000	ORD-0001	Item	I-001	Item 1	1	Each
2000	ORD-0001	Resource	R-001	Resource 1	1	Hour
1000	ORD-0002	Item	I-002	Item 2	1	Each
2000	ORD-0002	Resource	R-002	Resource 2	1	Hour
1000	ORD-0003	Item	I-003	Item 3	1	Each
2000	ORD-0003	Resource	R-003	Resource 3	1	Hour

Item Table

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

Resource Table

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

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!

[jeroendekk · Accepted solution]

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

[lachlanP]

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!

[lachlanP]

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]

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.