You can use the Lookup transformation to perform many tasks, including:

♦ Get a related value. For example, your source includes employee ID, but you want to

include the employee name in your target table to make your summary data easier to read.

♦ Perform a calculation. Many normalized tables include values used in a calculation, such as gross sales per invoice or sales tax, but not the calculated value (such as net sales).

♦ Update slowly changing dimension tables. You can use a Lookup transformation to

determine whether rows already exist in the target.

as explained previously u can use it for to get related value,perform calculations and in scds the thing is u can mainly use it...even no need of concerned tables grab into the workspace...

  Look Up Transformation is generally used when a fixed data is not present in the mappings we use but is required in the ware house or look up is more importantly used to compare the values...

ex1) in the transactional data we have only name  and custid .... but the complete name (with first and last is required by the biz user..) and there is a separate table (either in source or target data base) that has the first n last names in it.

ex2) u need to compare the prices of the existing goods with its previous prices (referred as type3 ) a look up table containing the olap data could be handy

You need Lookup Transformations because Dimension Tables are linked to Fact tables by Surrogate Keys.  

The Surrogate Key is automatically generated within the warehouse, and the ONLY way to find the surrogate key is to look up a Dimension table using the Natural (or Business Key).  

The surrogate key value of the Dimension is then stored against the Fact table to complete the Foreign Key relationship from Fact to Dimension.

Eg.   SALESMAN_DIMENSION

 SALESMAN_ID               Number  not null                  (Surrogate / Primary Key)
SALESMAN_CODE           Varchar2(10)         not null  (Business Key)
SALESMAN_NAME           Varchar2(30)
...Other attributes

Incoming sales records may be recorded against salesman AF293F, and you must perform a lookup using SALESMAN_CODE to find the primary key identifier SALESMAN_ID.  This sequence number is stored against the Fact table, which may identify for example, a single sale completed by this person.

Surrogate keys, keep the warehouse independent from the source systems (which can and will change), and minimise row length on large fact tables.  (eg. 10 million sales records with a 10 character Salesman Code would be use considerably more space than a single 2 byte numeric field).