What is a Lookup table?
In DW terminology, Lookup is the common practice in ETL operations to get the appropriate value from a lookup table/referenced data set.
Precisely, If appropriate data is not available in source table which might be available in another reference table. ETL operations will look in to the lookup/reference tables based on some key values to get the appropriate information from the lookup tables.
For instance, I have a dimension called Dim_Customer (Cust_SK, Customer Id, Customer Name, Address1, Address2, etc., and Product Id) are some of the fields. This tables holds the information of a customers and in addition, a Product Id which tells you what is the product that customer is associated with. All the Products information will be available in a Dim_Products(Prod_SK, Product Id, Product Name, Price, Quantity, etc.,) dimension. Now I want to load the customer information along with what all the products that he bought in to fact table called (Cust_Prod_Fact).
When do we go for Lookup?
In the above example my source table is Dim_Customers and my lookup table is Dim_Product. Here I can perform lookup operations to get the information about the products.
In any database, we can achieve the same operation by joining two/more tables.
Lookup is primary component in any ETL Tool, it can be found in either Transformations section or Predefined functions section. In SAP BODS lookup is a specialized function, we have 3 different types of lookup functions.
Translate Table: Here lookup table also called as Translate table.
lookup() : Briefly, It returns single value based on single condition
lookup_ext(): It returns multiple values based on single/multiple condition(s)
lookup_seq(): It returns multiple values based on sequence number