How to model such that a drill through for Order suppliers having M:1 relation with Order fact table can be configured?

Question

I have product dimension, date dimension, Order fact.

There is 1:M relation between dimension and fact.

This is a simple star schema.

In Order fact the OrderNumber column is unique.

I want to introduce a new table into the model called Order suppliers. This table has OrderNumber, Supplier name Supplier location, Supplier code. In this table one OrderNumber can have multiple rows (suppliers).

How best to add this table into the model such that user can drill through from the Order fact visual to the page showing Order supplier details.

Should I extract and make a common dimension containing Order number that connects to Order and Order Supplier table?

Answer 1

Then I think you can keep dim Orders as a dimension that will connect fact Orders and Orders Supplier, or do this modification:

The fact table will be on the one side, and OrderSupplier on the many side, which will allow to drill-through by ordernumber to see suppliers for each order.

Answer 2

First, I think you should add also the ProductID in the OrderSupplier table and also connect it to dim Product. After this is done, you should create something like this model:

If you do not have productID, you will get returned a combination of Product and Supplier (if you have 2 products and 2 suppliers in one complete order, you will get 4 rows - product A with suppl A, product A with suppl B, product B with suppl A, and product B with suppl B). I hope this helps, I tried with sample data. I can send you the file and dataset if you want.