Forum Discussion
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-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:
- 'Order Header Table'[Order No] - 'Order Lines Table'[Order No] , One-to-many
- 'Order Lines Table'[No] - 'Item Table'[Item No] , one-to-many
- 'Order Lines Table'[No] - 'Resource Table'[Resource No] , one-to-many
My questions/issues with this structure:
- 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
- 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!
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
4 Replies
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 DekkerThis 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=iK0uKo2G8tAWhat I didnt expect is that the end fact table is at the Line level of granularity.
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.
