Wednesday, February 24, 2016

Source Qualifier Transformation In Informatica

Source Qualifier Transformation

Ø  The Source Qualifier transformation represents the rows that the Integration Service reads from the source when it runs a session.
Ø  A Source Qualifier transformation converts data types defined at source definition to Informatica compatible data types.
Ø  A SQ transformation generates a default Query for those source ports which were connected to its downstream transformation.
Ø  A SQ transformaton can 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 and override the generated deafult query.
Ø  Filter rows when the Integration Service reads source data. If you include a filter condition, the Integration Service adds a WHERE clause to the default query. 
Ø  Specify an outer join rather than the default inner join. If you include a user-defined join, the Integration Service replaces the join information specified by the metadata in the SQL query.
Ø  Source Qualifier Supports Normal/ Equi/Inner join,left outer join, Right outer join ,and full outer join, Self joins.
Ø  Specify sorted ports. If you specify a number for sorted ports, the Integration Service adds an ORDER BY clause to the default SQL query. Based on the number of sorted ports , the ports need to be moved to in that order from beginning of source qualifier transformation. If you define number of sorted ports as 2 , the data from the source will be ordered based on the first two ports under ports tab. By default the SQ does sorting on Acending order if you want to override this to Descending order change it in ORDER BY clause generated in Default Query of SQ.
Ø  Select only distinct values from the source. If you choose Select Distinct, the Integration Service adds a DISTINCT statement to the default SQL query.
Ø  Create a custom query to issue a special SELECT statement for the Integration Service to read source data. For example, you might use a custom query to perform aggregate calculations.
Ø  If the data types in the source definition and Source Qualifier transformation do not match(at least compatible), the Designer marks the mapping invalid when you save it.
Ø  You can override default query with new query but retain the same port order between the generated default query and new override query,
Ø  All the settings above can be customized only for Source Qualifier transformations which were connected  to Relational database source, you can't customize any properties for a non relational sources like flat file etc.
Ø  If condition is used at SQ and the override query is updated with a different condition the condition used at override Query takes prcedence over the setting defined outside SQ Query.

Ø  The Default / Override Query at Source Qualifier can be overrided again at Session properties.And this takes highest level of precedence

2 comments:

  1. Excellent post.Your steps clearly explains the informatica process.It will be useful for many people.Keep sharing like this.
    Regards,
    Informatica Training in Chennai | Informatica Training center chennai | Informatica courses in Chennai

    ReplyDelete
  2. This substance makes another trust and motivation within me. A debt of gratitude is in order for sharing article this way. The way you have expressed everything above is entirely amazing. Continue blogging this way.
    Informatica course in Chennai | Best Informatica Training in Chennai | Informatica Training center in Chennai

    ReplyDelete