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?
Re: Joining Multiple Keys to Dimension in DataModel
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.