Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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