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,401

Re: Request explanation on differences in transformations in DI Studio

Super User
Posts: 6,938

Re: DI Studio

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,257

Re: Request explanation on differences in transformations in DI Studio

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
  • 363 views
  • 4 likes
  • 4 in conversation