Hello @khurram,
Thank You for posting your query into the Forum.
Firstly, I would like to say it doesn’t matter which type/kind of Join to follow if your data is completely designed in proper manner keeping in mind the “Star Schema”. All your records will by default will get matched and you will get the results that you want.
Now, there’s a lot to ponder around because as you said some of your tables contains “Many-to-Many” or “Many-to-One” relationships and also we’ve no idea how does your relationship flow from one table to another.
The fundamental or the best practice way is to have “One-to-Many” relationship between your Dimension Table and Fact Table (Waterfall Effect as said by Sam). There are lots and lots of videos of available on our education portal created by Sam where he guides us how to create, design and organize the data model. I sincerely recommend you to go through the course “Advanced Data Transformations & Modelling” which is exclusively created keeping in mind the “Best Data Modelling Practices”. In this course, he has also showed us whichever type/kind you of Join you select you’ll achieve the same results provided your data is designed correctly.
I’m also providing a few links from that section of the course regarding your query.
Hoping you find this useful.
Thanks & Warm Regards,
Harsh