SUMMARY

• 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 requirement gathering, system designing, data modeling, application development, enhancement development, maintenance and support.
• Extensive ETL tool experience using IBM Websphere DataStage, Ascential DataStage.
• Extensively used DataStage Client components like DataStage Administrator, Manager, Designer and Director in Data Warehouse development.
• Strong understanding of the principles of DW using fact tables, dimension tables and Star and snowflake schema modeling.
• Worked extensively with Dimensional modeling, Data migration, Data cleansing, ETL Processes for data warehouses.
• Familiar in using highly scalable parallel processing infrastructure using parallel edition.
• Used Enterprise Edition/Parallel Edition stages like Datasets, Transformation stage, Filter stage Modify, Copy, Change Capture, Row Generator and many other stages in accomplishing the ETL Coding
• Developed jobs based on the joining of data operations using various stages like Join stage, Merge stage, Lookup stage.
• Extensively worked on CDC and Slowly Changing Dimensions techniques to maintain the historical details of the data.
• Worked with and extracted data from various database sources Oracle 10g/9i/8i, DB2, SQL Server, Teradata, Sequential files, data sets, etc.
• Developed efficient mappings for data extraction/transformation/loading (ETL) from different sources to a target data warehouse.
• Experience in troubleshooting of jobs and addressing production issues like performance tuning and enhancement.
• Experience in new enhancements in the IBM Websphere DataStage - Multiple Job Compile, Surrogate key generator Stage, Job Report, Message handler options.
• Created various parameters and param sets for multi-instance jobs and environmental variables to be used across the project
• Knowledge in using ERwin as leading Data modeling tool for logical (LDM) and physical data model (PDM).
• Experienced in scheduling server and parallel jobs using DataStage Director, UNIX scripts.
• Assisted in development efforts for Datamarts and Reporting.
• Technical and analytical skills with clear understanding of design goals of ER modeling for OLTP and dimension modeling for OLAP.
• Knowledge in using PL/SQL to write stored procedures, functions, and triggers.
• Involved in providing support in Dev, QA and prod environments.
• Excellent communication, interpersonal, analytical skills and strong ability to manage and motivate the team.

TECHNICAL SKILLS

ETL Tools: IBM Infosphere DataStage 8.1, IBM Websphere Information Server 8.0 (Designer, Director, Administrator), Websphere DataStage 7.5.X (Designer, Director, Administrator, Manager), Ascential DataStage 7.0/6.0.

Data Modeling: Data Modeling, Star Schema Modeling, Snow-Flake Modeling, Fact and Dimensions, Physical and Logical Data Modeling, ERwin.

Databases: Oracle 10g/9i/8i, IBM DB2/UDB, Teradata, SQL Server 2003/2005/2008.

Tools: SQL Plus, SQL*Loader, TOAD.

Operating system: IBM AIX 5.2/4.x, Linux, Windows 2000/2003/XP.

Languages: SQL, PL/SQL, Unix Shell Scripting, Java, HTML, XML

PROFESSIONAL EXPERIENCE

Confidential, Lincoln, NE

ETL/Sr. DataStage Developer

Responsibilities:

• Analyzed, designed, developed, implemented and maintained Parallel jobs using Enterprise Edition of Datastage.
• Involved in design of dimensional database - Star schema and creation of physical tables in Oracle.
• Successfully implemented pipeline and partitioning parallelism techniques and ensured load balancing of data.
• Deployed different partitioning methods like Hash by field, Round Robin, Entire, Modulus, and Range for bulk data loading and for performance boost.
• Repartitioned job flow by determining DataStage PX best available resource consumption.
• Experienced in PX file stages that include Complex Flat File stage, DataSet stage, LookUp File Stage, Sequential file stage.
• Implemented various Shared container for multiple jobs and Local containers for same job as per requirements.
• Adept knowledge and experience in mapping source to target data using Ascential Data Stage 8.x.
• Implemented multi-node declaration using configuration files (APT Config file) for performance enhancement.
• Experienced in developing parallel jobs using various Development/debug stages (Peek stage, Head & Tail Stage, Row generator stage, Column generator stage, Sample Stage) and processing stages (Aggregator, Change Capture, Change Apply, Filter, Sort & Merge, Funnel, Remove Duplicate Stage)
• Worked within a team to populate Type I and Type II slowly changing dimension tables from several operational source files
• Used Data Stage Manager to import metadata, new job categories and creating new data elements as well as to export job categories.
• Created several general routines (Before-After, Transform function) used across the project.
• Involved in creating UNIX shell scripts for database connectivity and executing queries in parallel job execution.
• Used the ETL Data Stage Director to schedule running the solution, testing and debugging its components & monitor the resulting executable versions.
• Debug, test and fix the transformation logic applied in the parallel jobs
• Experienced in using SQL *Loader and import utility in TOAD to populate dataset in the data warehouse tables.
• Created Universes and reports in Business object Designer.
• Created, implemented, modified and maintained the business simple to complex reports using Business objects reporting module.

Environment: IBM Infosphere DataStage 8.1 (PX & Server), Oracle 10g, Flat files, Autosys.

Confidential, Meridian, CO

Sr. DataStage Developer

Responsibilities:

• Extensively used DataStage Designer to develop processes for extracting, transforming, integrating and loading data from various sources into the Data Warehouse database.
• Developed several complex ETL jobs for Historical data loads and ongoing data loads using various active and passive stages.
• Extensively used SQL queries to retrieve data from various database tables and used those queries in the jobs to reduce the complexity of processing data.
• Upgraded to IBM Websphere Information server 8.0 from IBM Websphere DataStage 7.5.3.
• Extracted data from flat files Transformed according to the requirement and Loaded into staging schema using various stages like sequential file, Aggregator, Transformer stage, Oracle enterprise, dataset, Look up, Aggregator, joiner, Remove Duplicates, Change capture, sorter, Column generators, Funnel and Sequencer.
• Extensively used ETL logic against DB2 DB and loaded the data into the target data mart.
• Involved in performance tuning of the jobs while developing the jobs.
• Used Manager for importing metadata from repository, new job categories and creating new data elements.
• Created the various complex jobs to load the data from staging area to the application data base and from there to data mart.
• Used Parallel Edition for splitting the data into subsets and to load data, utilized the available processors to achieve job performance, configuration management of system resources in Orchestrate environment.
• Developed staging and DataMart DS jobs using Data Stage Designer on parallel environment and involved in writing the possible Unit test cases and tested number of job and also resolved the defects in developed jobs.
• Transfer of large volumes of data using Teradata Fast Export, Multi Load.
• Developed batches and sequencers in designer to run and control set of jobs.
• Used the Director and its run-time engine to schedule running the job, testing and debugging its components, and monitoring the resulting executable versions
• Involved in importing and exporting jobs category wise and maintaining the backup regularly.
• Performed the Unit testing for jobs developed to ensure that it meets the requirements.
• Developed UNIX shell scripts to automate file manipulation and data loading procedures.

Environment: IBM WebSphere Information Server 8.0 (Designer, Director, Administrator), IBM Websphere Datastage 7.5.x (Director, Designer, Manager, Administrator), DB2, Teradata, SQL Server, Oracle 9i, Clear Case.

Confidential, Atlanta, GA

Developer

Responsibilities:

• Involved in Designing the ETL process to Extract translates and load data from OLTP Oracle database system to Teradata data warehouse.
• Gathered information from different data warehouse systems and loaded into One Sprint Financial Information System Consolidated model using Fast Load, Fast Export, Multi Load, BTEQ and UNIX Shell Scripts.
• Used the Ascential DataStage Designer to develop processes for extracting, cleansing, transforming, integrating, and loading data into data warehouse database.
• Knowledgeable in the areas of relational database logical design, physical design, and performance tuning of the RDBMS.
• Worked extensively on different types of stages like Sequential file, ODBC, Hashed File, Aggregator, Transformer, Change Capture Stage, Join, Lookup, Merge, Sort and Containers (Shared and Local) for developing job.
• Create master controlling sequencer jobs using the DataStage Job Sequencer.
• Effectively used DataStage Manager to Import/Export projects from development server to production server. Parameterized jobs for changing environments.
• Extensively used ETL to extract data from Oracle9i, XML files and Complex Flat files.
• Responsible for trouble shooting, identifying and resolving data problems, Worked with analysts to determine data requirements and identify data sources, provide estimates for task duration.
• Involved in unit testing, integrated testing and user acceptance testing.
• Optimized performance of Mappings and sessions by identifying bottlenecks and eliminating them.
• Created Fast Load, Fast Export, Multi Load, TPump, BTEQ scripts for Financial Information System.
• 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.
• Scheduled jobs dependencies using Control-M Scheduler

Environment: Ascential DataStage 7.5.2 (Administrator, Manager, Designer, Director, Parallel Extender), Teradata Tools & Utilities (BTEQ, Fast Export, Multi Load, Fast load, SQL Assistant), Oracle 10g/11g, AutoSys, ERwin 3.5, Microsoft SQL Server 2005

Confidential, Southfield, MI

Developer

Responsibilities:

• Worked with the Business analysts and the DBAs for requirements gathering, analysis, testing, and metrics and project coordination.
• Successfully handled the slowly changing dimensions.
• Involved in the Dimensional modeling of the Data warehouse.
• Developed documents like Source to Target mapping for developing the ETL jobs.
• Worked with DataStage server stages like OCI, ODBC, Transformer, Hash file, Sequential file, Aggregator, Sort, Merge, Link practitioner, Link collector and other stages.
• Imported the required Metadata from heterogeneous sources at the project level.
• Involved in designing various jobs using PX.
• Developed Parallel jobs using Parallel stages like: Merge, Join, Lookup, Transformer (Parallel), Teradata Enterprise Stage, Funnel, Dataset, Oracle Enterprise Stage.
• Performed debugging on these jobs using Peek stage by outputting the data to Job Log or a stage.
• Used Remove Duplicates stage in PX (EE) to remove the duplicates in the data.
• Involved in the migration of DataStage jobs from Development to Production environment.
• Designed and implemented several wrappers to execute the DataStage jobs, create job reports out of the DataStage job execution results from shell scripts.
• Designed and implemented wrappers to execute the DataStage jobs from remote servers.
• Worked on database connections, SQL joins, views, aggregate conditions, parsing of objects and hierarchies.
• Tuned SQL queries for better performance for processing business logic in the database.

Environment: DataStage 7.X (Designer, Manager, Director, Administrator), Oracle 9i, TOAD, SQL/PLSQL, Teradata, Erwin 4.0, UNIX (AIX).

Confidential, Santa Clara, CA

DataStage Developer

Responsibilities:

• Extensively used for extracting, transforming and loading databases from sources including Oracle, Teradata and Flat files.
• Worked on Datastage Designer, Manager, Administrator and Director.
• Created re-usable components using shared containers for local use or shared use. Exported the universe to the Repository to make resources available to the users.
• Loaded data into load, staging and lookup tables. Staging area was implemented using flat files.
• Created jobs in DataStage to import data from heterogeneous data sources like Oracle 9i, Text files and SQL Server.
• Generation of Surrogate IDs for the dimensions in the fact table for indexed and faster access of data in server jobs.
• Ran jobs using Parallel Edition for splitting bulk data into subsets and dynamically distribute to all available nodes to achieve best job performance.
• Tuned transformations and jobs for Performance Enhancement.
• Created Batches (DS job controls) and Sequences to control set of jobs.
• Created Data Model using ERwin from data sources and defined the dimensions, levels and measures in the model.
• Utilized Parallel Edition PX, to optimize performance in this very large database environment.
• Implemented the underlying logic for Slowly Changing Dimensions.
• Executed Pre and Post session commands on Source and Target database using Shell scripting.

Environment: Ascential 6.0, Parallel Edition PX, SQL Server, Oracle 9i, Teradata, Text Files, ERwin, UNIX.

Confidential

Developer/Trainee

Responsibilities:

• Involved in extracting the data from different data sources like Oracle 8i and flat files.
• Involved in extracting the data from staging to Relational.
• Involved in creating and maintaining Sequencer and Batch jobs.
• Developed parallel jobs using stages, which includes Aggregator, Join, Transformer, Sort, Filter and Lookup.
• Creating ETL Job flow design.
• Used ETL to load data into the Oracle warehouse.
• Involved in development of Job Sequencing using the Sequencer.
• Used designer and director to schedules and monitor resulting executable versions.
• Creating local and shared containers to facilitate ease and reuse of jobs.
• Managing Repository Metadata from Manager.
• Performing Unit Testing and Integration.

Environment: Ascential DataStage 6.0(Administrator, Designer, Manager, Director), Oracle 8i, Flat files.