Joining Multiple Keys to Dimension in DataModel

[polisetti]

Hi Everyone,

I have  2 fact tables A,B. One table has Customer ID, Account ID and other columns. Second table has only customer ID and other columns.Both were concatenated. Third table is my Customer dimension with which i want to join my fact table.Dimension table has Customer ID, Account ID and other columns.
Now, my question is
I want to join my fact table A with dimension table on a combination of Customer ID + Account ID as primary Key. And for table B join my fact with dimension table on Customer ID.
I'm unable to join as it is leading to the synthetic key. I tried to load dimension table twice and join separately. But entire columns in both dimension tables needs to aliased to avoid synthetic keys which is not my requirement.
How to avoid synthetic keys and join properly?
Could anyone help me on this?

[sujeetsingh]

Jaswanath ,

Can you post the graphical image of your model. It will help to understand better.

Well you have one thing have two keys one concatenated one CustID+AccountID for table A and Cust ID for table B.

Butter there can be many issues like circular loop and synthetic keys. You need to avoid them. Well I will recommend two table fact A and fact B both are not connected. Use dimension table to connect them hence from A to dimension table it is combined key and for dimension table to fact table A it is cust ID.

[ahaahaaha]

Hi,

This is a small example code of merging two fields ID1 and ID2 into a common key.

//create source tables.

Table1:

LOAD

RecNo() as ID1,

RecNo()+1 as ID2,

Div(Rand()*1000, 1) as Value11,

Chr(RecNo()+70) as Value12

AutoGenerate 50;

//Add a key field to Table1

Left Join

LOAD*,

ID1&'|'&ID2 as Key

Resident Table1;

Table2:

LOAD

RecNo()+10 as ID1,

RecNo()+11 as ID2,

Div(Rand()*1000, 1) as Value21,

Chr(RecNo()+80) as Value22

AutoGenerate 50;

//Add a key field to Table2

Left Join

LOAD*,

ID1&'|'&ID2 as Key

Resident Table2;

//Remove the synthetic key. Create key table

KeyTable:

Load Distinct

Key,

Key as TempKey,

ID1,

ID2

Resident Table1;

Concatenate (Table1)

Load Distinct

Key,

ID1,

ID2

Resident Table2

Where Not Exists(TempKey, Key);

Drop Field ID1, ID2, TempKey;

You can do not delete fields ID1 and ID2, if their further use is expected.

Regards,

Andrey