PROFESSIONAL SUMMARY:

• About 6+ years of experience in Information Technology including Data Warehouse/Data Mart development using ETL/Informatica Power Center/ SSIS.
• Worked on various domains including Insurance, Finance, Health, Pharmaceutical and Retail.
• Good exposure in overall SDLC including requirement gathering, development, testing, debugging, deployment, documentation and production support.
• Worked in various Heterogeneous Source Systems like Oracle, DB2 UDB, Teradata, MS SQL Server, Flat files and Legacy systems.
• Practical understanding of the Data modeling (Dimensional & Relational) concepts like Star - Schema Modeling, Snowflake Schema Modeling, Fact and Dimension tables.
• Good understanding of views, Synonyms, Indexes, Joins and Sub-Queries. Extensively used Cursors and Ref Cursors.
• Have implemented Type I and Type II for SCD on Dimensions.
• Extensively created mapplets, common functions, reusable transformations, look-ups for better usability.
• Experience in Performance tuning of ETL process. Reduced the execution time for huge volumes of data for a company merger projects.
• Providing the right level of data quality management for the appropriate business user.
• Worked with Informatica Server and Client tools, experience in the Data Analysis, Design, Development, Implementation, Testing, Production Support of Database/Data warehousing, Legacy for various industries using Data Modeling, Data Extraction, Data Transformation and Data Loading.
• Extensive experience in Tuning and scaling the procedures for better performance by running explain plan and using different approaches like hint and bulkload.
• Extensively used SQL, PL/SQL in writing Stored Procedures, Functions, Packages and Triggersin Oracle.
• Extensively used SQL, TSQL for writing stored procedures, and functions in MS SQL Server.
• Experience in using Exception Handling strategies to capture errors and referential integrity constraints of records during loading processes to notify the exception records to the source team.
• Experience in UNIX shell scripting, job scheduling and communicating with server using pmcmd.
• Delivered all the projects/assignments within specified timelines.
• Responsible for Team Delivery and Participated in Design Reviews.

TECHNICAL SKILLS:

ETL Tools: Informatica Power Center 6.x/7.x/ 8.1/8.6.1/9.6.1/10 (Source Analyzer, Mapping Designer, Workflow Monitor, Workflow Manager, Power Connects for ERP and Mainframes, Power Plugs), Power Exchange, Power Connect. IBM InfosphereDataStage 8.5 (Designer, Director, Administrator)OLAP/DSS Tools Business Objects XI r2/6.5.1

Databases: Oracle 8i/9i/10g/11g, DB2, MS SQL Server 2005/2000, Teradata v2r12/v2r6/v2r5

Others: TOAD, PL/SQL Developer, Perl, SQL-Navigator, TestDirector, WinRunner

Database Skills: Stored Procedures, Database Triggers and packages

Data Modeling Tools: Physical and Logical Data Modeling using ERWIN 3.5/4.5/7.x

Languages: C, C++, UNIX shell scripts, XML, HTML

Operating Systems: Windows NT/ 2000/2003, AIX, Sun Solaris, LINUX, UNIX

PROFESSIONAL EXPERIENCE:

Confidential

ETL Developer/Database Developer

Responsibilities:

• Collaborated with Business analysts for requirements gathering, business analysis and designing of the Enterprise Data warehouse
• Involved in massive data cleansing prior to data staging.
• Designed SCD Type 1 and Type 2 mappings
• Used Informatica client tools Informatica Repository Manager and Informatica Workflow Manager, Informatica Designer, Informatica Monitor
• Used DMX to load data into Bigdata and used ironstream utility to load data into splunk
• Design and Development of ETL routines, using Informatica Power Center within the Informatica Mappings, usage of Lookups, Aggregator, Ranking, Mapplets, connected and unconnected stored procedures / functions / Lookups, SQL overrides usage in Lookups and source filter usage in Source qualifiers and data flow management into multiple targets using Routers were extensively done.
• Created complex mappings with shared objects/Reusable Transformations/Mapplets using mapping/mappletParameters/Variables.
• Configured workflows with Email Task, which would send mail with session, log for Failure of a sessions and for Target Failed Rows.
• Used Server Manager to create schedules and monitor sessions. And to send the error messages to the concerned personal in case of process failures.
• Created sequential/concurrent Sessions/ Batches for data loading process and used Pre & Post Session SQL Script to meet business logic.
• Used pmcmd commands on command prompt and executed Unix Shell scripts to automate workflows and to populate parameter files.
• Created Stored Procedures to transform the Data and worked extensively in PL/SQL for various needs of the transformations while loading the data into SQL Server database.
• Worked with UNIX shell scripts extensively for job execution and automation.
• Developed Unix Scripts to extract the data from Oracle, SQLServer and load into SQL Server database.
• Scheduled Informatica workflows to run at regular intervals.
• Used SQL tools like Query Analyzer and TOAD to run SQL queries and validate the data.
• Tuning the Mappings for Optimum Performance, Dependencies and Batch Design.
• Designed and developed the logic for handling slowly changing dimension table’sload by flagging the record using update strategy for populating the desired.
• Documented ETL test plans, test cases, test scripts, test procedures, assumptions, and validations based on design specifications for unit testing, system testing, expected results, preparing test data and loading for testing, error handling and analysis.
• Involved in Unit testing, User Acceptance Testing to check whether the data is loading into target, which was extracted from different source systems according to the user requirements.
• Performed numerous ad-hoc requests, financial reports involving SQL scripts, UNIX, SAS, TOAD
• Programming client side scripts to deliver more dynamic and customized content to the Internet browsers

Environment: Informatica Power Center 9.6.1, Oracle 11g,DB2, MS SQL Server2008, SQL, PL/SQL, T-SQL,SQL*Plus, TOAD, Erwin, Unix, Syncsort technologies like DMX, Ironstream, Splunk, Cassandra, Hive Bigdata.

ETL Developer

Responsibilities:

• Analyzing the source data coming from different sources and working with business users and developers to develop the Model.
• Involved in Dimensional modeling to Design and develop STAR Schema, Using ER-win to design Fact and Dimension Tables.
• Tuned performance of Informatica session for large data files by increasing block size, data cache size, sequence buffer length and target based commit interval.
• Worked with MLOAD, FASTLOAD, TPUMP and BTEQ utilities of Teradata for faster loading and to improve the performance.
• Creation of customized MLOAD scripts on UNIX platform for Teradata loads
• Written several Teradata BTEQ scripts to implement the business logic.
• Created Test tables and worktables on development and production on Teradata.
• Worked extensively with the Teradata Quarryman to interface with the Teradata.
• Developed views on departmental and claims engine database to get the required data.
• Developed application views in Teradata and using expression and router implemented the Change Data Capture (CDC) process.
• Involved in source data profiling& source system analysis.
• Involved in designing the Logical and Physical data models for the data warehouse
• Developed SQL code for data validations and data computation process on source DB2 transaction system and on target warehouse
• Involved in massive data cleansing prior to data staging.
• Developed shell scripts, cron jobs for job execution and automation on server side.
• Define strategy for ETL processes, procedures and operations
• Prepared a handbook of standards and Documented standards for Informatica code development.
• Administrated users and user profiles and maintained the repository server.
• Tuning the complex mappings based on source, target and mapping, session level
• Extensively used pmcmdcommandson command prompt and executed Unix Shell scripts to automate workflows and to populate parameter files
• Extensively used transformations like lookup, router, Aggregator, sequence generator, filter, update strategy, joiner.
• Handled slowly changing dimensions of Type1/ Type 2 to populate current and historical data to dimensions and fact tables in the Data Warehouse.
• Document the process for further maintenance and support.

Environment: Informatica Power Center9.0.1/7.1.4,Teradata v2r12, Business Object XI, Oracle 10g, MS SQL Server, Toad, PL/SQL, SQL, XML

ETL Developer

Responsibilities:

• Involved in gathering of business scope and technical requirements and written technical specifications.
• Involved in all phases of software development life cycle (SDLC) in RUP framework.
• Fine-tuned existing Informatica maps for performance optimization.
• Developed complex mappings in Informatica to load the data from various sources using different transformations like Source Qualifier, Look up (connected and unconnected), Expression, Aggregate, Update Strategy, Sequence Generator, Joiner, Filter, Rank and Router transformations.
• Involved in massive data cleansing and data profiling of the production data load.
• Created synonyms for copies of time dimensions, used the sequence generator transformation type to create sequences for generalized dimension keys, stored procedure transformation type for encoding and decoding functions and Lookup transformation to identify slowly changing dimensions.
• Worked on Informatica tool - Source Analyzer, Data warehousing designer, Mapping Designer &Mapplet and Transformations.
• Involved in capturing slowly changing dimensions including type-I, type-II, and type-III.
• Used ready wizards in Informatica to capture and implement SCD Type-II (Changing Data) and tuned it requirement.
• Involved in the development of Informatica mappings and also tuned for better performance.
• Load balancing of ETL processes, database performance tuning and Capacity monitoring.
• Involved in the Unit testing, Event & Thread testing and System testing of the individual.
• Analyzed existing system and developed business documentation on changes required.
• Made adjustments in Data Model and SQL scripts to create and alter tables.
• Scheduled and monitored automated weekly jobs under UNIX environment.
• Extensively involved in testing the system from beginning to end to ensure the quality of the adjustments made to oblige the source system up-gradation.
• Involved in tracking and reporting defects using Clear Quest and was also responsible for communicating the status to the team in bug meetings.
• Worked with many existing Informatica Mappings to produce correct output.
• Made use of Server Manager to create and monitor sessions and batches.
• Efficient Documentation was generated for all mappings and workflows.

Environment: Informatica Power Center 8.5.1,Custom programming using Perl, IMS Data (Xponent, XponentPlantrak, PlanTrak, Rx Data), Teradata V2R6, Oracle 10g, MS SQL Server 2005/2000, RTMS, HP UNIX Shell Scripting, PL/SQL, Syncsort for UNIX, Rational Clear Quest, Windows 2000, HP-UX.