Sr. Etl Datastage Developer Resume
Miami, FL
SUMMARY
- Over 7+ years of experience in Data modeling, Datawarehouse Design, Development and Testing using ETL and Data Migration life cycle using IBM WebSphere DataStage9/8.x/7.x
- Expertise in building Operational Data Store (ODS), Data Marts, and Decision Support Systems (DSS) using Multidimensional Model(Kimball and Inmon),Star and Snowflake schema design.
- Experience in analyzing the data generated by the business process, defining the granularity, source to target mapping of the data elements, creating Indexes and Aggregate tables for the data warehouse design and development.
- Data Processing experience in designing and implementing Data Mart applications, mainly transformation processes using ETL tool DataStage (Ver9/8.x/7), designing and developing jobs using DataStage Designer, Data Stage Manager, DataStage Director and DataStage Debugger.
- Efficient in all phases of the development lifecycle, coherent wif Data Cleansing, Data Conversion, Performance Tuning and System Testing.
- Excellent in using highly scalable parallel processing Infrastructure using DataStage Parallel Extender.
- Efficient in in corporation of various data sources such as Oracle, MS SQL Server, and DB2, Sybase, XML and Flat files into the staging area.
- Experience in Mapping Server/parallel Jobs in DataStage to populate tables in Data warehouse and Data marts.
- Proven track record in addressing production issues like performance tuning and enhancement.
- Excellent knowledge in creating and managing Conceptual, Logical and Physical Data Models.
- Experience in dimensional and relational database design.
- Expert in unit testing, system integration testing, implementation, maintenance and performance tuning.
- Experience in different Scheduling tools like AutoSys for automating and scheduling jobs run.
- Excellent wif PL/SQL, T - SQL, Stored Procedures, Database Triggers and SQL * Loader.
- Experience in UNIX Shell Scripting.
- Detail oriented wif good problem solving, organizational, analysis, highly motivated and adaptive wif the ability to grasp things quickly.
- Ability to work TEMPeffectively and efficiently in a team and individually wif excellent interpersonal, technical and communication skills.
TECHNICAL SKILLS
ETL/Reporting Tools: IBM Information Server V9/8.1(DataStage, QualityStage, Information Analyzer), Ascential DataStage V7.5 (Designer, Director, Manager, Parallel Extender), IBM Cognos 8.0
Databases: Oracle 8i/9i/10g, MS SQL Server 2005/2008, DB2 UDB, MS Access
Languages: SQL, PL/SQL, SQL*Plus, Flat files, Sequential files, TOAD 9.6, Erwin, Microsoft Visio, Oracle Developer 2000, SQL*Loader, C, C++
Scripting: IBM AIX UNIX, UNIX Shell Scripting, Windows NT,/XP, Macintosh, VB scripting.
PROFESSIONAL EXPERIENCE
Confidential, Miami, FL
Sr. ETL Datastage Developer
Responsibilities:
- Involved in system analysis and design of the Data warehouse
- Responsible for designing Data warehouse schemas using Erwin
- Developed Functional (Business terms) and Technical Design documents.
- Responsible for Requirement Gathering Analysis and End user Meetings.
- Worked closely wif data modeler and database administrator to understand the business process and participated in gathering of business requirements to load the data warehouse which is designed on the Star schema and RALPH KIMBALL methodology.
- Developed Slowly Changing Dimension Mappings for Type 1 SCD and Type 2 SCD
- Extensively worked wif Parallel Processing (Pipeline and partition parallelism) techniques to improve job performance while working wif bulk data sources.
- Extensively used Parallel Job Stages like StoredProcedure, Dataset/Fileset, Aggregator, Join, Transformer, Sort, Merge, Filter, Modify, Lookup, Funnel and Pivot.
- Worked on Head, Tail and Peek stages for debugging Datastage jobs.
- Used parameter sets for easy maintenance of project specific parameters and better functionality.
- Used Job Sequencer stages to link multiple jobs in Series/Parallel based on the requirement
- Used partition key in Parallel Extender job for bulk loading of data and to get better performance.
- Used the Datastage Designer to develop processes for extracting, cleansing, transforms, integrating and loading data into data warehouse database.
- 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 (on an ad hoc or scheduled basis).
- Implemented shared containers for multiple jobs, which have the same business logic.
- Strictly followed the change control methodologies while deploying the code from DEV, QA and Production.
- Responsible for Formulating the DW process to load from sources to Target tables.
- Developed Data Stage jobs where in using data stage, data from different sources formatted, cleaned, summarized, aggregated and transform into data warehouse
- Used the Datastage Designer to develop various jobs processes for extracting, cleansing, transforming, integrating, and loading data into data warehouse database.
- Designed and implemented slowly changing dimensions (SCD’s) methodologies
- Designed job sequences for running jobs.
- Responsible for integrating the data warehouse on both Windows and UNIX platforms
- Interaction wif end-users and business analysts to identify and develop business requirements and transform it into technical requirements and ultimately will be responsible for delivering the solution
Environment: IBM InfoSphereDataStage 9.0, Oracle 10G, SQL, DTS, AIX 5.1, Cognos,Autosys, UNIX Shell Scripting, Flat files, XML files
Confidential, MC LEAN, VA
Sr. ETL DataStage Developer
Responsibilities:
- Designed the ETL jobs using IBM WebSphere Information Server 8.5 to Extract, Transform and load the data into Staging and tan 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 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.
- Developed Server jobs using various types of stages like Sequential file, DB2-API, DB2-Enterprise, Aggregator, Transformer, Sort and XML.
- Created Job Parameters and Environment variables to run the same job for different schemas.
- Created Technical Design Documents and Mapping documents based on the BRS provided by the analysts for the ETL jobs developed.
- Used to do code reviews for all the jobs before moving to production.
- Used ETL Job Control to run the jobs.
- Used Director to monitoring jobs, run and validating its components.
- Migrated projects from development to QA to Production environments
- Performed the Integration and System testing on the ETL application.
- Created all necessary documents like Migration form, Urgency form, Implementation form etc. to deploy the code to higher environments.
- Created shell scripts to invoke Datastage jobs, pre/post processing files and schedule using autosys scheduling tool.
- Created Autosys JIL’s - file watcher, box job, command jobs to automate the ETL process.
- Assisted operation support team for transactional data loads in developing SQL & Unix scripts
- Created Error Files and Log Tables containing data wif discrepancies to analyze and re-process the data.
- Participated in Unit testing and Integration testing of DataStage jobs.
Environment: IBM InfoSphereDataStage 8.5, Oracle 10g, SQL Server 2008, DB2-UDB 9.7, Sybase, Autosys, UNIX Shell Scripting, Flat files, XML files, Micro Strategy, IBM Rational ClearCase, IBM Rational ClearQuest, SAS.
Confidential, Newark
DataStage Developer
Responsibilities:
- Interacted wif End user community to understand the business requirements and in identifying data sources.
- Analyzed the existing informational sources and methods to identify problem areas and make recommendations for improvement. This required a detailed understanding of the data sources and researching possible solutions.
- Implemented dimensional model (logical and physical) in the existing architecture using Erwin.
- Studied the PL/SQL code developed to relate the source and target mappings.
- Helped in preparing the mapping document for source to target.
- Designed and developed ETL processes using DataStage designer to load data from Oracle, MS SQL, Flat Files (Fixed Width) and XML files to staging database and from staging to the target Data Warehouse database.
- Used DataStage stages namely Sequential file, Transformer, Aggregate, Sort, Datasets, Join, Lookup, Change Capture, Funnel, Peek, Row Generator stages in accomplishing the ETL Coding.
- Developed job sequencer wif proper job dependencies, job control stages, triggers.
- Excessively used DS Director for monitoring Job logs to resolve issues.
- Involved in performance tuning and optimization of DataStage mappings using features like Pipeline and Partition Parallelism and data/index cache to manage very large volume of data.
- Documented ETL test plans, test cases, test scripts, and validations based on design specifications for unit testing, system testing, functional testing, prepared test data for testing, error handling and analysis.
- Used Autosys job scheduler for automating the monthly regular run of DW cycle in both production and UAT environments.
- Verified the Cognos Report by extracting data from the Staging Database using PL/SQL queries.
- Wrote Configuration files for Performance in production environment.
- Participated in weekly status meetings.
Environment: IBM DataStage 8.0 (Designer, Director), Oracle 10g,SQL Server 2008, DB2, Flat files, Sequential files, Autosys, TOAD 9.6, SQL*Plus, AIX UNIX, IBM Cognos 8.0
Confidential, Pleasanton, CA
ETL Designer/ DataStage Developer
Responsibilities:
- Involved in understanding of business processes and coordinated wif business analysts to get specific user requirements.
- Studied the existing data sources wif a view to know whether they support the required reporting and generated change data capture request.
- Used Quality Stage to check the data quality of the source system prior to ETL process.
- Worked closely wif DBA's to develop dimensional model using Erwin and created the physical model using Forward Engineering.
- Worked wif Datastage Administrator for creating projects, defining the hierarchy of users and their access.
- Defined granularity, aggregation and partition required at target database.
- Involved in creating specifications for ETL processes, finalized requirements and prepared specification document.
- Used DataStage as an ETL tool to extract data from sources systems, loaded the data into the SQL Server database.
- Imported table/file definitions into the Datastage repository.
- Performed ETL coding using Hash file, Sequential file, Transformer, Sort, Merge, Aggregator stages compiled, debugged and tested. Extensively used stages available to redesign DataStage jobs for performing the required integration.
- Controlled jobs execution using sequencer, used notification activity to send email alerts.
- Ensured that the data integration design aligns wif the established information standards.
- Used Aggregator stages to sum the key performance indicators used in decision support systems.
- Scheduled job runs using DataStage director, and used DataStage director for debugging and testing.
- Created shared containers to simplify job design.
- Performed performance tuning of the jobs by interpreting performance statistics of the jobs developed.
- Documented ETL test plans, test cases, test scripts, and validations based on design specifications for unit testing, system testing, functional testing, regression testing, prepared test data for testing, error handling and analysis.
Environment: DataStage 7.5.1 Enterprise Edition, Quality Stage, Flat files,Oracle10g, Cognos, SQL Server -2005/2008, Erwin 4.2, PL/SQL, UNIX, Windows NT/XP
Confidential, Atlanta, GA
ETL Developer
Responsibilities:
- Involved in understanding of business processes and coordinated wif business analysts to get specific user requirements.
- Extensively worked on DataStage jobs for splitting bulk data into subsets and to dynamically distribute to all available processors to achieve best job performance.
- Developed ETL jobs as per business rules using ETL design document.
- Converted complex job designs to different job segments and executed through job sequencer for better performance and easy maintenance.
- Enhanced the reusability of the jobs by making and deploying shared containers and multiple instances of the jobs.
- Imported the data residing in the host systems into the data mart developed in Oracle 10g.
- Extensively used Autosys for automation of scheduling jobs on daily, bi-weekly, weekly monthly basis wif proper dependencies.
- Wrote complex SQL queries using joins, sub queries and correlated sub queries.
- Performed Unit testing and System Integration testing by developing and documenting test cases in Quality Center.
- Validated the report generated using Business Objects using PL/SQL queries.
- Worked on troubleshooting, performance tuning and performances monitoring for enhancement of DataStage jobs and builds across Development, QA and PROD environments.
Environment: DataStage 7.5, Oracle 10g, DB2, SQL, PL/SQL, UNIX, SQL*Loader, Autosys, Business Objects 6.1, Windows 2003, IBM AIX 5.2/5.1, HP Mercury Quality Center 9.0
Confidential, New York
DataStage Developer
Responsibilities:
- Involved in understanding of business processes to learn business requirements.
- Extracted data from different systems into Source. Mainly involved in ETL developing.
- Defined and implemented approaches to load and extract data from database using DataStage.
- Worked closely wif data warehouse architect and business intelligence analyst in developing solutions.
- Used Erwin for data modeling (i.e. modifying the staging and SQL scripts on Oracle and MS Access Environments).
- Involved in design, source to target mappings between sources to operational staging targets, using DataStage Designer.
- Performed ETL coding using Hash file, Sequential file, Transformer, Sort, Merge, Aggregator stages compiled, debugged and tested. Extensively used stages available to redesign Data Stage jobs for performing the required integration.
- Executed jobs through sequencer for better performance and easy maintenance.
- Involved in unit, performance and integration testing of Data Stage jobs.
- Used Data Stage Director to run and monitor the jobs for performance statistics.
- Involved in performance tuning of the jobs.
- Used T-SQL for validating the data generated at OLAP server.
Environment: Ascential DataStage 7.0(Designer, Manager, Director), Oracle 9i, MS Access, SQL-Server 2000/2005,SQL, PL/SQL, Toad, UNIX