cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ruthhacche
Helper II
Helper II

Efficiency - advice on structuring data

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?

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

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
Helper II
Helper II

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

ruthhacche
Helper II
Helper II

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.

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors