Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks.

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
    
Thanks Gonsalu. –  User Smith May 25 '12 at 20:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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