Star schema modelin OrderHeader and OrderDetails

[Anonymous]

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? 

[KNP · Accepted solution]

@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.

[Anonymous]

"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]

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