Lookup tables to Dimension

Hi,
Currently we have about 200 tables out of which 75 are lookup tables. How should we take these lookup tables to DWH environment?
Should we create 75 lookups to one junk dimension are how to do ?
-RBS

Not quite sure what your lookup tables store, but a typical approach is to perform the lookup during ETL and store the retrieved value/description as an attribute in the relevant dimension table.

it really depends. Some will be incorporated into conformed dimensions while others may be combined into junk dimensions or as stand alone dimensions if warranted. There are a lot of choices.

We use lookup tables (reference tables) in several ways – foremost, to determine data conformity among the attributes’ possible values during modeling. They are then used in the ETL process to enforce data quality – “if value doesn’t exist flag as error” (This helps a LOT with legacy systems that haven’t been well maintained). Then, in most cases, we collect the decode value and put it in with the dimension so we have the “code” and “decode” value in the dimension. (There is a distinct lookup during the dimension build for each code/decode fields.)

There have been some instances too where the lookup table is pivoted then populated from the source OR filled with all permutations (Boolean values, y/n or 1/0) then used as a lookup. The “all permutations method” reduces the volume of ETLs because all possible values exist (no need to run an ETL to populate the dimension). Refreshes on the dimension are triggered during change data capture. This method is most often associated with changing the source’s granularity - to reduce bridge tables.

Sooooooo many choices!

Hope this helps.

Thanks for the quick reply.

The One True Lookup table is an anti-pattern in transactional AND analytic databases.