Forum Discussion
Best practice for structuring data with Header and Lines tables
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=iK0uKo2G8tAWhat I didnt expect is that the end fact table is at the Line level of granularity.
