SUMMARY

• 8 years of IT experience specializing in Analysis, Design and Development of ETL processes in all phases of the Data Warehousing life cycle.
• Knowledge in OLAP and OLTP, Dimensional modeling using Star schema and Snowflake schema.
• Extensively worked in Extraction/Transformation/Loading of the legacy data to Data warehouse using IBM Websphere Datastage (8.x/7.x) including Designer, Director and Administrator.
• Worked on databases Oracle 8i/9i/10g/11g and knowledge in SQL Server 2000/2005 and DB2.
• Skilled in Planning, Designing, developing and deploying Data warehouses and Data Marts.
• Experienced in Ralph Kimball’s methodology and Bill Inman’s approaches.
• Involved in daily interactions with the Business team to understand their requirements.
• Extensively worked on performance tuning of Datastage jobs using best IBM standards by identifying and resolving performance bottlenecks at various levels.
• Extensively used SQL and PL/SQL to write Stored Procedures and Functions.
• Expertise in developing and maintaining overall Test Methodology and Strategy, Documenting Test Plans, Test Cases and editing, executing Test Cases and Test Scripts.
• Good experience in analyzing Datastage jobs performance using OSH score and dump scores.
• Good experience in maintaining the versioning and deployment instruction documents while moving the code to various environments.
• Extensively worked with production support team to resolve issues and worked in extracting data from Oracle, DB2, XML and flat files.
• Hands on Experience in data extraction & validation in SQL and UNIX Shell Scripting.
• Good exposure to development, testing, debugging, implementation, documentation, user training & production support.
• Resolved data issues, completed unit testing and system documentation for ETL processes
• Comprehensive knowledge in Conceptual, Logical and Physical Data modeling concepts.
• Experience on Creating detailed project outlines and application design specifications.
• Knowledge on tools such as IBM Cognos 8 (Framework Manager, Report Studio, Query Studio)
• Knowledge on creating reports in Report Studio and Query Studio using IBM Cognos 8
• Solid knowledge in Informatica Powercentre 9.x/8.x/7.x
• Ability to quickly analyze and develop an industry standard solution for a given problem.
• Excellent communication skills, problem solving skills, Leadership qualities and an attitude to learn new cutting edge technologies.

TECHNICAL SKILLS

ETL tools: Datastage 8.x/7.x (Administrator, Designer, Director)

Data Modeling: Dimensional Data Modeling, Star Schema, Snow - Flake Schema, Physical and Logical Data Modeling.

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

Programming: SQL, Unix Shell Scripting, PL/SQL

Other Tools: IBM Tivoli Workload Scheduler, Autosys, Microsoft Visio, TOAD, SQL Developer and Putty.

PROFESSIONAL EXPERIENCE

Confidential, Columbus, OH

Sr. DataStage Developer

Environment: DataStage 8.5/8.1, Oracle 11g, UNIX, TWS, HP Quality Center

Responsibilities:

• Responsible for designing and developing ETL jobs using websphere datastage involving mappings with complex transformation, join and aggregation rules, and with multiple sources and targets in different environments.
• Extensively worked in creating table design and maintained the ETL functional mapping documents.
• Extensively used stages such as Oracle Connector (8.5), Oracle enterprise (8.1), Transformer with loop function, Sparse and Range Lookups, XML input in addition to normally used stages as join, merge, filter, aggregator, funnel, dataset, sequential file etc
• Performed extensive research and implemented the slowly changing dimension logic to suit the project business rules using initial and incremental loads.
• Derived a logic for initial reload of data into the staging table for a scenario to reload data before a past cut off time.
• Derived and implemented a method to capture rejected records from all ETL jobs into a specially designed reject staging table.
• Derived and implemented a method to insert rejected records from previous loads along with new data using staging variables in Transformer stage.
• Designed a Batch run history table, and derived and implemented a method to define incremental load start time and load end time, for each table to rollback and run ETL from previous load start time, in case if the job fails..
• Implemented the logic to update the loaded, rejected and unloaded records of the staging table using multiple instance of a single update job
• Developed many complex Job Sequence utilizing user Variable Activity, Terminator Activity, StartLoop, EndLoop, Notification Activity, Routine Activity etc
• Used unix scripts for passing parameters into the Job sequence and also developed Bath run tables to update load start timestamp and load end timestamp.
• Created backup of Datastage project into clearcase software and restored as and when required.
• Used IBM Tivoli Workload Scheduler for scheduling the jobs.
• Responsible for Business Analysis and Requirements Collection to develop ETL to extract data from XML Clob using latest XML Input stages of 8.5 version
• Tuned oracle SQL queries by creating indexes on the key column
• Involved in creating PL/SQL Stored Procedures, Packages, and Triggers for the application, tuning SQL queries and the database.
• Performed tuning on sluggish ETL jobs by introducing manual partitioning, reducing stages, tuning Source and Target SQL queries, creating indexes on tables etc.

Confidential, Dallas, TX

Sr. DataStage Developer

Environment: DataStage 8.5/8.1, Oracle 10g, UNIX.

Responsibilities:

• Participated in all phases of project cycle including Requirement Analysis, Client Interaction, Design, Coding, Testing, support and Documentation.
• Extensively worked in creating table design and creating the ETL functional mapping documents.
• Develop ETL jobs using the Data Stage 8.5/8.1, write oracle PL/SQL scripts, and UNIX shell scripts as per the functional and technical specs provided by the client.
• Designed complex DataStage Jobs, sequences and tuned them for better performance.
• Responsible for preparing ETL Documentation for the developed processes.
• Extract data from different source systems (Mainframes, Flat files, Oracle DB) move them into the warehouse and mart.
• Developed many UNIX shell scripts to automate the Data Load processes to the Target.
• Responsible for handling Production Support tickets and creating AutoSys scheduling jobs.
• Schedule the jobs developed as per the time mentioned by the business users and monitor the jobs and fix the issue if there is any job failure.
• Co-ordinate the offshore dev and testing teams.
• Developed some generic Parallel routines and wrapper scripts which are used by our teams in almost all other processes.
• Involved in preparing Integration test cases, UAT test cases and involved in UAT testing.
• Involved in tuning many Oracle scripts and other ETL processes used in this project.
• Involved in conducting knowledge sharing sessions for end-users, to business and to offshore team members
• Involved in Stream lining the development guide lines for using new Datastage features.
• Responsible for doing the peer reviews, Planning and estimating the project requirements and to report the status to business managers.

Confidential, Roseville, CA.

Datastage Developer

Environment: DataStage 8.1/7.5x2, Oracle 10g, UNIX, AutoSys, HP Quality Center.

Responsibilities:

• Responsible for designing and developing ETL jobs using websphere datastage.
• Created Documentation for the developed jobs.
• Extensively worked with Datastage - Manager, Designer and Director to load data from flat files and Oracle to target databases.
• Designed and Developed Unix scripts to load the data into the table on a particular database.
• Tuned ETL jobs for performance improvement.
• Written Stored Procedures, SQL scripts and calling them at pre and post session.
• Automated the jobs using Autosys as per project needs.
• Prepared Unit test cases and UAT test cases.
• Maintaining warehouse metadata, naming standards and warehouse standards for future application development.
• Implemented the Functional Database Design based on the business needs.
• Developed many reusable routines and shared containers for reducing the development effort.
• Tuned oracle SQL queries by creating indexes on the key column.
• Utilized the stages of Job Sequence such as User Variable Activity, Notification Activity, Routine Activity, Terminator Activity, etc
• Developed Shell Scripts for event automation and scheduling.
• Attended training sessions on developing reports using IBM Cognos 8 in Report Studio.
• Knowledge on Developing prompts and customized prompts using Cognos Report Studio.
• Knowledge on Developing reports to be used as drill down reports from Powerplay Transformer.

Confidential, Boston, MA

Datastage Analyst

Environment: Data stage 7.5, Shell Scripting, SQL 2000 and UNIX

Responsibilities:

• Monitored the Data-stage engine. Tuned and configured for better performance.
• Developed parallel, server jobs and job sequences using DataStage PX.
• Developed job specifications and unit test cases for parallel jobs and job sequences.
• Guided Onsite and Offshore project team on technical issue resolution.
• Worked with DataSets, Lookup, Merge, Funnel, Transformer, MQ Stages, Wrapper Scripts etc.
• UsedParallelExtenderfordistributingloadamongdifferentprocessorsbyImplementingpipeline andPartitioningofdatainparallelextender.
• Worked with architects on Dimensional Modeling Techniques to create Dimensions and Fact tables.
• Developed the transformation logic to load data into Datawarehouse.
• Applied patches, Datastage stages and upgrade to latest version Backup of Datastage project .
• Utilized the stages of Job Sequence such as User Variable Activity, Notification Activity, Routine Activity, Terminator Activity, etc.
• Developed various shared containers, stages and validated and fine-tuned the ETL logic .
• Responsible for validating the Datastage jobs against the pre-defined ETL design standards.

Confidential, Emeryville, CA

Datastage Developer

Environment: Data stage 7.5 PX, UNIX, SQLServer2000, Oracle 9i, Control M 6.2

Responsibilities:

• Analyzed the requirements to identify the necessary tables for the staging database.
• Prepared the DDL’s for the staging/work tables and coordinated with DBA for creating the development environment and data models.
• Involved in analyzing the source data from different Data sources such as oracle, flat files etc.
• Used DataStage to extract data from source systems, aggregate and load into the oracle database.
• Extensively used database procedures and triggers.
• Suggested table partitions for parallel load processing.
• Involved in defining technical and functional specifications for ETL process.
• Used both pipeline and partition parallelism for improving performance.
• Extensively used third party tool which is Control M for job scheduling.
• Used lookup stage with reference to oracle tables for updating of slowly changing dimensions.
• Involved in performance tuning of the ETL process and performed the data warehouse testing.

Confidential 

Datastage Developer

Environment: Datastage server, Oracle 9i, Windows 2000, SQL, PL/SQL Toad 7.0.

Responsibilities:

• Involved in the Analysis of Physical Data Model for ETL mapping and the process flow diagrams.
• Used different stages such as the on Sequential File, Transformer, Aggregator, Lookup, Join, Filter etc.
• Created and maintained ETL documentation that supported business rules and detailed source to target data mappings.
• Wrote PL/SQL procedures, SQL Scripts, and SQL*Loader control files for creating a development database.
• Created data governance strategies that reduced data redundancy wherever possible.
• Developed mappings to load data in slowly changing dimensions.
• Investigated and assisted users in the resolution of any day-to-day data warehouse issues.
• Extensively involved in writing of Functions and Stored Procedures.