You might use REG_EXTRACT in an expression to extract middle names from a regular expression that matches first name, middle name, and last name. For example, I have an input port for full_name and I want to split and assign the full name as First, Middle & last name. Let’s identify the regular expression for… Continue reading How to extract middle name in Informatica Power center
Category: Informatica PowerCenter
How to extract first & last name in Informatica Power center
How to split first & last name from a full name In informatica Power center? There are few ways to do this but the recommended and the easy way is to use the function REG_EXTRACT. This function Extracts subpatterns of a regular expression within an input value. Let’s see an example now, I have an… Continue reading How to extract first & last name in Informatica Power center
How to calculate Age in informatica power center
How to calculate the Age in informatica power center. You can use the function DATE_DIFF. We normally calculate the age by substracting the current date from Date of Birth. In my case I have date of birth is available in the source file. I have created a port in an expression transformation and calculate the… Continue reading How to calculate Age in informatica power center
Decode function with Regular expression pattern matching in informatica PowerCenter
This post will help you to understand how to match a regular expression pattern in Decode function in informatica PowerCenter. If the requirement is to search for a pattern and assign the values accordingly, you can configure your DECODE function along with REG_MATCH to match with a pattern. In my case if any plan status… Continue reading Decode function with Regular expression pattern matching in informatica PowerCenter
How to remove the hash symbol from the flat file header in PowerCenter
How to remove the hash symbol from the flat file header in PowerCenter? You need add a customer property RemoveOutputHeaderHash in the session level. If you are using Header Options to create header columns in a flat file, you may end up with a hash symbol in the beginning of the header record. To remove that… Continue reading How to remove the hash symbol from the flat file header in PowerCenter
How to add Header Fields To Flat Files in Informatica Power Center (using header command)
How to add Header Fields To a Flat Files in Informatica Power Center using “Header command”? This option will give you the flexibility to fully customize your header names. In this example I am creating a csv target file with comma delimited. Also read another option solution using “Header options” This solution offers a little… Continue reading How to add Header Fields To Flat Files in Informatica Power Center (using header command)
How to add Header Fields To Flat Files in Informatica Power Center (using Header Options)
How to add Header Fields To a Flat Files in Informatica Power Center using “Header Options”? This is one of the quick & easy way to print the header columns in the target flat file. In this example I am creating a csv target file with Pipe delimited. Also read another option solution using “Header… Continue reading How to add Header Fields To Flat Files in Informatica Power Center (using Header Options)
How to get the last day of the month in informatica
How to get the last day of the month in informatica power center? We can use the transformation function LAST_DAY which Returns the date of the last day of the month for each date in a port. SyntaxLAST_DAY(pass_the_input_date) Example LAST_DAY( PLAN_END_DATE ) Let’s consider a real time scenarios. We have a PLAN effective date and… Continue reading How to get the last day of the month in informatica
Parameterize the Flat File Name & Directory in Informatica PowerCenter
How to Parameterize the Flat File Name & Directory in Informatica PowerCenter. Let’s walk through the steps by parameterizing the target file directory and the file name. Define the workflow variables for Target file directory & file name Assign the variable fields in the session task Give the location of the parameter file in the… Continue reading Parameterize the Flat File Name & Directory in Informatica PowerCenter
Pushdown optimization in informatica PowerCenter
This article will help you to understand Pushdown Optimization technique to enhance Informatica ETL performance. We will see how to implement Pushdown optimization and its limitation. Author: Dhandhaliya Dhiraj Informatica ETL Developer | SQL Developer | IICS Developer | IDQ Developer Email | Linkedin Profile What is Pushdown Optimization? Pushdown Optimization is use to increase… Continue reading Pushdown optimization in informatica PowerCenter
Stored Procedure transformation in Informatica
Stored Procedure A stored procedure is a precompiled collection of Transact-SQL, PL-SQL or other database procedural statements and optional flow control statements, similar to an executable script. Stored procedures are used to automate tasks that are too complicated for standard SQL statements. Stored procedures are stored and run within the database. The stored procedure must… Continue reading Stored Procedure transformation in Informatica
Solution – Create Retail Datamart using Informatica PowerCenter
Certification Project – Create Retail Datamart using Informatica PowerCenter Problem Statement. The purpose of this solution is to explain how to create a retail data warehouse / Datamart using PowerCenter. Description: Transaction and master data from OLTP (online transaction processing) systems is loaded into the data warehouse as Fact tables and Dimension tables respectively. In… Continue reading Solution – Create Retail Datamart using Informatica PowerCenter
Performance Tuning – Aggregator Transformations
Aggregator transformations often slow performance because they must group data before processing it. Aggregator transformations need additional memory to hold intermediate group results. Use the following guidelines to optimize the performance of an Aggregator transformation: Group by simple columns. Use sorted input. Use incremental aggregation. Filter data before you aggregate it. Limit port connections. Grouping… Continue reading Performance Tuning – Aggregator Transformations
Source Based Commits
The Integration Service commits data to the target based on the number of rows from some active sources in a target load order group. When the Integration Service runs a source-based commit session, it identifies commit source for each pipeline in the mapping. The Integration Service writes the name of the transformation used for… Continue reading Source Based Commits
Performance Tuning – Target Bottlenecks
The most common performance bottleneck occurs when the Integration Service writes to a target database. Small checkpoint intervals, small database network packet sizes, or problems during heavy loading operations can cause target bottlenecks. How to identify a Target Bottlenecks? Read the thread statistics in the session log. When the Integration Service spends more time on the… Continue reading Performance Tuning – Target Bottlenecks