Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
heiko_schmidt
Contributor III
Contributor III

Bridge Table for Dimension

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:

 Visio1.png

 

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?!?

Visio2.png

Best regards

1 Solution

Accepted Solutions
gn_marvs1989
Contributor III
Contributor III

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.

  

View solution in original post

2 Replies
gn_marvs1989
Contributor III
Contributor III

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
Contributor III
Contributor III
Author

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?