Link table problem

[#1]

hi

i have a problem when i create a link table and connecting two facts and 3 dimensions i got duplicate rows.

i isolate the problem and find that one table cause the problem and i don't know why.

in the following example i took 2 dimensions ( item and calendar) and 1 fact table (Fact B)

in the link table i insert the Fact B key + item key + calendar key.

i have one order on 04.02.2014 and two order on 05.02.2014 ( total 3 rows)

but for some reason i got 3 rows for each day ( duplicate)  and i don't know why .

thank you for your help.

BTW : if i remove 'Receipt_Date' from link table and make a connection direct to calendar table it's work fine .

[#2 · Accepted solution]

Hello Sharon,

I think that your problem is your key between table Fact B & your link table.

Your key is the vendor, if you select a date or an item it's the associated vendor that filter your table fact B

You must to create a key include several fields : date + item with a function like autonumber / hash between table fact B and link table.

Replace your current key between Fact B and Link table (Main_Vendor_PO_Receipt_Key) with :

Autonumber(Main_Vendor_Item_number, Main_Vendor_Receipt_Date) as %Key_FactB

Table LinkTable :

Autonumber(Main_Vendor_Item_number, Main_Vendor_Receipt_Date) as %Key_FactB

Hope that it's help !

Laura

[#3]

thank you

it's work