Efficiency - advice on structuring data

[ruthhacche]

I am looking for some general advice.  I have a moderate sized model (about 20 tables).    Most tables link into the order table in a normal star schema but the order table then links down furth into the order line table.  So the order line table is my basic fact table.  Both the order table and the order line table hold the transaction date.  Sometimes I am reporting at the order level (with no reference to the detail in the order lines) but sometimes I am reporting at the order line level.

Where would you link the calendar date  - into the order table or into the order line table?  Or does it make no difference to efficiency which one I use?

[v-stephen-msft]

Hi @ruthhacche ,

Please kindly refer to:

https://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

It teaches you about Date Table Relationship to Multiple Fact Tables.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ruthhacche]

Does one run more efficently that the other - eg when manipulating pivots in an excel spreadsheet derived from the dataset?

[ruthhacche]

Thank you for responding but this does not answer my question.  I want to understand whether the calendar should link into the order table OR into the order line table for most efficient handling.  I totally understand the calendar cannot link actively into two tables at once.

[amitchandak]

@ruthhacche , depends. What Date is most needed? I would have preferred  the order header as most of the filter will coming on the order date. For any line-specific filter, I can use the line date itself.

[amitchandak]

@ruthhacche , Ideally in star schema both order header and lines should independent tables. You can merge columns in power query(what ever you need from another table), but on the model view, they do connect with each other.

As for the date table, you can not have two paths with order lines. one direct and one via order header. So one of the joins will become inactive or not work