Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel free not to assume so in your answer if it makes sense). For instance, maybe this main table is a list of people and one of the columns is for favorite_color. Of course then our lookup table would looks something like this:

color_cd    color_name
0           Unknown
1           Red
2           Blue
3           Orange
4           Other

Now if I'm creating reports from this main table or perhaps feeding this data into some software for domain experts to use, I'd rather replace the favorite_color code with the color_name from the lookup table. One solution of course is to do this with a simple JOIN in my SELECT:

SELECT main.col1, main.col2, colors.color_name as `Favorite Color` 
  FROM main LEFT OUTER JOIN colors ON (main.favorite_color = colors.color_cd);

This gets the job done. Now suppose that the main table has dozens or maybe hundreds of such columns and millions of rows. Some of the lookup tables are small, like the above example, but others might have hundreds or thousands of values.

Is there any sort of standard way of doing this transformation, or is the writing a long with dozens or hundreds of joins the way to go? Is this type of thing a typical component of any ETL product? Is it preferable to write a script that handles this without joins and outside of SQL? Please forgive what are likely some abuses or misuses of terminology; I'm not even sure if the above process has a name.

share|improve this question
    
What RDBMS are you using? –  FreshPhilOfSO Jan 21 '13 at 16:14
    
For the moment I'm using MySQL, but I'm hoping it's not database-specific as I wanted a more general solution. –  Michael McGowan Jan 21 '13 at 16:20
    
Is the join you are talking about in the data warehouse? Or in the source that feeds the warehouse? –  Chris Aldrich Jan 21 '13 at 17:11
    
@ChrisAldrich Maybe I applied the term "data warehouse" incorrectly; I don't know and I'm not a DBA by trade. I'm trying to talk about a generic situation where this main table exists somewhere but we don't want to look at it with those yucky codes. Surely this thing happens all the time, so how is it typically handled? –  Michael McGowan Jan 21 '13 at 17:23
    
Well, if the code almost never change, you could always use a view with case statements that convert the codes to presentable values as one thought. –  Chris Aldrich Jan 21 '13 at 19:53
show 1 more comment

1 Answer

up vote 2 down vote accepted

In the ETL process, you can replace the codes with names while loading a target table. I'll focus on Informatica PowerCenter but I'm sure other ETL tools offer a similar feature.

There is a Lookup transformation that is used to look up values (DNAME) from a a relational table (it may also be a view or a flat file), based on a defined lookup match criteria (source.DEPTNO = lookup.DEPTNO). These values may then be appended to the source rows and stored in a target table (that is used for reporting).

Lookup transformation

When a session is executed, a SELECT statement is generated for each lookup in the mapping. These statements are run against the data source once and the results are stored in the lookup cache. Later, when a value needs to be looked up, the transformation uses the cache.

share|improve this answer
    
Thanks. Is "lookup procedure" the standard name for this operation? I haven't found this exact functionality yet, but my experience is pretty much limited to Talend/Jaspersoft ETL. There this can be faked with their components for joins, but your approach seems more natural if available. –  Michael McGowan Jan 22 '13 at 14:11
    
@MichaelMcGowan Yes, this is how it's called in PowerCenter. Other ETL tools may use different names. –  Marek Grzenkowicz Jan 22 '13 at 15:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.