SUMMARY

• Over 7 years of experience in working with ETL methodologies (Data Extraction, Cleansing, Transformation and Loading) to develop business intelligence for corporations using IBM InfoSphere/Ascential DataStage 8.5.x/8.1.x/7.5.x.
• Extensive experience in designing and implementation of Star Schema, Snowflake Schema and Multi - Dimensional Modeling.
• Expertise in handling multiple operational data sources including Oracle, MS SQL Server, Teradata, Complex Flat Files, Delimited Files and IBM DB2/UDB for extraction, staging, data warehouse and datamart environments.
• Extensive involvement in different phases of teh project including Design/Analysis, Systems Analysis, Capacity Planning, Impact Analysis, GAP Analysis, Data extraction, Cleansing, Transformation and Loading into ODS/EDW/Datamart.
• Extensive experience on Oracle, SQL*Plus, PL/SQL,SQL Server 2000/2005,TSQL in writing stored procedures, Packages, Functions and Triggers, Scheduling jobs, database backup’s.
• Experience with UNIX shell scripting for Data validation and scheduling teh DataStage jobs.
• Extensive experience in tuning job designs, identifying and resolving performance bottlenecks in various levels including source, target, and mappings.
• Used DataStage Version Control to promote teh DataStage jobs from Development to Testing and then to Production environment.
• Performed Unit, Integration, System, Functional and User Acceptance Testing (UAT) on various systems.
• Excellent communication, interpersonal, organizational and troubleshooting skills. Also, good business understanding of financial systems, retail Industries, Supply Chain Management and ERP applications.

TECHNICAL SKILLS

ETL Tools: Ascential DataStage 8.5.x/8.1.x/7.5.x(Administrator, Manager, Designer, Director, Parallel Extender)

OLAP Tools: Cognos 8.5, Microstrategy 8.x/9.x.x

Data Modeling Tools: Erwin 4.1/4.0/3.5.2

RDBMS: Oracle 11g/10g/9i/8x,MS SQL Server, MS Access, DB2, Teradata V2R5/V2R6

Languages: PL-SQL, T SQL,C, C++, Java, HTML, PHP, XML

Other tools: Oracle Utilities, XML Publisher, SQL*Loader, WinRunner 8.0/7.5, Test Director 8.0/7.0, Loadrunner 11.0/7.5, Autosys, Control M, SQL Assistant, Toad

Operating Systems: AIX/ Linux, UNIX, Windows 2008/2005/2000/ XP

PROFESSIONAL EXPERIENCE

Confidential, Rochester, NY

ETL/DataStage Developer

Responsibilities:

• Involved in full SDLC life cycle - from Analysis, Design, and Development, Testing, UAT, Implementation and Post Implementation Support activities.
• Used Ascential Quality Stage for cleansing teh source data coming from heterogeneous sources such as ANSI X12 (fixed width flat files), CSV files, and COBOL (CFF) files and also loaded using DataStage jobs.
• Designed teh Source to Target mappings between sources to Operational staging areas and then to target Data warehouse as per teh Architecture standards.
• Developed Parallel jobs using Copy, CFF, Aggregator, Join, Transformer, Sort, Filter, Funnel, Peak, Surrogate Key Generator, Remove Duplicates and Lookup Stages.
• Created Hash tables used for referential integrity and/or otherwise while transforming teh data representing valid information.
• Created re-usable components using shared containers for local usage/shared usage.
• Created Error Files and Log Tables containing data with discrepancies to analyze and re-process teh data.
• Used teh Slowly Changing Dimension Stage itself to implement SCD Type-1 & 2 instead of implementing them using CDC Stage in 7.5.2.
• Used Range Lookup to break teh Complex Queries into simple Queries thereby improving teh performance of jobs by utilizing DataStage Parallel Engine efficiently.
• Scheduled teh main sequence to run every night using DataStage Director/Control-M Job Scheduler.
• Wrote SQL scripts on EDW and Data Mart Tables to validate teh data results by counting teh number of rows, comparing teh data between Prod & QA tables.
• Improved teh server version jobs performance by using/removing teh Hash files, In Process and Inter Process settings, and Link Partitioner, Link Collector and IPC stages.
• Developed UNIX shell scripts for initialization process and for process control mechanism.
• Tuned DataStage transformations and jobs to enhance their performance.
• Wrote PL/SQL statements and Stored Procedures in Oracle for extracting as well as loading (Initial/Incremental) teh data.
• Created job sequences for each dimension as well as fact. Also created a performance efficient Master Sequence to call dimensions and facts in correct order.
• Used teh DataStage Director and Autosys to schedule teh sequences and jobs.
• Involved in Unit, Integration, System and User Acceptance Testing (UAT).

Environment: Ascential DataStage 8.5(Parallel Extender, Quality Stage, DesignerDirector, Administrator, Web Console, BASIC language Expressions), Oracle, CSV FilesSQL, PL/SQL, Shell Scripts, TOAD, Windows XP Professional, IBM MVS OS/390.

Confidential, Milwaukee, WI

ETL/DataStage Developer

Responsibilities:

• Design and development of Extract, Transform, and Load (ETL) processes for extracting data from a various legacy systems and loading into target tables using SQL, DataStage Enterprise Edition.
• Used Different Partitioning methods in parallel job (Auto, Round-Robin, Hash, and Entire) to improve teh performance and to get teh accurate results according to teh business requirements.
• Extensively used SQL for Data Analysis and to understand teh data behavior.
• Used teh Parameter set to define teh parameters at project level in 8.1.1 instead of defining teh parameters for each and every job as in 7.5.2 thereby lessening teh development effort and reducing teh redundant task of adding new parameters at job level.
• Developed server jobs and converted some jobs to parallel jobs to get maximum efficiency from these jobs, which are implemented using Parallel Extender.
• Worked on Teradata insert, Update, Upsert and Dynamic SQL to transform and Load Summary tables built on Core Data Warehouse using BTEQ scripts.
• Developed TEMPeffectively different types of SCD’s based on teh specifications.
• Developed jobs in Parallel Extender using different stages like Join, Lookup stage, Copy stage, Row generation, Column generation, Pivot stage and Funnel stages.
• Used DataStage Director to verify logs and monitoring jobs during run session.
• Done Reverse engineering on existing data model to understand teh data flow and business flows of existing systems and to integrate teh new requirements to teh current system using CA ERwin modeling tool.
• Developed various datastage routines for functionality requirement and UNIX scripts for maintenance of datasets.
• Developed shared containers and a script to import metadata for flat files.
• Developed teh job dependency diagram in MS Visio to schedule teh Dimensions, Association and Fact table loading jobs.
• Automated and fine-tuned DataStage jobs and sequences for loading source systems data into warehouse.
• Generated completion messages, status reports using capabilities of Job Control Sequence and UNIX.
• Involved in writing UNIX scripts and calling them through ExecSH Routine activity in teh sequence.
• Experienced in working with/guiding teh off-shore team to develop and deliver teh tasks with high quality.
• Participated in teh daily standup calls/status meetings to update teh status about design and development and to discuss about teh road blockers.

Environment: Datastage 8.1, CA Erwin, MS Office Suite (Word, Visio, Excel), OracleTeradata V12, Teradata Utilities (BTEQ, FastExport, FastLoad, MultiLoad), TOADTeradata SQL Assistant, SVN, SQL, UNIX Shell Scripting, UNIX, Windows XP.

Confidential, Richardson, TX

ETL/DataStage Developer

Responsibilities:

• Gathering and documenting requirements, requirements analysis, converting requirements into High Level Design Documents.
• Worked with various sources such as Oracle, Flat files. Used Ascential Data stage to extract data & Load into Data Warehouse and Data Marts built on Oracle Database.
• Checked in code, ETL specs, Test cases into PVCS on a daily basis.
• Conducted various before and after code reviews.
• Worked with Teradata utilities like BTEQ, FastLoad, MultiLoad, Tpump,Fast Expert.
• Did data reconciliation in various source system and in Teradata.
• Actively participated in teh Team meetings to gather teh business requirements and developing teh Specifications.
• Worked with DataStage Manager for importing and exporting teh DSX of teh jobs.
• Designed and developed Master controlling sequencer jobs using teh DataStage Job Sequencer.
• Extensively worked in teh performance tuning of teh DataStage jobs, ETL Procedures and processes, tuning teh SQL used in Transformations and fine-tuning teh database.
• Defined business rules, aggregations and lookups.
• Worked with DataStage Manager for importing metadata from repository, new job categories and creating new data elements.
• Designed parallel jobs using stages such as Join, Merge, Lookup, Remove Duplicates, Copy, Filter, Funnel, Dataset, Lookup, Pivot, Sort, Surrogate key Generator, Change Data Capture, Modify, Row Generator and Aggregator.
• Used Job Compare option to compare teh jobs within teh same project as well as cross projects for efficient job design.
• Extensively worked with teh team members (OLAP) to develop teh reports using Cognos Reportnet.
• Used debug mode in data stage to test teh data flow and fix jobs
• Extensively wrote user-defined SQL coding for overriding for Auto generated SQL query in DataStage.
• Participated in discussions with Team leader, Group Members and Technical Manager regarding any technical and Business Requirement issues.
• Involved in Unit testing, System testing and Integration testing.

Environment: Datastage 7.5 (Manager, Administrator, Designer, Director, AIX), Data Warehousing, Cognos Reportnet, Oracle 9i, SQL, PL/SQL, Shell Script, TOAD 7.4, Erwin 4.5, UNIX, Teradata, W XP Professional.

Confidential, Hartford, CT

ETL/DataStage Developer

Responsibilities:

• Involved in creating Table definitions, indexes, views, sequences, Materialized view creation
• Prepared documentation for addressing teh referential integrity relations in between teh tables at ETL level
• Redesigned teh existing server jobs with a different logical approach to improve teh performance
• Extensively used Ascential DataStage Designer for creating DataStage Jobs and created Shared Containers for reusability.
• Extensively used all teh stages in Server Jobs like OCI, Hash File, Transformer, Sequential File, Link Partitioner, Link Collector and IPC.
• Used teh DataStage Director and its run-time engine to schedule running teh solution, testing and debugging its components, and monitoring teh resulting executable versions (on an ad hoc or scheduled basis)
• Worked with DataStage Manager for importing metadata from repository, new job categories and creating new data elements
• Involved in designing teh procedures for getting teh data from all systems to Operational Data Store.
• Extensively used DataStage Designer components to design various Parallel jobsin accordance with business specs.
• Created DataStage jobs, batches and job sequences and tuned them for better performance optimized
• Used Job Control routines and Transform functions in teh process of designing teh job.
• Worked on programs for scheduling Data loading and transformations using DataStage from DB2 to Oracle 9i using SQL* Loader and PL/SQL
• Extensively worked with various stages in parallel Extender like Sequential file, Dataset, lookup, peek, transformer, Merge, Aggregator, row generator and many more to design jobs and load into Dimension and Fact tables.
• Involved in performance tuning of teh ETL process and performed teh data warehouse testing
• Prepared documentation including requirement specification.
• Designed XML stages for reading XML log files for capturing data stage jobs audit data.

Environment: DataStage7.x/EE/PX,MetaStage,DB2,Oracle9i/8i,SQL,PL/SQL,SQL*Loader Erwin 3.5, SQL Server, Windows NT

Confidential, Dallas, TX

Data Warehouse Developer

Responsibilities:

• Designed and Developed validation ETL processes using DataStage, Syncsort, and UNIX shell programming for Alignment, Customer, Promotions, subsystems as part of data warehouse
• Designed and Developed Extract, Transform and Load (ETL) functions from a variety of operational systems including legacy systems utilizing SQL, DataStage (ETL tool), Unix shell Scripts, bulk loads (Oracle SQL Loader), etc
• Used ERWIN as leading data modeling tool for Logical (LDM) and physical data model (PDM)
• Extensively used most of teh transforms of DataStage for various types of transformations.
• Validated and successfully executed jobs using DataStage Director
• Comprehensive expertise on DataStage server components - DataStage Repository, DataStage server, DataStage package installer.
• Involved in Development, mainly on Performance tuning and making sure dat all BTEQ scripts are developed in optimized way.
• Developed jobs in Parallel Extender using different stages like Transformation, Aggregation, Source dataset, external filter, Row generation, Column generation and vector stage Used Unix Shell Scripts to automate teh process.
• Involved in tuning DataStage repository and jobs for better performance
• Designed teh mappings between sources (external files and databases) to staging targets.
• Designing and Developing PL/SQL Procedures, functions, and packages to create Summary tables
• Worked closely with teh Data Warehousing Admin and Data Modeling team in tuning teh Extraction and Summarization process for better performance
• Developed UNIX shell scripts to automate teh Data Load processes to teh target Data warehouse.

Environment: DataStage 5.2/6.0, Orchestrate 6.x, Oracle 8i, DB2UDB, PL/SQLWindows NT 4.0, UNIX, SQL* Loader, ETL, Syncsort, Control-M, and UNIX (IBM AIX)

Confidential

Oracle Developer

Responsibilities:

• Involved in developing reports on existing oracle database and scheduling of these reports using UNIX shell scripts, also supported teh up gradation of oracle forms.
• Designed, created and maintained Web-enabled Oracle Forms & Reports for networks Procurement.
• Teh database design responsibility included teh conceptual data model design using ER diagrams, logical database design and physical implementation of teh database.
• Developed Oracle Forms and Reports for teh user interface; a data load using PL/SQL and SQL*Loader; and cron jobs calling UNIX scripts to download and manipulate files
• Identification and implementing business rules (constraints, triggers etc.) and views.
• Made necessary changes to existing Database Objects and added new whenever required.
• Generated SQL and PL/SQL scripts to install create and drop Trading Exchange Connection database objects including: tables, views, primary keys, indexes, constraints, packages, sequences, grants and synonyms.
• Designed and developed several Unix shells scripts for teh automation of teh processes
• Troubleshooted performance issues and bugs within packages, forms, and reports using dbms output, Forms debugger.
• Documented business rules, functional and technical designs, test cases, and user guides

Environment: Windows NT workstations, AIX Unix, Oracle 8i RDBMS, Developer/2000SQL, PL/SQL, Unix Development system, Windows sockets, Microsoft Visio.