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.



You're focusing on facts and forgetting about dimensions.

Years of service is a measurable attribute of a Driver, so, you should have a Driver dimension associated with the FactDeliveryDriver fact.

driverSid is a surrogate key for the Driver dimension, and not for the fact table. For the fact table it's a lookup column.

Also, deliveries are (probably) made by a Driver, so you should have the Driver dimension associated with the FactDeliveries fact as well.

There should also exist a Delivery dimension, that is associated with the FactDeliveries.

A simple schema should be something like:

Driver(sk_driver, name)
Delivery(sk_delivery, address, date_delivered)
FactDeliveryDriver(fk_driver, val_years_of_service)
FactDeliveries(fk_driver, fk_delivery, val_miles)

Since the Driver dimension is common to both fact tables, you can use the Driver dimension to relate measures from both of them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.