Database Lookup is used to map the columns of source and destination and get the surrogate key of lookup tables.
For example,
This can be achieved in Pentaho, create a transformation as shown below:
For example,
- My source table (dbo.Employee) contains employee details including DepartmentID
- My destination table (DimEmployee) including a column DepartmentKey.
- DepartmentKey is the SurrogateKey in the table dbo.DimDepartment with the following columns: (DepartmentKey, DepartmentID, DepartmentName,...).
- Now if you want to load the department key into Dimemployee table, we need to map the DepartmentID of the source with DepartmentID of the DimDepartment table (lookup table) to get the DepartmentKey as output.
This can be achieved in Pentaho, create a transformation as shown below:
Image is not available.
ReplyDeleteHi Mahadevan,
ReplyDeleteIn case there is large amount of data available.. Database lookup step performance will be degrade so in this case what will be the solution..??
Hi Mahadevan,
ReplyDeleteAs far as i have seen, the lookup only occurs for AND clause in joins like for example:
select * from employee left join department on e.empid = d.empid AND e.depid = d.depid.
But how can we implement using OR in a single lookup step?
Please help me with this.
Regards,
Nitish