topic Re: Request explanation on differences in transformations in DI Studio in SAS Data Management

Request explanation on differences in transformations in DI Studio

Sharad_Pujari — Wed, 02 Aug 2017 11:59:02 GMT

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

Re: Request explanation on differences in transformations in DI Studio

RW9 — Wed, 02 Aug 2017 12:00:20 GMT

The manual is generally the first place for information:
http://support.sas.com/documentation/cdl/en/etlug/65016/HTML/default/viewer.htm#n0geesvp950nken1tetx8ffwoxkt.htm

Re: DI Studio

Kurt_Bremser — Wed, 02 Aug 2017 12:03:26 GMT

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.

Re: Request explanation on differences in transformations in DI Studio

LinusH — Wed, 02 Aug 2017 19:30:03 GMT

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.