Lookup tables to Dimension

View previous topic View next topic Go down

Lookup tables to Dimension

Post  rbs100 on Thu Sep 15, 2011 8:07 am

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

rbs100

Posts : 12
Join date : 2011-09-14

View user profile

Back to top Go down

Re: Lookup tables to Dimension

Post  VHF on Thu Sep 15, 2011 11:30 am

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Lookup tables to Dimension

Post  ngalemmo on Thu Sep 15, 2011 4:03 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Lookup tables to Dimension

Post  KS_EDW on Thu Sep 15, 2011 4:41 pm

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.
avatar
KS_EDW

Posts : 20
Join date : 2011-09-07
Age : 42
Location : Kansas

View user profile

Back to top Go down

Lookup tables

Post  rbs100 on Sat Sep 17, 2011 10:06 am

Thanks for the quick reply.

rbs100

Posts : 12
Join date : 2011-09-14

View user profile

Back to top Go down

I believe we'd agree that

Post  Skipjacker on Wed May 02, 2012 5:57 pm

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

Posts : 16
Join date : 2010-11-10
Age : 48
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Lookup tables to Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum