Joining indirectly related business processes in a dimensional model

I did some searching around the forums, and nothing jumped out on how to handle the following scenario.

We have dimensional models for things like orders (fact at order line detail, multiple aggregations by dimensional keys to support business reports) and purchase orders (fact at po line detail, multiple aggregations by dimensional keys to support business reports). Typically, an order to the customer, can be fulfilled by a purchase order from a vendor. Our users request to be able to join these data sets (not just at an aggregate level).

If I would like to see orders for a customer, for a given time period, and see what vendor supplied the part (which would come from the purchase order fact - as the vendor dimension is related to this) would I:

1.) Create a PO/Order bridge, that has the surrogate keys from both facts, and use this to join the two
2.) Create a purchase order dimension, and put the PK from this dimension into the orders fact as an FK?
3.) Join the fact tables directly (not preferred)
4.) Other

For sake of conversation, lets assume both the orders fact (at line level) and the purchase order fact (at line level) are 50+ million records each. And for this exercise, we want to see transactional detail together, not purely just aggregates.

Thanks in advance!

Are you using serial numbers or lot tracking? Are purchase orders issued explicitly for a customer order? Do you receive goods against a customer order?

Unless you have some means to tie a receipt from a purchase order to a customer order, there is no mechanism that will reliably do it. You may as well simply record a vendor/part relationship (fact less fact table) with a time period and associate any sales order in that time period to that vendor. It's about a good a guess as anything else one could come up with.

Its in Oracle EBS, so in the flex fields in the ERP system they store order id (source system pk) as one of those flex fields... so all of the information is "there" its just a question of how to drill across the facts without doing a straight fact to fact join.

Its not clear which order has the flex field, but it doesn't really matter.

If the sales order carries the PO and all you care about is the vendor, the best thing to do is capture the vendor from the PO when you are loading the sales order (or fulfillment facts) as part of the ETL process and carry the vendor FK in the fact, then you don't need to combine the two facts.

if the PO receipt is carrying the sales order the goods were received for, carry the sales order as a dimension (degenerate) on the PO receipt facts.  You can then use sales order and item dimensions to combine the facts.

Thanks! Great idea!