SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Request explanation on differences in transformations in DI Studio

Reply
Occasional Contributor
Posts: 11

Request explanation on differences in transformations in DI Studio

[ Edited ]

what is difference between look up transformation and join transofrmation.???

 

Super User
Super User
Posts: 7,942

Re: Request explanation on differences in transformations in DI Studio

Posted in reply to Sharad_Pujari
Super User
Posts: 7,760

Re: DI Studio

Posted in reply to Sharad_Pujari

In a lookup transformation, the lookup tables are loaded into memory at the start, and the main table is processed sequentially.

In a join transformation, all tables need to be sorted (often done implicitly by a SQL step) and then merged along the by variables.

 

Joins should be done when the contributing tables are quite large, or there is a m:n relationship.

Lookups are good for smaller lookup tables (small just meaning they should fit into memory) and 1:n relationships (lookup to main); one can do several lookups in one sequential step.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,424

Re: Request explanation on differences in transformations in DI Studio

Posted in reply to Sharad_Pujari
There's a scenario where join should be preferred, when the master and lookup tabke(s) reside on an external data source. SQL can relatively easy be pushed down to the RDBMS.
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 424 views
  • 4 likes
  • 4 in conversation