Optimal Data Model - header detail

[clay_75]

Hi all, I'm new to Power BI and need some help on what would be the better data model for the below scenario. 

I want to analyse order history and have some measures at the header level and some at the detail e.g.

I am using SQL Views as my datasource. My question:

Should I be creating 1 View to return Header and Detail and then use DAX to handle the header level aggregations e.g. in the above example order O1 has 2 lines so I couldn't simply sum the TurnAroundTimeMinutes as it would give me double.

Alternatively, should I create 2 seperate views and then create a relationship on the OrderNumber within PowerBI (I want to have a drill through to the detail report.

Thanks in advance for any advice. 

Cheers,

Clay

[TomMartens · Accepted solution]

Hey @clay_75 ,

as always, it depends, unfortunately. The people from SQL BI have written an article about data modeling regarding the header/detail challenge: https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/

Nevertheless, you can create a single view in the data source that allocates the "measures" from the header table to the details, e.g., divididing the header measure by the number of products/per order. You also can consider to change default aggregation function SUM of the header measue to AVERAGE, if this makes sense.

Of course, you have to consider if this makes sense when you filter products and only one product remains in the detail table.

You can also consider both tables as different fact tables with a different dimensionality, one table is only filtered  by a single dimension table - order. The second fact table is filtered by the dimension tables order and product.

Maybe, this helps to make your decisions.

Regards,
Tom

[clay_75]

Thanks Tom, I'll do some tests. I think I'm leaning towards 2 separate fact tables. 

Cheers, Clay