Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi Dafnis,
try:
Left Join(Return_Items) // to associate Customer_Deliveries dimensional table with records in Return_Items
LOAD
Distinct
Key_Delivery_Offer_Item,
DeliveryID
Resident Delivery_Offer_Item;
Concatenate(Delivery_Offer_Item)
LOAD * Resident Return_Items;
DROP Table Return_Items;
In this concatenated table you will lose associations between fields that you have in the original data model. If you really want to combine into one table maybe a join would work better.
Left Join(Delivery_Offer_Item)
LOAD * Resident Return_Items;
DROP Table Return_Items;
Good luck
Andrew
bring deliveryId in return_item
return_item:
load *,
Key_Delivery_Offer_Item
from
..................;
left join
load distinct
Key_Delivery_Offer_Item,
DeliveryID,
from
...................Delivery_Offer_Item.qvd;
thn
concatenate return Item and delivery_offer_item table.
and then deliveryId from customer deliveries will automatically associate.
see if it works for you
thanks.
Hi Andrew & Mayuresh,
From what you both suggest, I understand that it is not possible to maintain the dimension table as it is.
I don't understand why..
Could you please elaborate?
Why isn't it possible to concatenate the fact tables?
Thank you!
Hi Dafnis,
You can concatenate the fact tables instead of joining. As Mayuresh says at the end of his post "see if it works for you".
Try concatenation and try joining, do both and compare results when making selections.
Cheers
Andrew
Hi Andrew,
This brings me back to the original issue of the synthetic key..
Maybe I misunderstood something 😞
Thanks..
Hi Mayuresh,
I tried what you offered... This brings me back to the original issue of the synthetic key..
Maybe I misunderstood something 😞
Thanks..
Hi Dafnis,
Left Join(Return_Items)
LOAD
Distinct
Key_Delivery_Offer_Item,
DeliveryID
Resident Delivery_Offer_Item;
Concatenate(Delivery_Offer_Item)
LOAD * Resident Return_Items;
DROP Table Return_Items;
gives this data model with no synthetic key:
Cheers
Andrew
Hi Andrew,
Yes, that's a compromise, because the dimension table turns into a fact table..
If there would be no other solution I wii go for it ..
thanks!
Hi Dafnis,
Good of you to mark your own comment as helpful!