How to avoid Synthetic keys and Create mapping between 2 different tables

[richard24best]

Hi Team,

I have 2 tables - 

1 )GFXSettAnalysis (Summary table)

2) GFX Settled (Detailed table)

Requirement is that i want to create filters using the fields shown in Synthetic table so that users when filter they can see Summary view and in Detailed view. Is this possible ?

Not sure how do i map these fields, please assist.

Regards,

Richard

[Mark_Little]

HI @richard24best 

Would help if you could share you fields names, Getting a synthetic key, means you have fields in both tables with the same name, if the contain the same data then you can remove them from one table or if they have the same name but different data then you want to alias in one table. i.e.

Field AS GFXSettled_Field,

[richard24best]

Thanks for your response Mark.

yes the field names are same, should i comment in one of the table to avoid Synthetic keys?

Regards,

Richard

[marcus_sommer · Accepted solution]

In many cases it's not expedient to keep multiple fact-tables within the data-model because it could cause such issues with synthetic keys and/or circular references (by further connecting them to dimension-tables). Further the relationship between the tables and/or missing key-values on one or even both sides could make serious problems. More disadvantages occur with multiple different fields for the same information which leads to a lot of efforts to address them properly.

Therefore I suggest you follows the official recommendations to create a star-scheme data-model. This means merging all facts into a single table and in your case just concatenating both facts together. By adding an extra source-field you will be further able to differentiate between the different tables.

- Marcus