I am working on a new cube. I know that you cannot directly create a relationship between two fact tables (and expect good results).
I am just wondering how to go about doing this I have read around and looked at examples, but I am just having difficulties wrapping my head around how this works.
My example would be these two fact tables.
Fact Table A:
FactDeliveryDriver (example measure: years of service)
Fact Table B:
FactDeliveries (example measure: miles)
Each of these fact tables contain measures specific to them. I am required to have these two both in the same cube. So I have a primary surrogate key in factdeliverydriver called driverSid and a primary surrogate key in factdeliveries called deliverySid. I also have the driverSid in factdeliveries even though I know I cannot directly link them.
I am guessing that I need some sort of bridge table. Again, I have read up on these but haven't really had much luck finding anything that shows how this would work with two fact tables. Maybe I have been looking at this for too long.
Any direction/advice is greatly appreciated.