Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
My questions/issues with this structure:
If you have worked with data like this (or even better, Business Central data), please let me know what the best practice is.
Thanks!
Solved! Go to 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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |