Informatica About Source Qualifier joining and multiple targets in one mapping

Question

Hello dear stackoverflow wizards,

Attached is a screenshot of the mapping in question. The Source Tables come from the same database. I tried to join the tables using a single Source Qualifier transformation. I also put all three targets in one mapping.

Would this work? If not, is it possible to use the Source Qualifier to join in a different case and is it possible to have multiple targets in one mapping at all?

My instructor gave me 0 points, I should have used a joiner and three seperate mappings (was not instructed to and not taught about joiners prior. Very basic course).

Thank you very much!

Best wishes, Cam The Mapping in question

Answer 1

It shouldn’t work as Data Integrity may fail. There are Foreign keys between these tables. First you need to upload data into Households table, then to Customers and after that to Orders.

It’s good to create three separate mappings and connect them sequentially in Workflow Manager. You can also create three separate data loads in one mapping, but you need to define Target Load Group Order (Click Mappings in Toolbar) to specify in which order Integration Service (IS) should load them. And, actually, you don't need to join anything.

I don’t see any issues using Source Qualifier to join tables, even filtering or ordering data there, but usually good practice is to use Joiners after SQ (or SQ >> FILTER >> JOINER). With Joiner you can join only two sources at a time.

Here is what Informatica says about Joiner Transformation:
Use the Joiner transformation to join source data from two related heterogeneous sources residing in different locations or file systems. You can also join data from the same source.

And about Source Qualifier:
Join data originating from the same source database. You can join two or more tables with primary key-foreign key relationships by linking the sources to one Source Qualifier transformation.

Joining Source Data
Use one Source Qualifier transformation to join data from multiple relational tables. These tables must be accessible from the same instance or database server. When a mapping uses related relational sources, you can join both sources in one Source Qualifier transformation. During the session, the source database performs the join before passing data to the Integration Service. This can increase performance when source tables are indexed. Tip: Use the Joiner transformation for heterogeneous sources and to join flat files.