- 7 years of Data Warehousing experience in the areas of ETL Design and Development.
- Involved in complete Software development life-cycle of various projects, including Requirements gathering, System Designing, Data modeling, ETL design, Development, Production Enhancements, Support and Maintenance.
- Extensive ETL tool experience in designing and developing jobs using IBM Infosphere/Websphere DataStage and Ascential DataStage. Proficient with DataStage tools like DataStage Designer, DataStage Director and DataStage Administrator.
- Familiar in using highly scalable parallel processing infrastructure using parallel jobs and multiple node configuration files.
- Extensively developed parallel jobs using various processing category stages like Aggregator, Sort, Remove Duplicate, Transformer, Filter, Change Data Capture (CDC), Join, Lookup, Merge, Funnel etc.
- Experience in troubleshooting of jobs and addressing production issues like data issues, performance tuning and enhancements.
- Strong understanding of the principles of Data Warehousing, using fact tables, dimension tables and star/snowflake schema modeling and implementing SCD’s (Slowly Changing Dimensions)
- Worked extensively with Dimensional modeling, Data migration, Data cleansing, ETL Processes for data warehouses.
- Knowledge in using Erwin as leading Data modeling tool for logical (LDM) and physical data model (PDM).
- Technical and analytical skills with clear understanding of design goals of ER modeling for OLTP and dimension modeling for OLAP.
- Extensive knowledge in dealing with various databases like Oracle 10g/9i/8i, DB2, SQL Server, Teradata, and MS Access.
- Experience in scheduling server and parallel jobs using DataStage Director, UNIX scripts and scheduling tools like Autosys.
- Experience in writing Stored Procedures, Functions and Triggers using PL/SQL.
- Experience in using Clearcase tool for migration of source code and performing version control.
- Knowledge in reporting tools such as Cognos and Microstrategy
- Excellent Interpersonal and communication skills with an ability to work effectively in a team or individually, remain highly focused and self-assured in fast-paced and high-pressure environments.
ETL Tools :IBM Infosphere DataStage 8.5, IBM Websphere DataStage 8.0.1 (Designer, Director,Administrator),Ascential DataStage 7.5.2 (Designer, Director, Administrator, Manager),alend open studio v.5.2.2
Databases :Oracle 10g/9i/8i, IBM DB2/UDB, Teradata, SQL Server 2003/2005/2008.
Data Warehousing :Star & Snow-Flake schema Modeling, Fact and Dimensions, Physical and Logical Data Modeling, ERwin.
Tools :Autosys, Clearcase, SQl Plus, SQL*Loader, TOAD
Languages/Scripting :SQL, UNIX Shell scripting
Confidential, Charlotte, NC
ETL Datastage Developer
Due to established process requirements and the segregated Bank and Brokerage operational infrastructures, transferring money between TIAA Direct and Confidential Brokerage accounts is a very slow, paper-based and manually-intensive process for customers consistent with funding from a competitor bank. The purpose of this project is to develop a new electronic funds transfer process between bank and brokerage so the Bank and brokerage accounts are immediately available to customers for online transfer.
- Understood the existing status of the project, data model and mapping document
- Interacted with data modeler, ETL developers, QA team to quickly get acquainted with process and standards of the project.
- Used technical specifications document to design and develop the ETL jobs using Data stage designer.
- Extensively used Change Data Capture, Transformer, and Modify, copy, Join, Funnel, Aggregator, lookup Stages to develop the parallel jobs and performed unit testing.
- Worked with Business users to fix the defects raised during UAT.
- Worked on creating the release instructions to deploy the code in production
- Worked on creating scheduling instructions document to automate the job run process.
- Knowledge transfer to support team to support the project in production.
Environment: IBM Web Sphere DataStage 8.7, Oracle 11g, DB2/AIX64 9.7.2, Teradata, Autosys.
Confidential, Hoffman Estates, IL
ETL Datastage Developer
Description:Sears Holding Corporation is a leading broad line retailer that provides merchandise and related services. ECOM project deals with the ecommerce business of the Sears Holding Corporation. As part of the project we also providing pricing feeds to the browse and search teams. I also worked on many sub projects like Associate commissions, VCS classifications, marketing feeds etc. Sears ecommerce team is migrating production code to New ETL platform called talend open studio from datastage.
- Analyzed the present data warehouse data model and participated in investigation of data subject areas and document data flows.
- Interacted with data modelers and data base administrators to make modifications in the data model according to the requirements.
- Used technical specifications document to design and develop the ETL jobs. Also modified the technical documents to accommodate the changes in specs.
- Designed, developed and tested the Data stage jobs using Designer and director based on business user requirements and business rules to load data from heterogeneous data sources like Sybase, Oracle 9i, text files and MS SQL Server.
- Developed Data Stage Jobs using Transformer, Aggregator, lookup, stored procedure stage, Join, merge, sort stages.
- Developed several Parallel jobs to improve performance by reducing the runtime for several jobs.
- Extensively used SQL coding for overriding the generated SQL in DataStage and also tested the data loading into the database.
- Used DataStage director to monitor the logs.
- Used DataStage version control to version the designed datastage jobs.
- Created DataStage sequencers using Job Activity, triggers, routine activity, execute command, notification activities to ensure the sequential run of all the designed jobs.
- Involved in manual testing of the designed jobs, UAT, integration testing and production process.
- Used Jira for defect management and version control of the created procedures and UNIX scripts.
- Developed UNIX shell scripts to automate file manipulation and data loading process.
Environment: IBM Web Sphere DataStage 8.7, Oracle 10g, DB2/AIX64 9.7.2, Teradata, CONTROL-M
Confidential, Conshohocken, PA
ETL Datastage Developer
Confidential is the largest and fastest growing bridal retailer in the world, with over 300 stores in the U.S. and two stores in Canada. They offer complete Wedding Solutions and also for any other Special Ocassion.
- Designed the ETL jobs using IBM WebSphere Information Server 8.1 to Extract, Transform and load the data into Staging and then into Oracle Database.
- Extensively used the designer to develop various parallel jobs to extract, transform, integrate and load the data into Corporate Data Warehouse (CDW).
- Designed and developed the ETL jobs using Parallel Edition which distributed the incoming data concurrently across all the processors, to achieve the best performance.
- Designed parallel jobs using stages such as Join, Merge, Lookup, Remove Duplicates, Copy, Filter, Funnel, Dataset, Lookup, Pivot, and Sort, Surrogate key Generator, Change Data Capture (CDC), Modify, Row Generator and Aggregator.
- Scheduled jobs using DataStage Job Sequences and AutoSys job scheduler utility based on the control flow and monitored the production jobs closely for any possible errors.
- Worked with Shared Containers for Re-using the Business functionality.
- Extensively developed and deployed UNIX Shell scripts that provide parameter values to DataStage jobs during runtime.
- Created Parameter Sets and Value Files to run the same job for different schemas.
- Worked with Slowly Changing dimension stage for implementing Type-II logic.
- Used Datastage Director to monitor jobs, run and validating its components.
- Involved in the Performance Tuning of the DataStage jobs using different methodologies like Configurations of the environment variable file, designing and editing configurations, increasing the reading as well as the writing speed while fetching or loading data to files or databases.
- Migrated projects through DSX executable files from Development to Testing environment and Production environments
- Assisted operation support team for transactional data loads in developing SQL & Unix scripts
- Involved in Unit testing and Integration testing of DataStage jobs.
Environment: IBM InfoSphere DataStage 8.1, IBM AIX 5.2, Oracle 10g, Flat files, Autosys, TOAD, SQL loader.
Confidential, Wayne, NJ
Data Stage Developer
Confidential is one of the nation’s leading environmental companies, providing water and wastewater services to approximately 5.7 million people in the United States. In addition to owning and operating 20 water utilities, Confidential operates 100 municipal and industrial water and wastewater systems through innovative public-private partnerships and contract agreements.
- Designed and developed various new processes, Fixed the existing process with new business requirements, and meetings with users for Input.
- Designed and developed jobs for extracting, transforming, integrating, and loading data into data mart using DataStage Designer
- Developed, executed, monitored and validated the ETL DataStage jobs in the DataStage designer and Director Components.
- Worked with DataStage Director to schedule, monitor, analyze performance of individual stages and run DataStage jobs.
- Worked on Teradata database, used Teradata Enterprise stage and Teradata Multiload stage.
- Involved in writing scripts for loading data to target data warehouse for Bteq, fastload, and multiload.
- Extensively used Change Data Capture, Transformer, and Modify, copy, Join, Funnel, Aggregator, Lookup Stages and development stages to develop the parallel jobs.
- Generated Surrogate Keys for composite attributes while loading the data into Data Warehouse using Key Management functions.
- Developed user defined Routines and Transformations for implementing Complex business logic.
- Developed Job Sequencer and batches and have edited the job control to have jobs run in sequence
- Imported Metadata from Oracle database. Imported metadata definitions into the repository. Exported and imported DataStage components.
- Involved in the preparation of ETL documentation by following the business rule, procedures and naming conventions
- Performed Troubleshooting and Tuning of DataStage Jobs for better query performance.
- Reviewing the developed jobs based on the build review checklists.
- Responsible for Unit, System and Integration testing. Developed Test scripts, Test plan and Test Data.
Environment: IBM Infosphere DataStage 8.1(Parallel Extender, Quality Stage), PVCS Version Controller, Citrix, Mercury Test Director, Control-M Scheduling, Oracle, Teradata and UNIX.
Confidential, Newark, NJ
Confidential companies serve individual and institutional customers worldwide and include The Confidential Company of America, one of the largest life insurance companies in the U.S. These companies offer a variety of products and services, including mutual funds, annuities, real estate brokerage franchises, relocation services, and more.
- Attended the user review meetings.to gather requirements and documented them.
- Designed and created DataStage Server jobs to load data from sequential files, Flat Files and MS Access.
- Used DataStage Manager for importing metadata from repository, new job categories and creating new data elements.
- Prepared Data Volume estimates.
- Defined the data definitions, and created the target tables in the database.
- Wrote routines to schedule batch jobs to obtain data overnight from various locations.
- Mapped the source and target databases by studying the specifications and analyzing the required transforms.
- Analyzed the performance of the jobs and enhanced the performance using the standard techniques.
- Used the DataStage Director and its run-time engine to schedule running the solution, testing and debugging its components, and monitoring the resulting executable versions.
- Used DataStage to transform the data to multiple stages, and prepared documentation.
- Created ETL execution scripts for automating jobs.
- Documented the purpose of mapping so as to facilitate the personnel to understand the process and incorporate the changes as and when necessary.
Environment: Ascential DataStage (Designer, Director, Manager), Windows NT 4.0, UNIX, Oracle 8i, PL/SQL, Dbase3 Files, DOS and UNIX Sequential Files, MS Access.