Synonyms [Syn]

Example

New customers are asked about the name of their previous provider. They can choose to select a value from the combo-box, or they can enter a new value. What happens is that different provider names exist in the source like zigo, ziggo, Zigg, Ziggo geloof ik, Upc, etc.

We want to map all theses different names to one single provider named Ziggo.

Solution

When the data gets normalized in level 1 of DWH we get a table called Brand, that contains all different provider names: e.g.

10 Zigo
11 Ziggo
12 Zigg
13 Ziggo geloof ik

The ETL automatically adds a column named synonym_id to each lookup table. synonym_id defines a parent-child relation. We have to fill this column once manually (or using some fussy logic query).

id
brand_name
synonym_id
10 Zigo 11
11 Ziggo
12 Zigg 11
 13  Ziggo geloof ik 11

 

When synonym_id is filled then all lookups must use synonym_id instead of the primary key. (.e.g. by using this tsql:

select isnull(synonym_id, brand_id) brand_id
from dbo.Brand

 

The table work_order will look something like this (brand_name comes from staging, brand_id is looked up).

id
register_dt
brand_id
 brand_name
100 2015-03-01 11  Zigg
101 2015-02-14 11 Ziggo

 

[Syn-1] Foreign key check

We have added a check is usp_historize to prevent the situation where synonym_id refers to a non existing or deleted record.
This check is done for every update of a historic table that contains synonym_id. We could improve this ETL by only performing this check when records are deleted.