Home > ETL Best Practices > Standardize Field Values – Use Translation Table or A Existing-VS-Standard-value Table W/ More Flexibility

Standardize Field Values – Use Translation Table or A Existing-VS-Standard-value Table W/ More Flexibility

I was initially using a very simple translation table initially to translate existing un-standardized values to standard values. This turns out to be too restrictive, and offers very little flexibility. Here is a typical translation table. I can only rely on two fields, SourceSystem and the FromValue to derive the ToValue. This works fine the standard value depends only on one existing value. When the relationship gets a little more complex than this, the translation table gets in the way.

image

A more flexible design is to have separate table for each set of standardized values. Here is an example.

image

The standard PC brand and model depends on the existing PC brand and model, and also the existing item type. An extra Exclude field can be also added to the table, so the standard values table can be also served as a filter for data that should be excluded from the ETL process.

Categories: ETL Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: