SSAS -> New cube contains two fact tables. How to relate these so I can use measures from both at the same time

facttablessas

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.

Best Answer

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.

Related Question