- Over 9 years of Technical and Functional experience in Data warehouse implementations, Data Visualization, ETL methodology using Informatica Power Center, Teradata, Oracle 10g/9i/8i and MS SQL SERVER in Finance, Health Insurance and Pharmacy Domains.
- Expertise in Informatica PowerCenter7.x/8.x/9.1 Designer tools like Source Analyzer, Warehouse Designer, Mapping Designer, Maple Designer, Transformation Developer, Workflow Manager and Workflow Monitor.
- Designed and developed complex mappings, from varied transformation logic like Unconnected and Connected lookups, Router, Filter, Expression, Aggregator transformation, Active and Passive transformations, Joiner and Update Strategy transformations.
- Expertise in design and implementation of Slowly Changing Dimensions (SCD) type1, type2, type3.
- Expertise in RDBMS, Data Warehouse Architecture and Modeling. Thorough understanding and experience in data warehouse and data mart design, Star schema, Snowflake schema, Normalization and Demoralization concepts and principles.
- Experience in working with Mainframe files, COBOL files, XML, and Flat Files.
- Extensive experience in ETL (Extract Transform Load), Data Integration and Data Warehousing using Informatica Power Center & Oracle PL/SQL technologies.
- Extensive knowledge in Business Intelligence and Data Warehousing Concepts with emphasis on ETL and System Development Life Cycle (SDLC).
- Extensive knowledge on Data Profiling using Informatica Developer tool.
- Implemented Slowly changing dimension types (I, II &III) methodologies for accessing the full history of accounts and transaction information designed and developed change data capture solutions (CDC) for the project, which captures and analyzes changes from daily feeds to maintain history tables.
- Very strong skills on project management, requirement analysis, business analysis, database modeling, design and analysis, issue co - ordination and development with Teradata/Oracle/SQL Server based Relational Databases.
- Proficient in Teradata database design (conceptual and physical), Query optimization, Performance Tuning.
- Strong hands on experience using Teradata utilities (FastExport, MultiLoad, FastLoad, Tpump, BTEQ and QueryMan).
- Familiar in Creating Secondary indexes, and join indexes in Teradata.
- Expertise in different types of loading like Normal and Bulk loading challenges. Involved in Initial Loads, Incremental Loads, Daily loads and Monthly loads.
- Expert in troubleshooting/debugging and improving performance at different stages like database, Workflows, Mapping, Repository and Monitor
- Involved in Informatica administration such as creating folders, users, change management and also involved in moving code from DEV to TEST and PROD using deployment groups in Informatica Repository Manager.
- Experience in handling different data sources ranging from flat files, Excel, Oracle, SQL Server, Teradata, DB2 databases, XML files.
- Expertise in tuning the performance of Mappings and sessions in Informatica and determining the performance bottlenecks.
- Proficient in applying Performance tuning concepts to Informatica Mappings, Session Properties and Databases.
- Experienced with mentoring Teradata Development teams, data modeling, program code development, test plan development, datasets creation, testing and result documentation, analyzing defects, bug fixing.
- Creating dashboards in line with the analytical uniformity using the advanced Analysis of Tableau Desktop, and organizing Dashboard views and objects.
- Experience in using Tableau functionalities for creating different Request, Filters, Charts, Interactive dashboards with Page and dashboard prompts.
- Hands on experience in handling data from various source systems such as Flat Files, XML Source, Oracle, MS SQL Server, IBM DB2, Teradata and Excel Files
- Excellent communication skills and experienced in client interaction while providing technical support and knowledge transfer.
Confidential - Hoffman Estates, IL
- Creation of Frame work, logical and physical data models for Level 0, 1 as per BI requirements.
- Define data types, nullability, Primary Indexes and Secondary Indexes.
- Worked on Source system analysis (SSA) on Oracle. Coded complex Oracle queries.
- Created ETL Design, Process and Mapping including Data quality documents.
- Created Functional and Technical specs to load data into Teradata warehouse.
- Involved in the identification and analysis of the source data for performing the ETL operations
- Provide the staging solutions for Data Validation and Cleansing with Quality Stage and Datastage ETL jobs
- Designed Quality Stage Jobs in order to perform data Cleansing using Investigate Stage, Standardize Stage, Match Frequency, Survive Stage, Reference match Stage
- Developed various business processes and Context Diagrams to find new ways of doing certain tasks, which resulted in efficient processes, cost and time savings. Develop Proof of concept for model ideas
- Used DataStage stages namely Sequential file, Transformer, Aggregate, Sort, Datasets, Join, Funnel, Row Generator, Remove Duplicates, Teradata Extender, Copy stages extensively.
- Developed job sequencer with proper job dependencies, job control stages and triggers.
- Excessively used DS Director for monitoring job logs to de-bug and resolve issues.
- Worked with Datastage Manager for importing metadata and take up project backups.
- Used Teradata API and Teradata Multiload Datastage stages extensively to load data into EDW.
- Coded numerous BTEQ scripts with complex logic to load/update aggregate tables for Level 1.
- Coded MLOAD and FLOAD scripts to Load data from staging tables.
- Designed and coded different SQL statements in Teradata BTEQ for generating reports.
- Involved in query translation, optimization and execution.
- Used Explains to optimize Teradata SQL queries for better performance.
- Used the Teradata tools Teradata SQL Assistant, Administrator and PMON extensively.
- Performance tuning using join Index, Hash Index and derived tables.
- Documented ETL test plans, test cases, test scripts, and validations based on design specifications.
- Used Control-M job scheduler for automating the monthly regular run of DW cycle
- Wrote Shell Scripts to check for the existence of files and count comparison.
Environment: IBM DataStage 8.x (Designer, Director), Teradata 14.0 (FastLoad, MultiLoad, FastExport, BTEQ), Teradata SQL Assistant., V2R6, Control-M, UNIX & Micro Strategy
Confidential, Miami, FL
- Involved in full Software Development Life Cycle (SDLC) - Business Requirements Analysis, preparation of Technical Design documents, Data Analysis, Logical and Physical database design, Coding, Testing, Implementing, and deploying to business users.
- Interacted with business team to understand business needs and to gather requirements.
- Involved in data validation of the results in tableau by validating the number against the data in the database tables by querying on the database.
- Prepared requirements document in order to achieve business goals and to meet end user expectations.
- Created Mapping document from Source to stage and Stage to target mapping.
- Involved in creating data models using Erwin.
- Worked with Designer tools like Source Analyzer, Target designer, Mapping designer, Mapplets designer, Transformation Developer.
- Designed Mappings by including the logic of restart.
- Did the Data Profiling and Data Analysis using SQL queries looking for Data issues, Data anomalies.
- Created Source and Target Definitions, Reusable transformations, Mapplets and Worklets.
- Created Mappings and used transformations like Source Qualifier, Filter, Update Strategy, Lookup, Router, Joiner, Normalizer, Aggregator, Sequence Generator and Address validator.
- Involved in tuning the mappings, sessions and the Source Qualifier query.
- Identified performance issues in existing sources, targets and mappings by analyzing the data flow, evaluating transformations and tuned accordingly for better performance.
- Designed and Optimized Data Connections, Data Extracts, Schedules for Background Tasks and Incremental Refresh for the weekly and monthly dashboard reports on tableau server.
- Changing the existing Data Models using Erwin for Enhancements to the existing Data warehouse projects.
- Manage all technical aspects of the ETL mapping process with other team members.
- Developed mappings to load Fact and Dimension tables, SCD Type 1 and SCD Type 2 dimensions and Incremental loading and unit tested the mappings.
- Worked with the Statisticians, Data Managers to provide SAS programming in analyzing Clinical Trial Data.
- Performed Unit testing and created Unix Shell Scripts and provided on call support.
- Created sessions and workflows to run with the logic embedded in the mappings.
- Extensively used SQL, PL/SQL code to develop custom ETL solutions and load data into data warehouse system.
- Developed PL/SQL triggers and master tables for automatic creation of primary keys.
- Created PL/SQL stored procedures, functions and packages for moving the data from staging area to data mart.
- Used Bulk Collections for better performance and easy retrieval of data, by reducing context switching between SQL and PL/SQL engines.
- Partitioned the fact tables and materialized views to enhance the performance.
- Created records, tables, collections (nested tables and arrays) for improving Query performance by reducing context switching.
- Created Informatica Mappings and TPT Scripts to load Medical, Eligibility and Pharmacy claims from flat file to table.
- Worked with TPT wizards to generate the TPT scripts for the Incoming Claims data.
- Implemented pipeline partitioning concepts like Hash-key, Round-Robin, Key-Range, Pass Through techniques in mapping transformations Used Autosys for Scheduling.
- Performed tuning and optimization of complex SQL queries using Teradata Explain and Run stats.
- Created a BTEQ script for pre population of the work tables prior to the main load process.
- Extensively used Derived Tables, Volatile Table and Global Temporary tables in many of the ETL scripts.
- Developed MLOAD scripts to load data from Load Ready Files to Teradata Warehouse.
- Performance Tuning of sources, Targets, mappings and SQL queries in transformations.
- Worked on exporting data to flat files using Teradata FastExport.
- Analyzed the Data Distribution and Reviewed the Index choices.
- Worked on building queries to retrive data into Tableau from SQL Server 2008 and Oracle SQL Developer, and developed T-SQL Statements for loading data into target database.
- Developed best practices guide for Tableau implementation using Tableau Desktop, Tableau server and Tableau Online.
- In-depth expertise in the Teradata cost based query optimizer, identified potential bottlenecks.
- Worked with PPI Teradata tables and was involved in Teradata specific SQL fine-tuning to increase performance of the overall ETL process.
- Implemented project using Agile software methodologies (scrum).
Environment: Informatica Developer 9.5.1, Unix, Oracle 10g, Teradata, Fixed width files, TPT, TPT script, TOAD, Harvest (SCM)Windows XP and MS Office Suite, Teradata 14.0 (FastLoad, MultiLoad, FastExport, BTEQ), Teradata SQL Assistant.
Sr. ETL Developer
Confidential, West Chester, PA
- Understand the Requirements and Functionality of the application from specs
- Involved in the development of Informatica mappings and tuned existing mappings for better Performance.
- Used the DataStage Designer to develop processes for extracting, cleansing, transforming, integrating and loading data into staging table.
- Develop required Unix shell scripts to automate ETL processes.
- Responsible for testing the mappings and ensure that the mappings do the transformation as proposed.
- Develop Check list & SOP's of all developed processes in HP required templates.
- Developed Mapplets and Worklets for reusability.
- Created mappings using different transformations like Source Qualifier, Joiner, Aggregator, Expression, Filter, Router, Lookup, Update Strategy, data masking and Sequence Generator etc.
- Involved in Unit Testing, create test cases to check whether the data loads into target.
- Prepared technical specifications for the development of Informatica (ETL) mappings to load data into various target tables and defining ETL standards.
- Scheduling daily, weekly and monthly loads for the managers according to the business specs.
- Monitoring the DataStage job on daily basis by running the UNIX shell script and made a force start whenever job fails.
- Develop workflows, sessions used command task in workflow manager.
- Involved in The ETL process is handled by defining and deploying mappings. OWB gives the user a highly graphical and easy-to-use interface.
- Configure the Driving tables according to the variation of data and maintain CSV documents as per that.
- Implemented Performance tuning in Mappings and Sessions by identifying the bottlenecks and Implemented effective transformation Logic.
- Worked with Metadata Definitions, Import and Export of Datastage jobs using Data Stage manager.
- Effectively communicated with Informatica Support team and BAs on Informatica bugs & issues.
Environment: Informatica Power center 7.1.2, Oracle 9i, Erwin r4, XML File, Flat files, SQL, PL/SQL, TOAD 8.0, Autosys 4.0, Windows 2000 and UNIX.
Sr.ETL/Data warehouse Developer
Confidential - San Antonio,TX
- Writing complex SQL Queries, Stored Procedures, Triggers, User Defined Functions to implement the business logic.
- Wrote numerous BTEQ scripts to run complex queries on the Teradata database. Used volatile table and derived queries for breaking up complex queries into simpler queries.
- Responsible to build new and modify existing adhoc reports using SSRS as per requirements.
- Used Teradata utilities like for Teradata ETL processing huge volumes of data throughput Fast Load, Fast Export, MultiLoad, Tpump and also Involved in implementation and batch monitoring
- Responsible for deploying the SSIS Packages from development to production server..
- Created UML Diagrams including Use Cases Diagrams, Activity Diagrams/State Chart Diagrams, Sequence Diagrams, Collaboration Diagrams and Deployment Diagrams and Entity Relation(ER) Diagrams.
- Worked on Teradata SQL, BTEQ, MLoad, FastLoad, and FastExport for Ad-hoc queries, and build UNIX shell script to perform ETL interfaces BTEQ, FastLoad or FastExport. Created numerous Volatile, Global, Set, MultiSet tables.
- Involved in Data analysis, Data model designs and Preparation of metadata documents for all source tables.
- Captured the DQ metrics using the Profiles and Created scorecards to review data quality using IDQ
- Used various Transformations, Dataflow and Control Flow, Implemented Event Handlers and Error Handling in SSIS.
- Involved in giving Quick resolutions and design changes when there was a requirements changes.
- Created action filters, parameters and calculated sets for preparing dashboards and worksheets in Tableau.
- Involved in tracking and reviewing the defects in all phases of the project using HP Quality centre
- Assisted in troubleshooting the Production support problems, which are related with Teradata database and Informatica Data Quality.
- Experience in creating the Profiles, Scorecards and add the custom Rules and Filters in Informatica Analyst and Developer Tools.
- Involved in analysis process of low level designs and provided design documents
- Involved in tracking and reviewing the defects in all phases of the project using Clear Quest
- Developed data mapping document and development of Group Ordering extracts - Order Lines
- Packaging of the developed component to be deployed in production environment using Change man package (Enhancements) Fixing production bugs. Temporary and permanent fix within Application Service Level Agreement
Environment: Teradata14, Teradata 14.10, Tableau 8.1, Informatica, SQL Assistant 13.0, SQL Server 2014, SSIS, BTEQ, IDQ 9.6.1, FastLoad, FastExport, Multiload, Tpump, MS Visio, View Point, Data Mover.
- Informatica 9.5 been used to load the data from Oracle to Netezza tables.
- Used Jira and confluence as project tacking tool, where we used to have all the tasks built and updated.
- Confluence has used to upload all the documents and decision tasks in confluence.
- Played a main role in creating all the templates to create different status pages add our tasks and track them in Jira.
- As this project falls under Waterfall methodology, still we used to have daily scrum meetings and I took lead to conduct this daily standup meetings.
- As this project has very small dead line and has a high priority because the contract is going to be end by Dec-2016 with E-bay, worked hard as a team by not letting the work goes into risk.
- Prepared all the designing documents consist of Data flow from Source to Target by mapping all the columns, which are required for reporting purpose.
- Handled the complex mappings by modifying some of the core tables which consist of Confidential customer data and also the sales tables that are involved in Batch load.
- Created different (Detailed and High level) Data Model diagrams to explain the flow of the data by using Data Modular tool called ERwin.
- Extensively worked understanding the Business requirements and designed the logic to populate the data as expected.
- Created DDL and DML scripts that have structure of new tables, and modifications of existing tables.
- Built Mappings, work lets and workflows to load the data into staging area, and then into DW tables.
- Used Push down Optimization to increase the Performance.
- Created Tidal jobs for automation of work Flows.
- Took Responsibility of creating the implementation plan document and worked closely with the admin's during go live.
- Provided One-month warranty support, which is a regular process in Confidential .
Environment: Informatica 9.1/9.5, SQL Server, Oracle, Netezza, Tidal.
Confidential - Houston, TX
- Transferring data from various sources like MS Excel, Flat File and SQL Server using SSIS and then creating reports with this data using SSRS
- Created complex Stored Procedures, Functions, Indexes, Tables, Views, SQL joins and other T-SQL.
- Based on the required reports, identified various data sources and established the connections using SSIS and constructed data source view.
- Transformations (Data Conversion, Export Column, Merge join, Sort, Union all, Conditional Split and more) for existing/new packages and huge CSV file import to Sale Force from different data sources and other ongoing tasks for mapping.
- Created SQL Server Reports using SSRS
- Deployment of the reports on to the PRODUCTION server for the end users.