Dimensional Hierarchy & different fact levels

[dafnis14]

Hi,

In the attached model I have a dimensional hierarchy in a flat table (Delivery_Offer_Item).

Delivery > Offer > Item

the 2 facts tables are linked to it with different fields and to different levels.

The request is to be able to display a delivered or returned item per customer.

I would like to concatenate the 2 fact tables but i receive a synthetic key.

My question is which is the right way to handle it.

Should I outer join the items in the dimension to Customer_Deliveries ?

In reality, the The Customer Deliveries have tens of millions of records of deliveries only.

I would appreciate very  much your help here.

Thanks!

[dafnis14]

Hi Andrew,

oops.. didn't notice 😞

I won't be able to find the solution requested, I will mark it as correct 🙂

Thanks a lot for your kind help !

[dafnis14]

Dear Andrew,

I tried your solution, but the problem is that it probably doesn't take into account the need to show the returned items by a customer.

So I ended up with another synthetic key..

Any idea how to resolve this?

Thank you!