SUMMARY:

• Over 8+ years of experience in ETL mechanisms using IBM DataStage 11.3/9.1/8.7 both enterprise and standard edition using components like DataStage Designer, Data Stage Manager, DataStage Administrator, DataStage Director.
• Highly Motivated, Solutions Driven with over 7+ years of Data Warehousing experience in the areas of ETL design and Development.
• Involved in complete Software Development life - cycle (SDLC) of various projects, including Requirements gathering, System Designing, Data modeling, and ETL design, development, Production Enhancements, Support and Maintenance.
• Worked extensively with Dimensional modeling, Data migration, Data cleansing, ETL Processes for data warehouses.
• Analyzed Business requirements and prepared Technical Specification for various project processes. Ability to create High-level and Low-level ETL Flow Diagram in Visio.
• Worked on ETL Development for Control Architecture, Common Modules, Sequence Controls and major critical interfaces and performance tuning.
• Developed parallel jobs using different processing stages like Transformer, Aggregator, Lookup, Join, Sort, Copy, Merge, Funnel, CDC, Change Apply and Filter, FTP Enterprise.
• Used Enterprise Edition/Parallel stages like Datasets, Change Data Capture, Row Generator and many other stages in accomplishing the ETL Coding.
• Familiar in using highly scalable parallel processing infrastructure using parallel jobs and multiple node configuration files.
• Experienced in scheduling Sequence and parallel jobs using DataStage Director, UNIX scripts and scheduling tools.
• Experienced in integration of various data sources (DB2-UDB, SQL Server, PL/SQL, Oracle, Teradata, XML and MS-Access) into data staging area.
• Proficient in writing, implementation and testing of triggers, procedures and functions in   PL/SQL   and   Oracle
• Experience in troubleshooting of jobs and addressing production issues like data issues, ENV issues, performance tuning and enhancements.
• Knowledge in using Erwin as leading Data modeling tool for logical (LDM) and physical data model (PDM).
• Identify the production bugs and helping other team members to resolve the issues within the agreed SLA. Assisted in development efforts for Data marts and Reporting.
• Technical and analytical skills with clear understanding of design goals of ER modeling for OLTP and dimension modeling for OLAP.
• Extensive experience in Unit Testing, Functional Testing, System Testing, Integration Testing, Regression Testing, User Acceptance Testing (UAT) and Performance Testing.
• Worked with various databases like Oracle 11g/9i/8i, DB2, SQL Server, Teradata.

TECHNICAL SKILL SET

ETL Tools: IBM Info sphere DataStage 11.3/9.1/8.7/8.5/8.0.1/7.5

Operating Systems: Windows 10/8/7/vista/2000/2003, MS-DOS, UNIX, Sun Solaris and Linux

Database: Oracle11g/10g/9i/8i, SQL Server 2008/2005/2000 , DB2, Teradata, Sybase

Languages: SQL, PL/SQL, UNIX Shell scripting, VB scripting, C, Java

Reporting Tools: Crystal Reports & Pentaho Report Designer 3.5, Cognos

Data Modeling: Toad, Erwin 4.0/3.5

Scheduling Tools: Zena Scheduler and Control M Scheduler

PROFESSIONAL EXPERIENCE:

Sr. ETL/ DataStage Developer

Responsibilities

• Verified the implemented ETL code against the corresponding mapping document. Confirmed data loads from source files or tables into the target tables.
• Identified, researched, reported, tracked and verified software defects proactively and comprehensively.
• Performed issue analysis and resolution in a technical environment.
• Conducted Knowledge Transfer and Training Sessions for new team members and for other development teams.
• Created detailed test plans based on analysis of requirements and design documents.
• Executed systems and data quality testing to ensure all development deliverables are production ready.
• Used UNIX commands and utilities to monitor server side activities and performance.
• Developed jobs Using Parallel Extender for Extraction, Transformation and Loading (ETL) the data into the data warehouse.
• Designed the Jobs using the DB2 enterprise stage, Look up Stage, Merge Stage, Join Stage, Data Sets, Sequential Stage, Row Generator Stage, Transformer Stage and Modify Stage.
• Used DataStage Manager for importing metadata from repository, new job categories and creating new data elements.
• Used the DataStage Designer to design and develop jobs for extracting, cleansing, transforming, integrating, and loading data into different Data Marts.
• Defined the data definitions and created the target tables in the database.
• Mapped the source and target databases by studying the specifications and analyzing the required transforms.
• Used Teradata M load and Fast Load utilities through mainframe to extract data from flat files and load to Teradata tables.
• Wrote BTEQ scripts in mainframe to import and export data to and from the Teradata tables.
• Extract, Transform and Load data from various data sources to Teradata using Teradata utilities.
• Review of statistics and joins for performance improvement of Teradata SQL's using DBQL, Explain Plans.
• Created Complex SQL Queries and Used in the Jobs. Used the surrogate key stage to generate surrogate keys on parallel jobs.
• Compose new XML documents from relational tables or other sources. Extended XML processing by applying XSLT style sheets to the incoming XML.
• Developed Complex Data stage jobs using input and output XML stages by applying XML transformations to generated XML files as per the requirement and send to down streams applications via MQ message
• Creating ETL jobs using Pentaho Data Integration tool in an SQL/windows environment to handle the generation and distribution of reports.
• Designed and developed a series of complex Business Intelligence solutions using Pentaho Report Designer. Used Pentaho Data Integration Designer to create ETL transformations.

Environment: IBM Information Server Datastage 11.3/9.1, Oracle 10g, IBM DB2 UDB 8.2/8.0, Sybase, SQL SERVER 2008, Windows, Putty.

Sr. DataStage developer

Responsibilities

• Designed the ETL jobs using IBM infoSphere Information Server 8.7 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, Modify and Aggregator.
• Handled Performance Tuning of Jobs to ensure faster Data Loads.
• Used QualityStage to coordinate the delivery, consistency, removing data anomalies and spelling errors of the source information.
• Expert in using stages, Standardization, Scrubbing, Matching using QualityStage. Created Master controlling sequencer jobs using DataStage Job Sequences.
• Developed Quality Stage Jobs in order to perform Data cleansing using Investigate, Standardize Stage, Match Frequency, Survive Stage.
• 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 slowly changing dimensions.
• Provided data mappings (extract, transform and load analysis) of the data marts and data feed systems in the aggregation efforts.
• Migrated projects through DSX executable files from Development to Testing environment and Production environments.
• Performed the Integration and System testing on the ETL modules.
• Worked with BI team to apply the business rules for online analytical processing (OLAP), and for designing the Frame Work models.
• Assisted operation support team for transactional data loads in developing SQL & Unix scripts
• Participated in Unit testing and Integration testing of DataStage jobs.
• Scheduled jobs using AutoSys job scheduler utility based on the control flow and monitored the production jobs closely for any possible errors.

Environment: Flat files, DataStage 8.7 (Designer, Manager, Director), Oracle 9i, TOAD, SQL/PLSQL, Teradata, Erwin 4.0, UNIX (AIX) and Windows NT/2000.

DataStage Developer

Responsibilities

• Worked on mappings between multiple sources and Oracle target tables in Oracle Applications based on the functional design document (FDD). Interpret mapping documentation, and translate into detailed design specifications.
• Coordinated with System architects to design comprehensive solution. Worked on multiple sources: Oracle, SQL Server, Sequential files, XML files.
• Developed parallel jobs using most of the stages like Sequential file, change capture, funnel, transformer, data set, ODBC stage etc.
• Assist in defining a test plan for functional/quality assurance testing. Performed Unit Testing and System/Integration testing. Design and documented the test cases for unit testing.
• Extensively used Data Transforms like Data type Conversion, Null Handling, Date & timestamp.
• Used DataStage to extract, transform and load data from different input sources like flat files, Oracle into Oracle database.
• Estimating the efforts for Projects, Change Requests and Enhancements from a technical standpoint.
• Designed DataStage job with CDC (change data capture) to capture all records and transfer file over to the customer on a daily basis.
• Used AUTOSYS job scheduler to schedule all batch jobs and their dependencies to automate the process. Created job sequences to implement job dependencies.
• Generated Surrogate Keys for composite attributes while loading the data into Target tables. Written wrapper shell scripts to run data stage jobs and scheduling the jobs.

Environment: IBM InfoSphere Information Server 8.0.1, Metadata Workbench 8.5, Oracle11g, TOAD.

DataStage Developer

Responsibilities

• Analyzed requirements to identify necessary tables that need to be populated into the staging tables.
• Wrote UNIX scripts to Execute ETL Jobs, Sequences, FTP files, Sending Emails, Archiving and purging the files on regular intervals.
• Prepared DDLs for staging/work tables and coordinated with DBA for creating development environment and data models.
• 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.
• Used Data Stage Director to run and monitor the jobs for performance statistics. Analyzed source data from different Data sources such as DB2, flat files etc.
• Extracted data from source systems aggregate the data and load into the DB2 database using Datastage 7.5.
• Mapped source and target databases by studying specifications and analyzing required transforms.
• Created workflows and schedules for automation. Used Transformations to manipulate data to load into target tables.
• Involved in performance tuning of the ETL process and performed the data warehouse testing.

Environment: IBM InfoSphere DataStage and Quality Stage 8.5, Windows, Sybase, DB2, Zena, PLSQL, CDMA.

Confidential

Software Developer

Responsibilities

• Installed the database and overseen the implementation of users and security of the application's database.
• Set the environment of the client database using the export files.
• Involved in requirement analysis and design documentation.
• Designed and developed database tables necessary along with the necessary constraints to enforce business rules.
• Created and modified several Datsabase Triggers, especially for Audit and Security purposes.
• Developed the Stored Procedures, Packages, and Cursors and involved in the performance tuning of PL/SQL.
• Developed packages for various financial analyses.
• Resolved several hardcore business rules and issues at the client site.

Environment: Oracle8, PL/SQL, SQL, Oracle Import Developer 2000, MS SQL server 2000.