PowerCenter provides a number of command line programs that you call from your application to manage the Integration Service and Repository Service. What is pmcmd? pmcmd is a command line programs that can be used to manage workflows. You can use pmcmd to start, stop, schedule, and monitor workflows. This command enables you to manage […]
Read MoreCategory: Informatica PowerCenter
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 MoreHow to create Repository Folder
Repository Folders Folders provide a way to organize and store metadata in the repository, including mappings, schemas, and sessions. Folders help you logically organize the repository. A folder consist of several objects that form power center code. Most of the objects created during the life cycle of the development are stored within the respective folders. […]
Read MoreHow To Propagate Port Attributes
Propagating Port Attributes When you edit a port name in a transformation, by default, the Designer propagates references to that port in the expressions, conditions, and other ports in that transformation. You can also propagate changed attributes throughout the mapping. The Designer propagates ports, expressions, and conditions. We can propagate changes forward, backward, or in […]
Read MoreMD5 (Message-Digest algorithm 5) In Informatica
MD5 (Message-Digest algorithm 5) In Informatica MD5 is a one-way cryptographic hash function in informatica which is used to verify the data integrity. This function uses Message-Digest algorithm 5 (MD5) to generate a 128-bit hash value. MD5 returns a 32 character string of hexadecimal digits 0-9 & a-f and returns NULL if the input is a […]
Read MoreHow to Convert Julian to Gregorian date in Informatica
How to Convert Julian date to Gregorian date in Informatica Dates in the Julian calendar are called Julian dates which are not supported in Informatica. We need to convert the date to Gregorian calendar for further informatica processing. We can use TO_DATE function to convert the Julian date to Gregorian in an expression and then […]
Read MoreHow to remove duplicate records using Sorter Transformation
How to remove duplicate records using Sorter Transformation In the last two post we have seen how to remove duplicate records (Only one column) using Aggregator and Expression. We have identified the duplicate JOB_ID and skipped those rows from loading to target. The same logic CANNOT be done using Sorter Transformation. In sorter we cannot […]
Read MoreHow to load every 5th record into target table
How to load every 5th record into target table We need to read the source table and load only 1st and every 5th (nth) records to target. Let’s start the mapping for this. We need a sequence generator and a filter transformations to complete this requirement. The logic which we are following here is to assign […]
Read MoreHow to remove duplicate records using Expression
How to remove duplicate records using Expression In the last post we have used Aggregator Transformation to remove the duplicate records. In this post we will use the Expression transformation to remove the duplicate JOB_ID from the source Flat file. Source – Flat File Target – Oracle Table Key port – JOB_ID Transformations Sorter – To […]
Read MoreHow to remove duplicate records Using Aggregator
How to remove duplicate records Using Aggregator There are couple of options available in informatica to remove duplicate records from the source. For Relational Tables 1. Source Qualifier > ‘SELECT DISTINCT’ option 2. Source Qualifier > SQL override (Write your own Query) For Flat files or other sources Sorter > Aggregator Sorter > Expression > […]
Read MoreInformatica Target Load Plan
Target Load Group and Target Load Plan If you have multiple Source Qualifier transformations connected to multiple targets, you can specify the order in which the Integration Service loads the data into the targets using ‘Target Load Plan’. This will give you an option to control which target load order group need to execute first and […]
Read MoreHow to Load only Half of the records from Source table
Load only Half of the records from Source table The requirement is to process only half of the records from the source table. To do this we need to calculate the total number of rows (TOTAL-COUNT) in the source table and then assign ROW-COUNT and exclude the rows if the ROW-COUNT <= (TOTAL-COUNT/2). Let’s design the mapping for this. We […]
Read MoreHow to fix [Informatica][ODBC Oracle Wire Protocol Driver] Optional feature not implemented
[Informatica][ODBC Oracle Wire Protocol Driver] Optional feature not implemented You may get this error while trying to Preview data on Target in a Mapping Designer. I have also seen the same error from Source Qualifier Transformation while applying SQ properties. Let’s see how to fix this error. This is a part of ODBC Data Source configuration, […]
Read MoreSource Qualifier – Full Outer Join
Full Outer Join using Source Qualifier – ‘SQL Query’ Integration service support only Left & Right outer joins. We cannot perform Full outer join using ‘User Defined Join’ properties in SQ, you will get a database error. This can be done by using overriding default ‘SQL query‘ in SQ. Full Outer Join Query SELECT EMP_JOB1.JOB_ID, […]
Read MoreSource Qualifier – User Defined Join – Right Outer Join
How to join two tables – Source Qualifier – User Defined Join – Right Outer ** Source Qualifier Transformation is the most efficient way to join two tables in the same Database ** There are two options available in SQ to join multiple tables, One using ‘User Defined join’ property and the other using ‘SQL Query’. We can […]
Read More