The best way to make a unified dim table

[vtatarnikov]

Hello everyone.

I have 3 almost identical tables in the landing zone, these are small dimension tables different systems.

What is the best way to make one general dim table (hub without satellite)? Do it using 3 mappings that write in one table or make one custom sql-query?

How to store the same values with different keys? Create surrogate keys and lookup from fact tables?

[TimGarrod · Accepted solution]

If you are consolidating the 3 identical tables - you can either 

• Create a mapping for each source
  • Pro - CDC processing natively supported
  • Can use all 3 mappings in a single ETL set
• Create a UNION ALL query or view
  • No native CDC processing supported (from _CT tables)

Regarding this question - 

How to store the same values with different keys?

Do you need to consolidate the keys?   

If so - then you typically want a master set of items and an X-REf table.  The XRef being used as a lookup in the mapping process to convert to the consolidated set of keys.   

[vtatarnikov]

Hi Tim! Thanks for replay