topic Re: Dimensional Hierarchy & different fact levels in QlikView

Dimensional Hierarchy & different fact levels

dafnis14 — Mon, 21 Aug 2017 04:14:41 GMT

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!

Re: Dimensional Hierarchy & different fact levels

effinty2112 — Mon, 21 Aug 2017 09:32:36 GMT

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

Re: Dimensional Hierarchy & different fact levels

mayuresh_d — Mon, 21 Aug 2017 11:06:53 GMT

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.

Re: Dimensional Hierarchy & different fact levels

dafnis14 — Mon, 21 Aug 2017 14:18:14 GMT

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!

Re: Dimensional Hierarchy & different fact levels

effinty2112 — Mon, 21 Aug 2017 14:27:29 GMT

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

Re: Dimensional Hierarchy & different fact levels

dafnis14 — Mon, 21 Aug 2017 14:46:11 GMT

Hi Andrew,

This brings me back to the original issue of the synthetic key..

Maybe I misunderstood something 😞

Thanks..

Re: Dimensional Hierarchy & different fact levels

dafnis14 — Mon, 21 Aug 2017 14:54:33 GMT

Hi Mayuresh,

I tried what you offered... This brings me back to the original issue of the synthetic key..

Maybe I misunderstood something 😞

Thanks..

Re: Dimensional Hierarchy & different fact levels

effinty2112 — Mon, 21 Aug 2017 15:59:50 GMT

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

Re: Dimensional Hierarchy & different fact levels

dafnis14 — Mon, 21 Aug 2017 16:55:57 GMT

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!

Re: Dimensional Hierarchy & different fact levels

effinty2112 — Mon, 21 Aug 2017 17:39:58 GMT

Hi Dafnis,

Good of you to mark your own comment as helpful!

Re: Dimensional Hierarchy & different fact levels

dafnis14 — Tue, 22 Aug 2017 06:29:41 GMT

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 !

Re: Dimensional Hierarchy & different fact levels

dafnis14 — Wed, 23 Aug 2017 06:21:25 GMT

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!