- 9 years of progressive experience in the IT industry in designing and developing Data Warehouse applications using ETL and Business Intelligence tools like Informatica PowerCenter 9.6, 9.5, 9.1, 8.x, SAP Power Designer and Teradata.
- Good experience in Relational Databases like Oracle, Teradata, MS SQL Server and DB2.
- Experienced in tuning Informatica mappings to identify and remove processing bottlenecks.
- Automation and scheduling of UNIX shell scripts and Informatica sessions and batches using Tivoli Workload Scheduler (TWS).
- Experience in migrating the environment from Informatica 8.x to Informatica 9.x.
- Hands on experience using query tools like TOAD, SQL Developer, SQL Server Management Studio and Teradata SQL Assistant
- Wrote, tested and implemented Teradata Multiload and Bteq scripts, DML and DDL.
- Designed and developed complex mappings from various transformations like Unconnected/Connected Lookups, Router, Filter, Expression, Aggregator, Joiner, Update Strategy, Stored Procedures, Normalizer, XML and Web Services.
- Experience in Dimensional Data Modeling using Star, Snowflake Schema, Fact & Dimensions tables, Physical & Logical Data Modeling using Erwin & SAP Power Designer.
- Proficiency in Data Warehousing techniques for Data Cleansing, Slowly Changing Dimension (type 1, 2 & 3).
- Exposure in other OLAP tools like Business Objects, SSRS for developing Reports
- Good work experience in Unit testing, Functional, Performance and User Acceptance Testing.
- Strong knowledge of Software Development Life Cycle (SDLC) of Data warehousing, Decision Support System.
- Worked in entire life - cycle beginning from Requirements gathering, Data Model Design, ETL Design, Report Design, Testing, and Migration to Production and Post Production Support.
- Experience working in WaterFall and Agile methodologies for software development
- Excellent written and verbal communication skills with a good track record of working individually as well as in groups.
ETL Tools: Informatica PowerCenter 9.x/8.x/7.x, OLAP, OLTP, Data Profiling, Data Migration and Data Cleansing
Databases: Oracle 12c/11g/10g/9i/8i, SQL Server 2016, DB2, Teradata
Reporting Tools: Business Objects, Siebel Analytics 7.8.2/7.7.1, SSRS, Cognos 8.0
Languages: C, C++, SQL, PL/SQL, Unix Shell Script, XML
Data Modeling: ERWIN, SAP PowerDesigner
Operating Systems: Windows NT/2000/XP/Vista/7, UNIX (SunSolaris10, HP, AIX Linux), MS Visio
Scheduler: Autosys, TWS 8.5/8.2, JSC
SQL Editor: Toad for Oracle, Teradata SQL Assistant, Hue, Oracle SQL Developer, SQL Server Management Studio
Confidential, Boston, MA
- Understanding data models and relationships between data files/tables
- Handling multiple source systems & data types like flat files and relational tables
- Understanding business requirements and translate into ETL design
- Creating Informatica ETL components and translate based on the requirements
- Developing mappings/Transformations/mapplets by using Mapping Designer, Transformation Developer and Mapplet Designer in Informatica PowerCenter.
- Performing data validation, IBC and data testing
- Debugging to identify defects and resolution
- Interacting with various Business users, architects, DBAs, test leads and offshore resources
- Creating ETL job schedules and dependencies using scheduling tools (Tivoli)
- Working on Unix scrips for scheduling Informatica jobs
Environment: SQL Server Management Studio 13.0, Microsoft Outlook 2016, WinSCP, HP ALM Quality Center 12.20, Microsoft SQL Server 2016, Informatica PowerCenter 9.6.1, Profisee Desktop 6, Citrix Receiver, PuTTY for UNIX, ServiceNow, Master Data Services 2016, IBM Workload Scheduler 9.4, Toad for SQL Server 6.7
Confidential, Bothell, Washington
Data Modeler/ ETL Developer
- Designing data layer schema to enable EDW and business intelligence analytics
- Working with business to understand requirements/business problems and provide technical design solutions and recommendations
- Performing Source System data profiling and data architecture
- Converting Use Cases and RRQs into dimensional model for the reporting system by identifying required dimensions and facts
- Designed and developed tables, transformation logic and processes for implementing business rules and standardization of source data from multiple systems into target IDW
- Designing Conceptual, Logical and Physical Data models as per requirements
- Built the ETL source to Target (S2T) specification documents by understanding the business requirements
- Created PDM along with DDLs that best suits the requirements, from the LDM
- Working with ETL Lead and Business Analysts to ensure design integration and consistency across subject areas
- Created mappings using PowerCenter Designer and extracted data from various sources, transformed data according to the requirement
- Responsible for Data model design within Teradata and HIVE environments
- Participate in Data Architecture, Design and performance improvement
- Used Rally to plan User stories, add tasks, dependencies and acceptance criteria and participated in Sprint ceremonies like Sprint planning, Sprint review and Backlog grooming etc. for each Sprint.
Environment: SAP PowerDesigner 16.5, Informatica PowerCenter 9.1, Oracle SQL Developer 4.0, HP Application Life Cycle Management 12.0, Accurev 6.1.1, Teradata SQL Assistant 14.10, Microsoft Outlook 2013, Hue 2.6.1, Rally
Confidential, St. Louis, MO
- Involved in loading the business data from various relational sources or Flat files using Informatica and Teradata load tools.
- Development of ETL jobs through development and change requests.
- Performance tuning of existing ETL jobs.
- Developed TWS jobs (Teradata, Informatica jobs) and scheduled them which in turn call UNIX scripts and Informatica Workflows to give desired results.
- Unit Testing and planning of developed code including preparation of Unit Test case documents.
- Participated in activities which include Code Review and Peer Code Review.
- Involved in the project Runbooks preparation which are used for production support
- Responsible for maintaining and updating the BIDW Dashboard to reflect the current state of applications
- Used macro scripts in Teradata to fix job failures.
- Used Performance Alerts to keep clients or business informed about issues in applications or potential SLA misses for applications
- Checked file system disk space and performed clean up before reaching the optimal mount point size
- Used BMC Remedy Tool for implementing code changes and resolved tickets assigned to me.
- Used Connect Direct (C:D) scripts to transfer files from source to other destination servers
- Analyze and mitigate the impact to applications or databases while attending SIS Review calls
Environment: Informatica PowerCenter 8.6, Teradata SQL Assistant 13.10, Teradata Decision Expert 12.1, Teradata View Point 15.00, Teradata R13/R14, BTEQ, MLOAD, PuTTY for UNIX, TOAD for Oracle 11.6, Microsoft Outlook 2010, IBM Tivoli Workload Scheduler (TWS) 8.5/8.2, BMC Remedy 7.6
Confidential, Libertyville, IL
- Involved in business analysis and technical design sessions with business and technical staff to develop requirements document and ETL design specifications.
- Used Erwin for Logical and Physical database modeling of the warehouse, responsible for database schema creation based on the logical models.
- Designed and developed mappings, transformation logic and processes in Informatica for implementing business rules and standardization of source data from multiple systems into the data warehouse.
- Created mappings to load the data mart with the use of transformations like Aggregator, Filter, Router, Expression, Joiner, Sequence Generator and Update Strategy.
- Involved in performance tuning of targets, sources, mappings, and sessions.
- Wrote complex SQL scripts to avoid Informatica Lookups to improve the performance as the volume of the data was heavy.
- Worked on optimizing and tuning the Teradata views and SQL’s to improve the performance of batch and response time of data for users.
- Involved in creation of Technical specification document (Source to Target) for the project by conducting technical meetings.
- Created stored procedures to extract data from various data sources like Oracle and SAP.
- Used pmcmd to run workflows and used schedulers for scheduling of sessions.
- Created and monitored sessions using Workflow Manager and Workflow Monitor
Environment: Informatica PowerCenter 8.1, Oracle 10g, Teradata, SQL Assistant 12.0, SQL Server 2005, Windows XP, UNIX, SQL Developer, SQL, SSIS, Oracle SQL *Loader, Erwin 4.0, Trillium
Confidential, Houston, TX
- Understanding business needs and creating business requirement documents.
- Responsible for the development and support of a Data Warehouse using Informatica as the primary ETL Tool and Business Objects as reporting tool.
- Developed mappings/Reusable Objects/Transformations/mapplets by using Mapping Designer, Transformation Developer and Mapplet Designer in Informatica PowerCenter.
- Designed and developed mappings using Source Qualifier, Aggregator, Joiner, Lookup, Sequence Generator, Stored Procedure, Expression, Filter, Java and Rank transformations and validated the Data.
- Implemented and populated Slowly Changing Dimension (SCD) to maintain current information and history information in dimension tables.
- Used Informatica PowerCenter Workflow Manager to create sessions, batches to run with the logic embedded in the mappings.
- Developed PL/SQL and UNIX shell scripts for scheduling the sessions in Informatica.
- Created procedures to drop and recreate the indexes in the target Data warehouse before and after the sessions.
- Creating Business Objects reports according to BRD specifications.
- Analyzing and enhancing the existing BO Reports as per new requirements.
- Demonstrated complete follow-through on technical problems.
- Involved in Unit testing and User acceptance testing
Environment: Informatica PowerCenter 9.1, Oracle 11g, Flat Files, Win7, SQL * Plus, Toad.
Confidential, Boston, MA
ETL Informatica Developer
- Interacting with the end users to get all the incomplete requirements and developed client satisfied code.
- Performed Source System Data analysis as per the Business Requirement. Distributed data residing in heterogeneous data sources is consolidated onto target Enterprise Data Warehouse database.
- Developed Mappings, Sessions, Workflows and Shell Scripts to extract, validate, and transform data according to the business rules.
- Building the necessary staging tables and worktables on Oracle development environment.
- Sourced the data from XML files, Flat files, SQL Server tables and Oracle tables.
- Made substantial contribution in simplifying the development and maintenance of ETL by creating re-usable Mapplets and Transformation objects.
- Developed complex mappings in Informatica to load the data from source files using different transformations like Source Qualifier, Look up (connected and unconnected), Expression, Aggregator, Update Strategy, Joiner, Filter, Stored Procedure, Normalizer, Router and Mapplets.
- Developed mappings that extract data to Data Warehouse and monitored the Daily, Weekly, Monthly and Quarterly Loads.
- Created High level/detailed level design documents and involved in creating ETL functional and technical specification.
- Extensively worked in performance tuning of programs, ETL procedures and processes.
- Performed Unit, Systems and Regression Testing of the mappings. Involved in writing the Test Cases and assisted the users in performing UAT.
- Created integration services, repository services and migrated the repository objects.
- Used heterogeneous data sources like Oracle, DB2, XML Files and Flat Files as source, and imported stored procedures from Oracle for transformations.
Environment: Informatica PowerCenter 8.3, Oracle 10g, SQL, PL/SQL, Toad, Shell Scripts, UNIX (AIX).
Data Warehouse Consultant
- Involved in the requirement definition and analysis in support of Data Warehousing efforts.
- Created Repository using Repository Manager.
- Worked Extensively on Informatica tools -Repository Manager, Designer and Server Manager.
- Involved in Extraction, Transformation and Loading (ETL) processes.
- Created the Source and Target Definitions using Informatica PowerCenter Designer.
- Created and monitored Batches and Sessions using Informatica PowerCenter Server.
- Tuned the mappings to increase its efficiency and performance.
- Used Informatica Workflow Manager to create workflows
- Workflow Monitor was used to monitor and run workflows
Environment: Informatica PowerCenter 7.1, Oracle 8i, UNIX, Toad, SQL * Loader, Windows NT