What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

-- Download Lookups in SAP BODI/DS as PDF --

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

What do you think of this post?
  • Awesome 
  • Interesting 
  • Useful 

9 comments on “Lookups in SAP BODI/DS

    • Swathi,

      I have already given the example for lookup_ext. Examples are due from my side for lookup() & lookup_seq()… will have best practices for those functions in weeks time.


      • sooperb site.highly appreciated if updated ASAP,let me know how can i help you as am currently working but recently joined ,some hyperlinks are not accesble please check the site construction

Leave a Reply

Your email address will not be published. Required fields are marked *