Optimal Data Modelling for Order Headers and Order Lines

[datadonuts]

Hi community,

In almost all of my projects I came across databases, that contains separate tables for headers and details / lines e.g. Order_Heads contains OrderID, CustomerID and OrderDate, while Order_Lines contains the details of each order like OrderID, Order_line_ID, product_ID, quantity. That’s a very common scenario and I deal with them in different ways, depending on the business case, the KPIs and the associated dim_tables.

On way of course is to model them as separate tables and build a relationship on the OrderID. In complex models, that often becomes a challenge, where I have to go into bi-directional relationships, which I don’t feel are optimal.

The second way is to merge them together (e.g. via Power Query) to get one big order table, containing the order information on the line granularity. That eases the data model but comes with the downside, that certain DAX becomes a bit complex and slow as well.

A third way – I call it hybrid – is to aggregate the order line table on certain figures like sum of sales, order quantity, no of products, last order date ect. and then merge that to the order header table. That works like option one plus getting more information into the header table but unfortunately loosing a lot of detail information for instance about the products, which are on the order line level.

I studied Bhavik Merchants book “Performance Best Practices” but unfortunately, he is not mentioning this scenario, even its very common in the real business world.

So I am wondering, is there a recommended or optimal way to handle that challenge. How do you do that in your projects?

1. Keep them separated with a relationship
2. Merge them into one table
3. Another method

I am glad to hear from you and discuss further.

[amitchandak]

@datadonuts , My Take

Ideally merged table should be able to solve the purpose. For header-level data we can use measure like

Sum of header discount  = Sumx(Summarize(Combined, Combined[Order Header id], [Header Discount Value]) ,[Header Discount Value] )

But some time model is more complex and you need the header as a dimension, in that case, you need both merged and header as a dimension. Especially when need to ignore the selected filter on header