SUMMARY:

• Around 7 years of profound IT experience in Design, Development and Implementation of Relational Database and Data Warehousing Systems using IBM InfoSphere Information Server 8.1(DataStage, QualityStage), IBM DataStageEnterprise and AscentialDataStage7.5/7/6.x/5.x. Client - Server environment.
• Hands on experience in creating Indexed Views, complex Stored Procedures, effective functions, and appropriate Triggers to assist efficient data manipulation and data consistency.
• Strong knowledge ofRelational Database Management Systems (RDBMS) conceptin several databases like SQL Server 2008/2005 and Oracle 10g/9i/8i.
• Worked on activities related to the development, implementation, administration and support of ETL process for large-scale Data Warehouses using SSIS, DataStage.
• Creating data mappings and data dictionaries and understanding business data relationships.
• Experience in working with Server jobs, Parallel jobs, debugging, troubleshooting and performance tuning.
• Expertise in working with different versions of DataStage - DataStage Standard (Server Jobs and sequence jobs), DataStage Enterprise (Server jobs, sequence jobs and parallel jobs) offering parallel processing features and DataStage Enterprise MVS allowing jobs to be developed on a Unix or Windows server and transferred to the mainframe to be compiled and run.
• Worked on DataStage client tools like DS Designer, DS Director, DS Manager and DS Administrator.
• Experience in Designing, Compiling, Testing, Scheduling and running DataStage jobs.
• Experience in various phases of IT projects Software Development Life Cycle (SDLC) such as analysis, design, construction and testing, deployment and production support.
• Strong skills in data mapping for the Slowly Changing Dimensions - SCD1, SCD2 (Flagging, Time Stamping and Version), and SCD3.
• Implemented the complex business rules by creating reusable transformations and Mappings / Mapplets.
• Experience in debugging and Performance tuning of targets, sources, mappings and Sessions in Informatica and performed debugging, troubleshooting, monitoring and performance tuning using DataStage.
• Expertise in Dimensional Data modeling SSAS techniques like Star Schema, Snowflake modeling, Fact and Dimensions tables, Physical and logical data modeling.
• DesignedDataStageJobs to extract data from XML files using XML input stage, Used XML transformer stage to cleanse and transform the data to load it into the Data Mart.
• Used built-in Quality Stage 8.x for data cleansing, data quality and consistency by standardizing, validating, matching, and merging information to create comprehensive and authoritative information for multiple uses.
• Good Knowledge of ODBC Connector, SQL Builder, Advanced and Quick Find, Parameter Set, Common Logging, Resource Estimation tool.
• Interacted with Developers, Business analysts, Database team, Deployment team, and Release Management in an efficient and effective manner.
• Experience in developing ETL mappings and scripts using AscentialDataStage7.5/7/6.x/5.x using Designer (Source Analyzer, Warehouse designer, Mapping designer, Mapplet Designer, Transformation Developer), Repository Manager and Workflow Manager.
• Effective leadership skills with good written, verbal communication and presentation skills. Self-motivated with result oriented approach. Committed collaborator and team leader

TECHNICAL SKILLS:

Databases: Oracle 8i/9i/10g/11g/11i, MySQL, Teradata 12, DB2, MS SQL Server 2000/2005/2008

ETL: Web Sphere DataStage 8.5/8.1 and 7.5(Designer, Director, Administrator, Manager), Information Analyzer, MetaStage 6.0, Parallel Extender 6.0(PX) Informatica Power Center 9.0, 8.1.1, 8.6.1(Repository manager, Designer, Workflow manager, Workflow monitor), IBM Web Sphere Information Server 8.0(Designer, Director, Administrator)

Languages: SQL, PL/SQL, Unix Shell Scripting, COBOL, ASP, HTML, VB, Java Script

Tools: Toad, Teradata SQL assistant, SQL Developer, MS (SSIS, SSAS), MS Office, Erwin 4.1

Operating Systems: Windows 98/NT/2000/XP, UNIX (Sun Solaris 5.9, HP-UX, LINUX)

Scheduling Tools: Tivoli, Control-M, Autosys

PROFESSIONAL EXPERIENCE:

Confidential, Naperville, Illinois

DataStage Developer

Responsibilities:

• Developed both Server and Parallel Jobs in DataStage Designer to extract data from the Sources Oracle and Complex Flat Files, Cleanse it using Quality Stage, Transform by applying business rules, staging it in Data marts and Load (Initial/Incremental) into Target Teradata.
• Used the DataStage stages Oracle Enterprise, CFF, Copy, Filter, Lookup, Transformer, Sort, Funnel, Shared Containers, Join, Dataset, Aggregator, Sequential file, Remove Duplicates.
• Used Job Compare option to compare the jobs developed for efficient job design.
• 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.
• Improved the server version jobs performance by using Hash file, In Process and Inter Process settings, Link Partitioner, Link Collector.
• For Parallel jobs, configured the multiple nodes and used parallel engine capacity efficiently as well as designed a master sequence to run multiple jobs in parallel.
• Developed Sequences to run Dimensions and Facts and also developed a Master Sequence to run the entire project.
• Used Remove Duplicates stage in PX (EE) to remove the duplicates in the data
• Created Project level Message Handlers to promote or demote the warning messages
• 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
• Responsible for monitoring all the jobs that are running, scheduled, completed and failed. Troubleshooting the failed jobs is a primer in these situations
• Worked on database connections, SQL joins, views, aggregate conditions, parsing of objects and hierarchies
• Tuned of SQL queries for better performance for processing business logic in the database

Environment: IBM Information Server (Datastage) 8.5/8.1(Designer, Manager, Director, Administrator), SAGENT tool, Parallel Extender, Oracle 10g, IBM DB2 UDB 8.2/8.0, SQL SERVER 2005, Windows XP, AIX 5.3/5.1

Confidential, NJ

Data Warehouse Developer (DataStage)

Responsibilities:

• Worked with the Business analysts and the DBA’s for requirements gathering, analysis, testing, and metrics and project coordination
• Successfully implemented the slowly changing dimensions. Involved in the Dimensional modeling of the Data warehouse. Involved in designing various jobs using PX
• Created and populated Data Warehouse structures using DataStage
• Developed documents like Source to Target mapping for developing the ETL jobs
• Populated Data Marts at different levels of granularity for the inside Customers using DataStage (being executed from shell scripts driven by Autosys)
• Worked with DataStage server stages like OCI, ODBC, Transformer, Hash file, Sequential file, Aggregator, Sort, Merge, Link Partitioner, Link collector, IPC and other stages
• Designed and coded high performance DataStage routines
• Imported the required Metadata from heterogeneous sources at the project level
• 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
• Worked on programs for scheduling Data loading and transformations using Data Stage from legacy system and Data Warehouse to Oracle 9i.
• Used the Slowly Changing Dimension Stage itself to implement SCD Type-1 & 2.
• Exported the project from Development to Test environment using DataStage Manager.
• Wrote the SQL queries & PL/SQL code tables and Data Mart Staging Tables to validate the data results by counting the number of rows in both the tables.
• Developed UNIX shell script to run jobs in multiple instances by using a parameter file.
• Interacted with Solutions analysts, system staff, ETL, BO developers and the data load staff.
• Involved in Unit, Integration, System and User Acceptance Testing (UAT).

Environment: DataStage Parallel Extender 8.X (Designer, Manager, Director), Oracle 10g, TOAD, SQL/PLSQL, Teradata V2 R5, Teradata SQL Assistance 7.0, Erwin 4.0, UNIX (AIX) and Windows NT/2000

Confidential, Providence, RI

DataStage Developer

Responsibilities:

• Involved in status meetings, and interacted with the Business Analyst to gather the requirements as per the business rules.
• Migrated Outbound Data Feeds from SQL Server as source to Oracle as destination.
• Documented the purpose of mapping so as to facilitate the personnel to understand the process and in corporate the changes when necessary.
• Designed Parallel jobs using various stages like join, merge, remove duplicates, filter, dataset, lookup file set, modify, aggregator and funnel stages. Extensively used Built-in (Sort, Merge, Oracle, Aggregator, DB2 Stages), Plug-in Stages for extraction, transformation and loading of the data.
• Used Quality Stage stages such as Investigate, Standardize, Match and Survive for data quality and data profiling.
• Received the master data and populated the Dimension Tables first, Time generation, Surrogate key generation and the populated the Fact Tables.
• As per Data Architect’s directions, created and executed the SCD Type 2 implementation on Dimensional data.
• Used MetaStage for the synchronization and Integration of Metadata from various Data Warehouse related tools and also used for automatically gathering process data from operational systems.
• Developed the customized routines, stored procedures and functions for complex joins to achieve better job performance.
• Used Autosys to schedule the DataStage ETL batch jobs on a daily, weekly and monthly basis.
• Created shell scripts that will invoke the Data Stage jobs passing all variables for job to execute with parameterized databases connection information.
• Exclusively used SQL query to query data from MS SQL Server warehouse for extraction and to also query data from Oracle data warehouse for loading.
• Analyzed data with discrepancies through Error files and Log files for further data processing and cleansing.
• Performed Unit, System Testing and modified jobs running in production environment, with minimized downtime.
• Optimized partitioning and parallelism in jobs by doing performance tuning and using indexes for the tables being used.
• Worked on performance tuning and enhancement of DataStage jobs by reducing the use of stages like sort, aggregate and avoided using routines which eliminates the overhead of the procedure call for better performance.

Environment: DataStage v 7.5.2, DB2 UDB 8.xx, Oracle 10g, SQL Server, Erwin 4.1, Windows XP, IBM AIX 5.2.

Confidential, Dallas, TX

DataStageDeveloper

Responsibilities:

• Provided High Level Design, Detail Level Design Document.
• Extensively used the DataStage designer to develop various parallel jobs to extract, transform, integrate and load the data into Enterprise Data warehouse (EDW).
• Designed and developed Data Stage ETL Jobs using Parallel Extender 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, Surrogate key Generator, Change Data Capture, Modify, Row Generator and Aggregator.
• Created Master controlling sequencer jobs using Data Stage Job Sequencer.
• Extensively developed and deployed UNIX Shell scripts as wrappers that provides values to Data Stage jobs during runtime, execute Data Stage jobs.
• Used DataStage Director to monitoring jobs, run and validating its components.
• Imported existing technical documented done with excel document converted to CSV and into Fast track.
• Documented the jobs used in ETL processes.
• Analyzed data with discrepancies through Error files and Log files further data processing and cleansing.
• Designed parallel jobs using Quality Stage activities like Standardize, CASS, and Unduplicated Match etc.
• Worked on analyzing the processed data in Information Analyzer.
• Played a key role as a primary contact person for data stage production environment.
• Worked on creating and assigning projects and Metadata in Information Analyzer.
• Migrated projects from development to QA to Production environments.
• Performed the Integration and System testing on the ETL application.
• Working with BI team to apply the business rules for OLAP, designing the Frame Work models.
• Scheduled jobs using Autosys job scheduler utility based on the requirements and monitored the production closely for any possible errors.
• Experience configuring, integrating and implementing Data stage version 8.

Environment: DataStage 8.0.1 (PX & Server), QualityStage 8.0.1, Information Analyzer, OS 390 (MVS), XML files, CSV files, AIX, flat files, Cobol, Oracle 10g,DB2 UDB, Autosys, CWDirect, Clear Case/Clear Quest, UNIX, Windows NT.

Confidential, CA

ETL Developer

Responsibilities:

• The Stanley Works is a worldwide manufacturer and marketer of tools, hardware and specialty hardware products for home improvement, consumer, industrial and professional use. This project was developed to organize and integrate the data from multiple channels into data warehouse for analysis of customer Receivable System, Order entry system, Sales & Marketing.
• Responsibilities and Tasks Performed:
• Involved in gathering business requirements and mapping various source systems to targets.
• Installed Plug-in stages like Teradata Load and Teradata Multiload.
• Worked closely with Data Modeler and Database Administrator to understand the Business process of the Data Warehouse Model.
• Extensively used UNIVERSE BASIC transformations, functions and routines for data cleansing and conversion.
• Developed Analytical applications that can analyze large amounts of online and offline data.
• Used the DataStage Designer to develop processes for extracting, cleansing, transforming, integrating, and loading data into data warehouse database.
• Extensively wrote SQL scripts.
• Used DataStage Manager for importing metadata from repository, new job categories and creating new data elements.
• Used Data Stage Director to validate schedule run and monitor the data stage jobs.
• Developed user defined Routines and Transformations by using UNIVERSE BASIC.
• Used Before/After Job-Subroutines in Job Properties.
• Created various DataStage Hash files for lookups.
• Used DataStage, Oracle 8i for developing programs for scheduling data loading and transformations.
• Developed shell scripts to automate file manipulation and data loading procedures.
• Error Logs / Audit Trails were maintained.
• Developed aggregate fact tables for different users in Data marts.
• Involved in the process design documentation of the Data Warehouse Dimensional Upgrades.
• PL/SQL procedures to transform data from staging to Data Warehouse Fact and summary tables.

Environment: Ascential DataStage 7.0, ERWIN 4.1, Oracle 8i, Teradata v2 R4.0/v2R5.0, Teradata SQL Assistant 6.1, Teradata Multiload, UNIX, Windows NT 2000

Confidential

ETL Developer

Responsibilities:

• Involved in all phases including Requirement Analysis, Design, Coding, Testing and Documentation.
• Involved in gathering business requirements and mapping various source systems to targets.
• Installed Plug-in stages like Teradata Load and Teradata Multiload.
• Worked closely with Data Modeler and Database Administrator to understand the Business process of the Data Warehouse Model. Developed Analytical applications that can analyze large amounts of online and offline data.
• Used the DataStage Designer to develop processes for extracting, cleansing, transforming, integrating, and loading data into data warehouse database.
• Extensively wrote SQL scripts. Extensively used UNIVERSE BASIC transformations, functions and routines for data cleansing and conversion.
• Used DataStage Manager for importing metadata from repository, new job categories and creating new data elements.
• Used Data Stage Director to validate schedule run and monitor the data stage jobs.
• Developed user defined Routines and Transformations by using UNIVERSE BASIC.
• Used Before/After Job-Subroutines in Job Properties. Created various DataStage Hash files for lookups.
• Used DataStage, Oracle 8i for developing programs for scheduling data loading and transformations.
• Developed aggregate fact tables for different users in Data marts.
• Error Logs / Audit Trails were maintained. Developed shell scripts to automate file manipulation and data loading procedures. Involved in the process design documentation of the Data Warehouse Dimensional Upgrades.
• PL/SQL procedures to transform data from staging to Data Warehouse Fact and summary tables.

Environment: Ascential DataStage 7.0, ERWIN 4.1, Oracle 8i, Teradata v2 R4.0/v2R5.0, Teradata SQL Assistant 6.1, Teradata Multiload, UNIX, Windows NT 2000