Bridge Table for Dimension

[heiko_schmidt]

Good morning, i have two tables: Orders and Invoice.

One order belongs to one invoice but both tables could have different supplier. So when i select one order i would love to get all the information like addresse, country etc. from supplier_order AND supplier_invoice.

Right now my data model looks like this:

 

But as i want to add some more attributes and i need to use hierarchy load for the supplier dimension, because one supplier can have a parent supplier (not shown in the diagram) i wonder if it is possible just to have one supplier dimension but i can´t figure out how to do that. Most of the time I am getting a circular reference and i don´t know how to build some kind of bridge table and don´t know if it is the best way to solve the problem.

Maybe the data model should look something like this?!?

Best regards

[gn_marvs1989 · Accepted solution]

For me, I will Concatenate the Order and Invoice Table into only 1 fact table and put a flag field to it (e.q. 'Order'/'Invoice' as Source).

Then rename both SupplierID key to be the same. 

With that, you will have only one key path to the supplier table.

on the report, If you need to check which supplier it is coming from, you can just use the Source Flag field to determine if it is from Order supplier of Invoice supplier.

[heiko_schmidt]

Hey, thank you for this advice. But what about the connection between orders and invoice?

I think when concatenating these two tables i don´t have a reference that links from my order to the invoice concerning this order?