Responsibilities:
• Analyze the business requirements and framing the Business Logic for the ETL Process and maintained the ETL process using Informatica Power Center.
• Created complex Informatica mappings to load the data mart and monitored them. The mappings involved extensive use of transformations like Aggregator Router, Expression, Joiner, Union, Normalizer and Sequence generator.
• Develop an ETL Informatica mapping in order to load data into staging area. Extracted from Mainframe files and databases and loaded into Oracle 11g target database.
• Create workflows and work lets for Informatica Mappings.
• Work on SQL coding for overriding for generated SQL query in Informatica.
• Involve in Unit testing for the validity of the data from different data sources.
• Design and develop PL/SQL packages, stored procedure, tables, views, indexes and functions. Experience dealing with partitioned tables and automating the process of partition drop and create in oracle database.
• Involve in migrating the ETL application from development environment to testing environment.
• Perform data validation in the target tables using complex SQLs to make sure all the modules are integrated correctly.
• Worked with Informatica toolkit, Analysis, data cleansing, data matching, data conversion, exception handling, and reporting and monitoring capabilities.
• Perform Data Conversion/Data migration using Informatica PowerCenter.
• Involve in performance tuning for better data migration process.
• Analyze Session log files to resolve error in mapping and identified bottlenecks and tuned them for optimal performance.
• Create UNIX shell scripts for Informatica pre/post session operations.
• Automated the jobs using CA7 Scheduler.
• Worked on Direct Connect process to transfer the files between servers.
• Document and present the production/support documents for the components developed, when handing-over the application to the production support team.
• Worked with XML targets for the data coming from SQL server source.
• Query tuning and SQL Query override used in Source Qualifier transformation to pull historical data from database not earlier than the given date i.e. the change data capture (CDC).
• Parameterized the whole process by using the parameter file for the variables.
• Imported xsd file to create the xml target and create the Hierarchical Relationship
• And normalized views.
• Implemented the logic by using HTTP transformation to query the web server.
• Configure and setup a secure FTP connection to the vendor using the Informatica Managed File transfer software.
• Created complex Shell scripts for various set of actions that would automate the process of executing the actions like validating the presence of indicator files.
• Pushing the compressed and encrypted xml files and flat files generated to the external vendor using MFT.
• Involved in Unit testing and system integration testing (SIT) of the projects.
• Assist the team members with the mappings developed as part of knowledge transfer. Environment: Informatica PowerCenter8.6.1/ 8.1.1, Windows Server 2008, MS-SQL Server 2005, Batch Scripting, Perl Scripting, XML Targets, Flat Files, ), Tidal 5.3.1. UNIX. Industry: Volkswagen of America, (Chennai, India) (Auburn Hills, MI) Role: ETL Developer/Analyst