how do i build my link table data model with multiple combination of keys?

[supriyabiware]

Hello All,

I have 4 facts and 3 dimensions in my data model

Facts - 1. SALES (Keys - Product ID, Supplier ID, Plant ID)

            2. PRODUCT (Keys - Product ID)

            3. REDUCTION YEAR (Keys - Product ID , Supplier ID)

            4. ENQUIRY (Keys - Product ID, Supplier ID)

Dimensions - 1. PRODUCT (Key - Product ID)

                      2. PLANT (Key - Plant ID)

                      3. SUPPLIER (Key - Supplier ID)

I tried linking FACTS using JOINS but somehow the results dint turn out to be fine. (Duplicates issue i think)

I cannot concatenate facts since they are huge and there are no common fields other than IDs

Now i am trying using LINK TABLE but am confused how can use the keys here. all the keys are necessary to be in LINK TABLE since i need information from all tables under one objects.

Thanks

Supriya.

[supriyabiware]

Hello All,

Can someone tell me the best way to model this dataset?

Facts - 1. SALES                          (Keys - Product ID, Supplier ID, Plant ID)

            2. REDUCTION YEAR     (Keys - Product ID , Supplier ID)

            3. ENQUIRY                     (Keys - Product ID, Supplier ID)

            4. PRODUCT                    (Keys - Product ID)

Dimensions - 1. PRODUCT      (Key - Product ID)

                      2. PLANT            (Key - Plant ID)

                      3. SUPPLIER      (Key - Supplier ID)

Thanks

Supriya

[Kushal_Chawda · Accepted solution]

Sales:

LOAD *,

           autonumber( [Product ID]&'|'&[Supplier ID]&'|'&[Plant ID]) as Key_Sales

FROM SALES;

REDUCTIONYEAR:

noconcatenate

LOAD *,

           autonumber( [Product ID]&'|'&[Supplier ID]) as Key_ProductYear

FROM REDUCTIONYEAR;

ENQUIRY:

noconcatenate

LOAD *,

           autonumber( [Product ID]&'|'&[Supplier ID]) as Key_Enquiry

FROM ENQUIRY;

PRODUCT:

noconcatenate

LOAD *

FROM PRODUCT;

LinTable_Temp:

LOAD distinct Key_Sales,

                      [Product ID],

                     [Supplier ID],

                      [Plant ID]

Resident  SALES;

drop fields [Product ID],[Supplier ID], [Plant ID] from SALES;

concatenate(LinTable_Temp)

LOAD distinct Key_ProductYear,

                      [Product ID],

                     [Supplier ID]

Resident  REDUCTIONYEAR;

drop fields [Product ID],[Supplier ID] from REDUCTIONYEAR;

concatenate(LinTable_Temp)

LOAD distinct Key_Enquiry,

                      [Product ID],

                     [Supplier ID]

Resident  ENQUIRY;

drop fields [Product ID],[Supplier ID] from ENQUIRY;

concatenate(LinTable_Temp)

LOAD distinct

                      [Product ID]

Resident  PRODUCT;

LinkTable:

noconcatenate

LOAD *

resident LinTable_Temp;

drop table LinTable_Temp;

Now you can load the dimension table as it is

[supriyabiware]

Thankyou Kushal for your reply.

Somehow i am not able to link all the three facts using this LINK TABLE. when i select Product ID it should link me to respective PLANT ID for that Product. instead there is  nothing.

Can i use Outer Join in LINK TABLE instead of concatenate? what do you think on this?

Also when i load dimensions as it is - and take dimensions and facts objects in straight table its taking lot of time to load like 10-15 mins.(Data from all facts would be 50 million rows approx)

Thanks

Supriya