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 […]
Read MoreCategory: 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 […]
Read MoreHow 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 […]
Read MoreDecode 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 […]
Read MoreHow 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 […]
Read MoreHow 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 […]
Read MoreHow 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 […]
Read MoreHow 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 […]
Read MoreParameterize 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 […]
Read MorePushdown 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 […]
Read MoreStored 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 […]
Read MoreSolution – 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 […]
Read MorePerformance 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 […]
Read MoreSource 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 […]
Read MorePerformance 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 […]
Read More