Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Star schema modelin OrderHeader and OrderDetails

Hi,

 

I have and OrderHeader table with the unique Order_id and the OrderDetails with many products, which relates to one Order.

 

So: OrderHeader 1:n OrderDetails 

 

Should i put OrderHeader and OrderDetails in one fact table or should i seperate them into two facts and build a 1:n relationship based on Order_id between them? 

 

What is best practise? 

 

 

 

 

1 ACCEPTED SOLUTION
KNP
Super User
Super User

@Anonymous- that is very dependent on what you're trying to achieve with your reporting from this model and probably not really enough info to provide a definitive answer. 

For example, if there's a need to report on individual orders, your order headers table may act as a dimension and if that level of granularity is not required, then @amitchandak may be correct and merging the tables may be the correct approach.

 

It would probably be worthwhile sharing a screen shot of the model as it stands currrently showing the existing relationships.

 

Best practice for Power BI performance is always a star schema but defining whether something is a fact or dimension often depends on the grain required for reporting.

 

Hope this helps.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

3 REPLIES 3
KNP
Super User
Super User

@Anonymous- that is very dependent on what you're trying to achieve with your reporting from this model and probably not really enough info to provide a definitive answer. 

For example, if there's a need to report on individual orders, your order headers table may act as a dimension and if that level of granularity is not required, then @amitchandak may be correct and merging the tables may be the correct approach.

 

It would probably be worthwhile sharing a screen shot of the model as it stands currrently showing the existing relationships.

 

Best practice for Power BI performance is always a star schema but defining whether something is a fact or dimension often depends on the grain required for reporting.

 

Hope this helps.

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
Anonymous
Not applicable

"need to report on individual orders, your order headers table may act as a dimension (...)"

I dont want to lose any information so this is required. And in the end I want so say which OrderDetails are related to which OrderHeader. That is simply a 1:n relationship, but that is not best practise for facts... Is it allowed to let the degenerated dimension "order_id" in the OrderHeader be a dimension for the OrderDetails? 

 

 

 

amitchandak
Super User
Super User

@Anonymous , On fact, so the dimension related both header and details can directly join to this new fact.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.