Lookup tables to Dimension

Go down

Lookup tables to Dimension Empty 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

Back to top Go down

Lookup tables to Dimension Empty 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

Back to top Go down

Lookup tables to Dimension Empty 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Lookup tables to Dimension Empty 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.
KS_EDW
KS_EDW

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

Back to top Go down

Lookup tables to Dimension Empty 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

Back to top Go down

Lookup tables to Dimension Empty 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.
Skipjacker
Skipjacker

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

Back to top Go down

Lookup tables to Dimension Empty Re: Lookup tables to Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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