SUMMARY

• Around 8+ years of total IT experience in Business Requirement Analysis, Application Design, Data Warehousing, Informatica Administration, Data Modelling, Development, Database administration for Mortgage, Insurance, Finance, Healthcare and Telecom/Wireless domains
• Profound knowledge in complete life cycle development of Data Warehouse: Data Analysis, Design, Development, Implementation and Testing using Data Extraction, Transformation and Loading (ETL)
• Knowledge in Data warehousing tools like Power Exchange, Power Analyzer, Informatica Data Quality (IDQ) and Informatica Data Explorer (IDE).
• Expertise in design and implementation of SCD - slowly changing dimensions types (1, 2 and 3), CDC - Change data capture
• Knowledge in Database architecture for OLAP and OLTP applications to model the snowflake and star schema’s and report delivery methodologies.
• Expertise in coding, testing and implementation of SQL Queries and PL/SQL Objects like triggers, stored procedures, functions, packages.
• Proficient in the Integration of various data sources like Oracle, SQL Server, DB2, Teradata, Ms-Access, COBOL, Excel, XML, VSAM files and Flat Files
• Experience in UNIX shell scripting and automation of jobs
• Expertise in Business Intelligence Applications using Oracle Business Intelligence (OBIEE/OBIA) in Marketing & Sales modules.
• Documentation on Detailed Technical Design, Migration process, Test cases and ETL Run Book.
• Strong experience in Tuning both ETL and Database Objects for better performance.
• Experience in coordinating with cross functional teams and project management.
• Excellent analytical and logical programming skills with a good understanding at conceptual level and possess excellent presentation, interpersonal skills with a strong desire to achieve specified goals.

TECHNICAL SKILLS

OS: Windows95/98/2000/XP, Windows NT 4.0, UNIX, MS DOS

Languages: C, C++, SQL and PL/SQL, Teradata SQL, T-SQL, Perl, Unix shell scripting.

ETL Tools: Informatica Power Center 9.x/8.x/7.x,Informatica Power Exchange 8.1, IDQ

Data Modeling: Dimensional Data Modelling, Star Schema and Snowflake Schema, Erwin, Power Analyzer.

Databases: Oracle 11g/10g/8i/9i, SQL Server 2000/2005/2008 , DB2, MS Access 7.0/97/2000, Teradata V2R6, Sybase.

DB Tools: Toad, SQL*plus, SQL Developer, iSQL*plus, SQL*Loader, TD SQL Assistant 13.10, DB Artisan

Scheduling Tools: Control-M, Autosys, Tivoli, TIDAL, CA Scheduler

BI Tools: OBIEE, Business Objects, Cognos

PROFESSIONAL EXPERIENCE

Confidential, Camp hill - PA

Sr. Informatica Lead

Responsibilities:

• Installed and maintained Informatica Power center 9.5/9.6 environments and applied Hotfixes
• Proficient in configuring and administering LDAP configurations into Informatica Application and manage Group/User privileges.
• Designed ETL’s using Informatica Power Center 9.5.1 to extract data from the application Tables and apply validation rules to determine the eligible records that needs to be transmitted to the carriers and generate XML & EDI Files.
• Designed & scheduled ETL batches to load data from DB2 to SQL Server on a daily basis to reduce the load and enhance efficiency for reporting.
• Used Informatica Data Transformation studio to generate the 834 EDI Files.
• Installation of Symantec PGP encryption software for secured transmission of 834 EDI Files
• Involved in gathering the requirements for determining eligibility and analyzing the segments to be included in the 834 EDI file.
• Created Database Views and Procedures in DB2 for populating Source data that is required for ETL processing.
• Work closely with DBA and Infrastructure team on server maintenance and DB/OS upgrades
• Used Java transformation for masking PI Data & invoked jar files from Informatica.
• Created SFTP bat scripts for transmitting files to the Carriers & MMIS and archiving them
• Involved in setting up ETL environments, Performed ETL code reviews and Migration of ETL Objects across repositories.
• Automate reports to publish the batch execution statistics on a daily basis
• Mentored other ETL Developers, assigned tasks and had stand up meetings to improve efficiency and provide quick turnarounds.
• Coordinated with Functional Managers in finalizing the requirements and providing estimations required for the ETL Design.
• Involved in setting up automated process of receiving the files from various vendors using IBM Connect Direct and other sftp tools.
• Provide 24X7 On-Call production support in resolving issues in a timely manner and follow escalation procedures to meet the SLA

Environment: Informatica Power Center 9.5.1/9.6.1 DB2 10.0, Windows Server, SQL Server,WinScp, FileZilla, Putty, SFTP/FTP, IBM Data studio 4.1.0, MS-Excel, Connect Direct, Symantec PGP Encryption

Confidential - Irving, TX

Sr.Informatica Developer/Lead

Responsibilities:

• Gathering Business requirements, Perform analysis and provide necessary documentation
• Installed and configured Power Center 9.1.0 and 9.5.1 on Unix platform
• Designed ETL’s using Informatica Power Center 9.1.0 to extract data from the functional Data Marts (Sales Data Mart & Billing Data Mart) and aggregate the metrics into the SQL Server Database which would be later used to generate Executive dashboards.
• Worked along the migration of Oracle to Teradata project where the existing Data Marts have been migrated from Oracle to Teradata environment
• Analyzed and Identified the Tables/Views, Procedures and ETL objects that need to be migrated and prepared necessary Documentation
• Developed BTEQ scripts & Designed data feeds to load into Teradata from Oracle
• Implemented Teradata Utilities such as FastLoad, MultiLoad & FastExport and Teradata Parallel Transporter connections for loading data to enhance runtime and performance.
• Involved in the creation of new objects (Tables/Views, Triggers, Indexes, Keys) in Teradata and modified the existing 700+ ETL’s to point to the appropriate environment.
• Designed ETL’s & procedures to allow switching mechanism in loading the partitioned Tables so that the application will point to the latest refreshed data without experiencing downtime
• Coordinate with offshore team and have timely status calls on their deliverables.
• Designed ETL’s for masking the data when made available for the Offshore Dev team.
• Performed ETL Code Reviews and migrate ETL Objects from DEV, QA to PROD environments
• Developed UNIX scripts for dynamic generation of Param Files & for FTP/SFTP transmission
• Maintained repositories and performed Admin level tasks such as creating Folders,Users, Connections & assigning proper roles and privileges
• Developed different types of Drilldown,Aggregation Customized Reports in order to meet the client requirements.
• Developed UNIX scripts for handling the execution of workflows and pre validation scripts that checks & releases the Table level locks since the last ETL run before loading the current session
• Used TIDAL enterprise scheduler for scheduling the Daily,Weekly & Monthly jobs
• Coordinated and interacted with other teams to maintain integrity in performing the tasks and execution of the loads appropriately in a timely manner.

Environment: Informatica Power Center 9.1.0/9.5.1 , Oracle 11g/10g,SQLServer 2008,Teradata 13.10, UNIX(Sun Solaris 5.10/AIX),Toad 10.0,SQLDeveloper 3.0,SQL Assistant 13.10,F-Secure 5.3,Putty,SFTP/FTP, MS-Excel.

Confidential - Dallas, TX

Informatica Dev/Admin

Responsibilities:

• Involved in Architecting and designing an Enterprise Data Warehouse for housing clinical information which would benefit the FOCUS study in analyzing the calculated CORE MEASURES.
• Involved in Designing the Logical and Physical Data models using Erwin
• Responsible for upgrading Informatica PC 8.6.1 to 9.0.1
• Designed ETL’s to extract data from SoftMed HL7 Files and update the EDW with the Patient related Clinical information. This includes a one-time history load and subsequent daily loads.
• Implemented SCD type 2 for loading the dimension tables with 8080 Core measures data and update the periodic FACT tables in EDW for further reporting needs.
• Migrate historical ADT data, which is one time and built migration process for ongoing data loads into the Data warehouse using Informatica power center designer which would benefit the FOCUS study in analyzing the calculated CORE MEASURES
• Developed procedures to derive Patient keys for respective facilities, implement ETL Load Control process, truncate procedures
• Extensively worked on Oracle SQL’s for Data Analysis and debugging.
• Developed scripts in UNIX to accumulate the HL7 files and create file lists for the functioning of indirect method in Informatica and archiving them post completion of the process.
• Handled scripts for pre validating the source File structures before loading into the Staging by comparing the source file headers against the base lined header
• Mentored team members and guided them with ETL’s & procedures to allow switching mechanism in loading the partitioned Tables so that the application will point to the latest refreshed data without experiencing downtime
• Created ETL load control process which keeps track of all the load run statistics and publish them to the users to notify the load completion
• Performance tuning has been done to increase the through put for both mapping and session level and SQL Queries Optimization as well.
• Involved in migrating objects to different environments using Deployment groups and Labels
• Worked closely with the Business analysts and attended meetings to gather and discuss the requirements for various projects.
• Prepared various documents like ETL Specification doc, Technical Design doc, Run Book etc.
• Offered production support services to resolve ongoing issues and trouble shoot them to identify the bugs

Environment: Informatica Power Center 8.6.1/9.0.1 , Oracle 11g/10g,UNIX(AIX),Toad 10.0,SQL Developer 3.0,WinScp, FileZilla, Putty, SFTP/FTP, Tivoli scheduler, MS-Excel, Ms-Access.

Confidential - Dallas, TX

Informatica Developer/Lead.

Responsibilities:

• Involved in architecture, design and implementation of DARS project which involved setting up of new environments & servers to replicate the data between them for critical applications.
• Installed and configured Informatica Power center 9.0.1 on Unix environment.
• Involved in Project UNO (enterprise wide release) analyzing the impact of changes on the existing systems and maintaining uniqueness across the LOB.
• Immensely worked on designing of ETL mappings, sessions and Workflows using Informatica 8.6/9.0.1.
• Designed ETL processes involving dynamic generation of Param Files and preferred usage of Mapping variables and Mapping parameters.
• Performed Incremental loading, extracting Delta on a daily/hourly basis, SCD type 2 and Change Data capture (CDC).
• Implemented indirect method of reading Flat Files, XML File sources by creating File lists.
• Developed ETL’s to compare the DB objects and help in remediate the discrepancies across environments.
• Developed standard and re-usable mappings and mapplets using various transformations like Expression, Aggregator, Joiner, and Source qualifier, Router, Lookup, Sequence Generator, SQL Transformation, Stored Procedure Transformation and Filter.
• Leading an ETL Offshore Team, responsible for analyzing, designing and developing ETL strategies and processes, writing ETL specifications and reviewing day to day work.
• Explored various techniques for optimizing mappings, sessions namely dynamic look up caching, Session partitioning, Target Update overrides, Lookup and SQL overrides,Pre and Post SQL’s.
• Deliberately worked on PL/SQL Procedures, Functions, Packages, dropping and rebuilding of Indexes and Primary keys,, fine tuning of SQL’s, and Optimization at the Database level.
• Involved in business analysis and technical design sessions with business and technical staff to develop requirements document, translate the requirements into technical specifications, prioritize and track multiple ETL projects throughout SDLC.
• Used the OBIEE 10.1.3.4 Administration tool to develop and enhance Data Model
• Configured and created OBIEE Repository, Involved in modifications of the physical, the BMM, and the presentation layers of metadata repository
• Create groups for users to provide suitable securities for different dashboards and reports
• Developed Unix Scripts to handle the execution of the workflows, populate relational Meta Tables to track the run times of the ETL Workflows, create File lists, archiving mechanism, initiate Secure File Transfers between servers, obtaining connection information from encrypted gpg files and validating reconciliation files.
• Scheduled ETL processes and wrapper Unix scripts using Control-M scheduler.
• Proven experience in projects which required quick turn around and prompt deliverable timelines.

Environment: Informatica Power Center 8.6.1/9.0.1 , Oracle 11g/10g,DB2 7.1,SQL Server, UNIX(Sun Solaris5.8/AIX),Toad 10.0,SQL Developer 3.0,DBArtisan,WinScp,Putty,SFTP/FTP,XML Editor, MS-Excel, Ms-Access.

Confidential - Columbus, OH

Informatica Consultant

Responsibilities:

• Responsible for developing, support and maintenance for Extraction, Transformation and Loading data from heterogeneous source systems using Informatica Power Center 8.6.1
• Validation of the source file-layouts from different source systems
• Analysis of Source, Requirement, existing OLTP system and Identification of required dimensions and facts from the Database.
• Created Technical design specification documents based on the functional design documents and the physical data model.
• Responsible for creating different reusable sessions, reusable worklets and workflows in Informatica for the initial data loads into development, QA and production environments for the Warehouse and Created User Defined Functions in Informatica to reduce code redundancy.
• Involved in massive data cleansing prior to data staging from flat files.
• Implemented slowly changing dimensions Type 2, CDC.
• The scope of CDC was to find the delta’s from the previous day file and current day file to flag changed, new, unchanged and deleted records.
• Wrote UNIX and PERL scripts to ensure the smooth/correct execution of a test, apart from concatenation and deletion of 2 or more target files, using the Post-Session or Pre-Session functionalities.
• Involved in performance tuning of the ETL processes.
• Debugging invalid mappings using break points, testing of stored procedures and functions, testing of Informatica sessions, batches and the target Data.
• Developed shell scripts, PL/SQL procedures, for creating/dropping of table and indexes of performance for pre and post session management.

Environment: Informatica Power Center 8.6.1, Power Exchange, Oracle 9i, DB2 7.1, SQL Server, UNIX (Sun Solaris5.8/AIX), Windows XP Professional, UltraEdit-32, Erwin Data Modeler, FTP, COBOL, MS-Excel, Ms-Access.

Confidential - New Jersey

Informatica Developer

Responsibilities:

• Worked to identify different source systems and performed data analysis to determine the best approach of procuring source data using Informatica Power Center 8.5.
• Extensively used stored procedures and also worked with parallel processing.
• Created snapshot for the transactional tables in distributed databases and also created Triggers, Procedures and Functions for the backend Development.
• Maintained files (data) for Prescriber and drugs prescribed at the hospitals coming from vendor companies called IMS and NDC.
• Created shared folders, local and global shortcuts to reuse metadata.
• Extensively worked on autosys to schedule the jobs for loading data into the tables and also for spooling the data to various down streams.
• Parsed high-level design specification to simple ETL coding and mapping standards.
• Good exposure to Informatica Change Data Capture (CDC) using Informatica PowerExchange
• Developed automated Korn-shell scripts to schedule the load of flat files.
• Writing BTEQ script and used Fatsload, Export/Import for loading data into the tables.
• Transfer of large volumes of data using T-Pump.
• Tuned performance of Informatica session for large data files by increasing block size, data cache size, sequence buffer length and target based commit interval.
• Was responsible for the version control and up-gradation.
• Performed Performance tuning in RDBMS sources by implementing complex SQL query over rides in Source qualifier transformation, which reduced unwanted data to be processed, thereby increasing the data processing speed.
• Implemented Explain Plan and SQL Trace for improving the performance of SQL queries.
• Designed and developed UNIX scripts to load tables and also for scheduling the jobs.
• Leading the team for Production support.

Environment: Informatica 8.5(Repository Manager, Repository Sever Administration Console,Power Center Designer, Workflow Manager, Workflow Monitor),,Power Exchange,Teradata, Erwin, COBOL, Oracle 10g, PL/SQL, Autosys Windows 2000, DB2, Windows NT 4.0