How to Avoid The Usage of SQL Overrides in Informatica PowerCenter Mappings

resource link:http://www.disoln.org/2014/03/How-to-Avoid-The-Usage-of-SQL-Overrides-in-Informatica-PowerCenter-Mappings.html

Many Informatica PowerCenter developers tend to use SQL Override during mapping development. Developers finds it easy and more productive to use SQL Override. At the same time ETL Architects do not like SQL Overrides as it hide the ETL logic from metadata manager. In this article lets see the options available to avoid SQL Override in different transformations.

What is SQL Override

Transformations such as Source Qualifier and LookUp provides an option to override the default query generated by PowerCenter. You can enter any valid SQL statement supported by the underlying database. You can enter your own SELECT statement with a list of columns in the SELECT clause of the SQL, which is matching with the transformation ports. The SQL can perform aggregate calculations, or call a stored procedure or stored function to read the data.

Source Qualifier Options to Avoid SQL Override

There are few options available in source qualifier to avoid the usage of SQL Override. These can be effectively used to avoid the usage of SQL override.

1. User Defined Join

User defined join option provides the most flexible options to avoid the usage of SQL Override. You need to enter only the contents of the WHERE clause of your SQL, not the entire query in user defined join option.

If the JOIN Syntax of your query is entirely with in the WHERE clause, you can directly enter the WHERE clause of your query into the user defined join option, with out any modification. Oracle still supports the old way of join using(+), which is with in the WHERE clause. Where as most of the other databases uses the latest JOIN syntax, which uses the JOIN syntax in the FROM clause.

Below image shows the left outer join between CUSTOMER table and PURCHASES table. This join uses the Oracle Join syntax (+).

Note :- You can not use the above option, if the JOIN Syntax of your query is with in the FROM clause.

Informatica Join Syntax

If the JOIN Syntax of your query is written with in the FROM clause, you should use the Informatica Join Syntax in the user defined join option. When you use the Informatica join syntax, the Integration Service insert the join syntax in the WHERE clause or the FROM clause of the query, depending on the underlying database syntax.

 

Informatica Join supports, Normal, Left Outer and Right Outer Joins and here is the join syntax.

 

• Normal Join :-  { source1 INNER JOIN source2 on join_condition }
• Left Outer Join :- { source1 LEFT OUTER JOIN source2 on join_condition }
• Right Outer Join :- { source1 RIGHT OUTER JOIN source2 on join_condition }

 

 

Note :-  Enclose Informatica join syntax in braces { }. 

Above shown image is displaying the Informatica Join Syntax. Using the user defined join option, CUSTOMER table is left outer joined with PURCHASES table as shown in the above image.

2. Source Filter

Source filter option can be used to adjust the ‘WHERE’ clause of the SQL created by the integration service, with out using the SQL Override option. You can enter a source filter to reduce the number of rows the Integration Service queries. You can provide the source filter condition with out giving the string ‘WHERE’. 

Source filter option is used to filter source data based on the Customer ID.

3. Sorted Ports

Using the sorted ports option, you can sort the source data. When using sorted port option, Integration Service adds the ports to the ORDER BY clause in the default query. The Integration Service adds the configured number of ports, starting at the top of the Source Qualifier transformation. The sorted ports are applied on the connected ports rather than the ports that start at the top of the Source Qualifier transformation.[1.集成服务根据配置的Number of sorted ports对Source Qualifier 组件自上而下的生成order by 条件；如A B C D四个字段，Number of sorted ports为2 生成的order by为 order by A,B。 2.sorted ports只应用在连接的字段上，如A B C D四个字段，Number of sorted ports为2，但是A字段没有向后连接，生成的order by 为order by B,C。]

Based on the setting above, source data is sorted on the first two connected ports from the source qualifier to the downstream transformations. The data is sourced in the ascending order.

4. Select Distinct

 

If you want the Integration Service to select unique values from a source, use the Select Distinct option. Using Select Distinct filters out unnecessary data earlier in the data flow, which might improve performance. 

'Select Distinct' option can be set in source qualifier as shown in the above image.

Advantages and Limitations of SQL Override

Pros

• Utilize database optimizers techniques such as indexes, hints. 
• Can accommodate complex queries.

Cons 

• Lose transformation logic in metadata searched. 
• Unable to utilize Partitioning or Pushdown Optimization options.
• Processing impacts database resources. 

Hope you enjoyed this article. Feel free to ask any further questions or clarification you may have below in the comment section. We are happy to help you with.