Sr. Etl Developer Resume
CA
SUMMARY
- Over 9 + years of experience in System Analysis, Design and Development In the fields of Data Warehousing, Data Integration, Data Migration and Developing Client server application.
- 8 years of strong experience on Extraction, Transformation and Loading (ETL) processes from various sources into Data Warehouse/Data Marts using Confidential InfoSphere Information Server 8.7/8.5/8.1 (Quality Stage, Data Stage, FastTrack and Information Analyzer,Metadata Workbench) 8.7/8.1/7.5.2/6. x/5.2 using components such as Datastage Designer, Datastage Manager, Datastage Director and Datastage Administrator.
- Confidential Certified Solution Developer - - Web sphere Data Stage Enterprise Edition V8.0.1
- Experience in Data profiling with the Information analyzer.
- Strong experience in designing of Server jobs, Parallel jobs, Job Sequencers and Batch Jobs.
- Experience in Data Modeling, Star Schema/Snowflake Schema modeling, FACT & Dimensions tables, as well as reverse engineering, using tools Oracle Designer, Erwin.
- Worked on Mapping Specifications using Fast Track - Foundation Tools from Confidential InfoSphere Information Server
- Experience in designing jobs by using XML stages and loading XML Schema Definitions (XSD).
- Resolving bottlenecks while developing the Source-Target Mapping for the Dimensions and Fact Tables.
- Extensively used Parallel Extender to load data into data warehouse with different techniques like Pipeline and Partition in MPP environment.
- Experience in UNIX Shell scripting as part of file manipulation, and have strong knowledge in scheduling Data Stage jobs using Control-M as well as familiarity with Crontab and Autosys.
- Hands on experience in writing, testing and implementation of the Triggers, Procedures, functions at Database level using PL/SQL.
- Experience in Performance Tuning and Optimization of Parallel Jobs and Server Jobs.
- Involved in Creating and verifying quality stage jobs for Match and un duplication
- Experience in integration of various data sources like Oracle, SQL Server, Sybase, Teradata, and DB2/UDB
- Having experience in using written Stored Procedures in Data Stage jobs.
- Hands on experience in SQL *Loader for data migration from Legacy system.
- Strong knowledge in OLTP/OLAP Systems and ODS models.
- Experience working on multiple platforms such as Windows NT/2000, UNIX (Sun Solaris, AIX, HP).
- Possess strong ability to quickly adapt to new applications and having Excellent interpersonal and communication skills.
- Able to Work under pressure to meet job requirements and deadlines highly driven and self-motivated with sound business judgment and strong work ethics.
TECHNICAL SKILLS
ETL: Confidential Info sphere Information server 8.7/8.1/8.0.1 , Data stage EE 7.5.2/7.5.1/7.0 ( Confidential Web sphere), Quality stage, Information Analyzer
Database/RDBMS: Oracle 11g/10g/9i/8i, SQL Server 2000,DB2/UDB, Teradata
Languages: SQL, PL/SQL, Unix Shell scripting
Tools: DB Visualizer 9.2.4, Confidential data studio version 3.1.1,Toad, Brio Query Explorer 6.5.2 Erwin, Visio
OLAP/Reporting: Business Objects X1,Cognos
Database/RDBMS: DB2/UDB, Oracle, SQL Server 2000, Teradata
Web Technology: HTML, XML
Methodologies: Dimensional Modeling, E-R Modeling
Environment: Confidential UNIX AIX 4.2, LINUX, Windows 2000/NT/XP, MS-DOS
PROFESSIONAL EXPERIENCE
Confidential, CA
Sr. ETL Developer
Responsibilities:
- Worked on multiple projects in all the student and Employee subject areas.
- Involved with Business users and ETL Leads from different teams to implement ETL Frame Work using DataStage Server/PX combination of jobs.
- Involved in understanding the Business Process and Coordinating with Business users, Data Modelers and DB A's to create Dimension and Fact tables based on the existing Data Warehouse design.
- Implemented various strategies for Slowly Changing Dimensions using DataStage Server/PX jobs.
- Sourced data from various sources like DB2 UDB, XML Files,Flat Files, and CSV files and loaded to target DB2 UDB (Corporate Data Warehouse).
- Designed jobs using different parallel job stages such as Join, Merge, Lookup, Remove Duplicates, Filter, Dataset, Lookup File Set, Change Data Capture, Switch, Modify, and Aggregator.
- Worked on Real time stages (XML i/o stages)
- Involved in creating XSD and extracted data in to XML
- Involved in developing DataStage Designer- Server and PX jobs for Extracting, Cleansing, Transforming, and Integrating /Loading Data into DSS Data Warehouse.
- Developed Job Sequencers with restart capability for the designed jobs using Job Activity, Exec Command, E-Mail Notification Activities and Triggers.
- Extensively designed, developed and implemented Parallel Extender jobs using Parallel Processing (Pipeline and partition parallelism), Restart ability techniques to improve job performance while working with bulk data sources.
- Extensively used DataStage PX Plug-ins like Data Set, File Set, Lookup File Set, Aggregator, Filter, Funnel, FTP, Join, Merge, Lookup, Modify, Sort, Transformer, DB2 Enterprise, DB2 API and Sybase OC stages.
- Extensively used SCP techniques to transfer files in between DS servers.
- Extensively used DataStage Director to Monitor and check the run statistics of the Jobs.
- Extensively used DataStage- Manager to Export/import DataStage components.
- Performed SQL tuning techniques to improve the database read performance through DataStage Jobs and used Frame Work approach to improve transformation and loading steps.
- Worked closely with Data Quality Analysts and Business Users for data accuracy and consistency after table loads.
Environment: InfoSphere DataStage and QualityStage 8.7/8.5/8.1, DB2, Sybase, LINUX, BO XI 3.0, DbVisualizer 8.0
Confidential - Charlotte, NC
Datastage Developer
Responsibilities:
- Involved in the design and development of Data Warehouse
- Involved in jobs and analyzing scope of application, defining relationship within & between groups of data, star schema, snow flake etc.
- Worked closely with database administrator to understand the business process of the data warehouse model.
- Extensively worked with DB2 source systems.
- Involved in writing UNIX shell Scripts, C shell programming for file validation and scheduling Data Stage jobs.
- Worked closely with database architect during the design and development of ETL technical specification document
- Developed Data Stage jobs for extraction transformation and loading of the data from source systems
- Extensively worked with Parallel Extender using Parallel Processing (Pipeline and partition parallelism) techniques to improve job performance while working with bulk data sources
- Extensively used Sequential, Hashed, Join, Merge, Look up, Transformer , Aggregator and DB2 UDB Stages during ETL development
- Written routines in c++ in order to perform the transformations.
- Having experience in designing jobs by using XML stages
- Having experience in loading XML Schema Definitions (XSD).
- Experience in using PL/SQL Functions, stored procedures to load the data into Data marts.
- Used Data Stage Designer to develop processes for extracting, cleansing, transforming, integrating and loading data into Data Warehouse database.
- Performed Import and Export of Data Stage components and table definitions.
- Used Data Stage Director to Run and Monitor the Jobs performed, automation of Job Control using Batch logic to execute and schedule various Data Stage jobs
- Performed the Unit testing for jobs developed to ensure that it meets the requirements and documented unit test plan and test case scenarios of developed code.
- Documented the Data Warehouse development process and performed knowledge transfer to Business Intelligence developer
Environment: DataStage 8.1/7.5.2, Quality stage, DB2/UDB, Oracle9i/10g, Windows XP, XML, XSD, Confidential AIX, Brio Query Explorer 6.5.2.1,SQL,PL/SQL, C shell, UNIX shell scripts.
Confidential, Nashville, TN
Data Stage Developer
Responsibilities:
- Involved in understanding the scope of application, present schema, data model and defining relationship within and between groups of data.
- Followed a structured approach for gathering and formalizing business requirements expressed by managers, Business owners and operational users of the information systems.
- Developed data marts based on business requirements and developed ETL logic to process the data by using data stage 8.0.1.
- Developed Shell scripts C/C++ that controls the process of running data stage jobs.
- Extensively worked on the source data from Flat file, Oracle source, and DB2 source systems.
- Extensively used CFF stage to handle mainframes Data source that extracts data from a flat file containing complex data structures, such as arrays, groups, and redefines.
- Handling Huge data in various kinds of formats like EBCDIC/BINARY, Variable length of records, Unusual characters, Multiform at file handling with external source (Mainframe to db2)
- Worked on Parallel Job Stages like Sequential file, Complex Flat file, Stored Procedure, Dataset/File set, Lookup File set, Aggregator, Join, Merge, Filter, Lookup, XML I/O stages, Head, Tail and Pivot.
- Developed parallel(c++) routines which creates files dynamically.
- Designed Job Sequencers to run in a loop based on the success or failure of individual jobs in the Sequencer.
- Implemented shared containers for multiple jobs, which have the same business logic.
- Experience in designing jobs by using XML stages
- Loaded XML files by using XSD parallel jobs.
- Importing XSD’s using xml metadata importer.
- Designed staging and Error handling tables keeping in view the overall ETL strategy.
- Used the Data stage Director extensively to run, schedule, monitor, debug and test the application on development, and to obtain the performance statistics.
- Used Data Stage Designer for importing metadata from repository, new job categories and creating new data elements.
- Defined PL/SQL stored procedures, functions to load the data from staging source to Confidential data warehouse tables and for business validations.
- Staged the data coming from source database on the server using Hash/Sequential files and used aggregator stage for optimum performance also for data recovery in case job aborts.
- Enhanced Unix Shell Scripts to Automate the Loading Process using Autosys.
- Developed SQL scripts to test and validate the information from the source to Confidential database.
- Analyzed the given source Dimensions and Confidential Fact table structures to develop Surrogate Key tables referencing required dimensions.
- Documented the developed code for promotion to production environment.
Environment: Data stage Enterprise Edition 8.0.1 SQL, PL/SQL, XML, XSD, Oracle 10g, DB2, TOAD 7.3, Confidential AIX 5.2.,crystal reporting 9, C/C++, UNIX shell scripts, Business objects.
Confidential, NY
ETL / Data Warehouse Analyst
Responsibilities:
- Involved with Business users and ETL Leads from different teams to implement ETL Frame Work using DataStage Server/PX combination of jobs.
- Involved in understanding the Business Process and Coordinating with Business users, Data Modelers and DB A's to create Dimension and Fact tables based on the existing Data Warehouse design.
- Implemented various strategies for Slowly Changing Dimensions using DataStage Server/PX jobs using the Frame Work approach.
- Sourced data from various sources like DB2 UDB, Flat Files, and CSV files and loaded to target DB2 UDB (Corporate Data Warehouse).
- Designed jobs using different parallel job stages such as Join, Merge, Lookup, Remove Duplicates, Filter, Dataset, Lookup File Set, Change Data Capture, Switch, Modify, and Aggregator.
- Worked on Real time stages (XML i/o stages)
- Involved in creating XSD and extracted data in to XML
- Involved in developing DataStage Designer- Server and PX jobs for Extracting, Cleansing, Transforming, and Integrating /Loading Data into Corporate Data Warehouse (CDW).
- Developed Job Sequencers with restart capability for the designed jobs using Job Activity, Exec Command, E-Mail Notification Activities and Triggers.
- Extensively designed, developed and implemented Parallel Extender jobs using Parallel Processing (Pipeline and partition parallelism), Restart ability techniques to improve job performance while working with bulk data sources.
- Extensively used DataStage PX Plug-ins like Data Set, File Set, Lookup File Set, Aggregator, Filter, Funnel, FTP, Join, Merge, Lookup, Modify, Sort, Transformer, DB2 Enterprise, DB2 API and Sybase OC stages.
- Extensively used DataStage Director to Monitor and check the run statistics of the Jobs.
- Extensively used DataStage- Manager to Export/import DataStage components.
- Performed SQL tuning techniques to improve the database read performance through DataStage Jobs and used Frame Work approach to improve transformation and loading steps.
- Worked closely with Data Quality Analysts and Business Users for data accuracy and consistency after table loads.
- Developed reports based on user requirements for fixed and Ad-Hoc requests using Business objects.
- Involved in documenting the Frame Work Templates and the process of developing jobs using Templates.
Environment: Ascential DataStage7.5.2, DB2 UDB 8.1.6(EEE), Confidential DB2 Mainframe, XML, XSD, Sybase Server, DB2 SQL, UNIX, Windows NT 4.0/2000, Sun Solaris 2.8, Confidential AIX 5.2, BO 6.5.
Confidential, Hartford, CT
ETL Developer
Responsibilities:
- Used DataStage as an ETL tool to extract data from sources systems to Target system.
- Data warehouse was implemented using sequential files from Mainframe System, Oracle, and DB2 UDB.
- Designed complex DataStage mappings between sources (external files and databases) to Operational staging targets using Oracle as the target database in Data Warehouse.
- Used DataStage Designer to develop jobs for extracting, cleansing, transforming, integrating, and loading data into data warehouse database.
- Developed several transformations in the process of building Data warehouse database.
- Worked on retrieving data from different legacy systems such as Oracle, DB2.
- Used Lookup transformations to manipulate the information.
- Worked on c/c++ shell scrips to run the datastage jobs.
- Used DataStage Manager for importing metadata from repository, new job categories and creating new data elements.
- Worked on Universe Database to create Hash Files, Intermediate files while ETL process.
- Interacted with User Groups/Corporate Testing Groups/Business people.
- Developed shell scripts to automate file manipulation and data loading procedures.
Environment: Ascential DataStage 7.0, Oracle 8i, DB2 UDB, PL/SQL, Lotus Notes, C/C++, Windows NT, UNIX AIX.