JOINER Transformation

JOINER Transformation

1. Overview

   • Transformation properties - location of cache directory,
     • Data Cache
     • Index cache
   • Join Condition
     • use source with fewer rows as Master
     • for sorted Joiner - use source with less dups as Master
   • Join Type - Normal (faster) Master Outer (faster), Detail Outer, Full Outer
     • Normal (faster) - discard all rows from master and detail that do not match
     • Master Outer (faster) - keep all rows from detail and the matching rows from master, discards unmatched master rows
     • Detail Outer - keeps all rows from master and matching rows from detail, discards unmatched detail rows
     • Full Outer - keeps all rows from detail and master
     • Joining data from a Single Source
       • you want to perform calculation on part of the data and join the transformed data with the original data.
         • Joining data from a Single Source - two branches of the same pipeline, two instances of the same source
           • Two branches of a pipeline - when you have a large source or can read source only once, like from a message queue; or when you use sorted data
           • Two instances of a source - need to add a blocking transformation to the pipeline between source and Joiner; or if one pipleine processes slower than another; or need to join unsorted data
   • Transaction Scope Property - apply transformation logic to all data in transaction (Transaction), all incoming data (All Input), or one row of data at a time (Row)
     • Drop or preserve transaction bounderies based on mapping configuration and transformation scope.
       • Preserve Transaction Boundaries - join two branches of the same source pipeline or join two sources and you want to preserve transaction boundaries for the detail source
       • Drop Transaction Boundaries - join two sources ort wo branches and you want to drop transaction boundaries. Use All Input scope to apply to all incoming data and drop boundaries for both pipleines.
       • Summary
         • Row - Unsorted - Preserve Transaction boundaries in the detail pipeline
         • Row - Sorted - Session fails
         • Transaction - Sorted - Preserves when master nad detail from the same transaction generator; otherwise it fails
         • Transaction - Unsorted - session Fails
         • All Input - Sorted, Unsorted - Drops transaction boundaries
   • Sorted Input
     • Configure session for sorted or unsorted input; maintain sorted data in mapping
     • sorted flat files, sorted relational data in SQ transformation, use Sorter transformation
     • Can't place these between sort origin and Joiner: Custom, Unsorted Aggregator, Normalizer, Rank, Union, XML Parser, XML Generator, mapplet with the following transformation
     • Blocking with Unsorted Joiner - IS reads master rows before details rows. It blocks the detail source while it caches master rows.
     • Blocking with Sorted Joiner - blocks data based on mapping configuration and if master and detail come from different sources.
     • Integration Service reads rows from both sources concurrently and builds the index and data cache based on the master rows. The Integration Service then performs the join based on the detail source data and the cache data. The number of rows the Integration Service stores in the cache depends on the partition type, the source data, and whether you configure the Joiner transformation for sorted input.

2. Master and Detail

   • join source data from two related heterogeneous sources or from the same source
   • Master pipeline ends at Joiner, detail continues to target
   • ports from first source are Detail pipeline, ports from second source are Master pipeline, the designation can be changed clicking M in the port Tab

3. ACTIVE

4. limitations

   • null values are not matched, replace null with default before joining
   • can't use Joiner with Update Strategy or Sequence generator used before Joiner
   • Char(40)="abcd" and Varchar(40) = "abcd" are not the same

Imagens e arquivos do mapa