- Over 7 years of IT experience in System Analysis, Business Analysis, Design, Development, Testing, Implementation and Integration of various Data Warehouse Systems.
- About 6 years hands on experience working in Data Warehousing using Ascential/ IBM DataStage 8.1/8.0/7.5.x/7.0 (Designer, Director, Manager, Administrator, PX and Information Analyzer).
- Extensive experience in designing ETL jobs (Parallel Jobs, Server jobs and Sequence jobs) using DataStage.
- Extensively worked on different kinds of DataStage stages like Sequential File, Complex Flat File (CFF), XML, Dataset, Lookup Fileset, Copy, ODBC, Lookup, Join, Aggregator, Sort, Merge, Filter, Transformer, Funnel and Database Native stages.
- Experience in gathering functional, technical requirements and preparing Data Mapping documents, ETL Design Specifications and Test case documents.
- Experience in Data integration and Data migration projects with (source-Target) same database as well as with heterogeneous databases.
- Expert in Data Warehousing techniques for Data Cleansing, Slowly Changing Dimension Phenomenon(SCD), Surrogate key assignment and CDC (Change Data Capture).
- Experience in using Information Analyzer for Column Analysis, Primary Key Analysis and Foreign Key Analysis.
- Expert at Data warehousing concepts, Relational Database Management Systems and Dimensional modeling techniques (Star schema and Snowflake schema).
- Expert in developing SQL coding for ad hoc queries and database development.
- Experience in writing, testing and implementation of procedures, functions, triggers and packages at Database level using PL/SQL.
- Performed Debugging, Troubleshooting and Tuning of DataStage Jobs.
- Extensively used Autosys to schedule batch jobs.
- Excellent interpersonal communication skills and possesses the ability to work effectively in a team environment.
- Excellent in Documentation, End-User Training and Knowledge Transfer.
IBM Information Server/DataStage 8.1/8.0/7.5/7.0 (PX, Information Analyzer, Fastrack)
Oracle 8i/9i/10g, SQL Server 2005, DB2 UDB 9.1/8, Sybase, Teradata
Linux 3.x/4.x, Sun Solaris 8/9/10, IBM AIX 5.1/5.2, Windows 98/2000/NT/XP/7, MVS(OS/390)
SQL, PL/SQL, UNIX Shell Scripting, C, Java
SQL* Loader, SQL*Plus, SQL Tools, Erwin, CA Workbench, Toad, SQL Navigator, MS Visual Studio, WinSQL, SQL Developer, Putty
- Bachelor of Technology
Confidential, NC/CA Sep ‘09—Tilldate
WLS Data Mart
Confidential. is a leading financial services company. Wholesale loan services data mart application processes data using DataStage and provides BI reporting of SLA, Volume and Quality to management in commercial and real estate loan centers. Data mart is developed to capture all the activities served by team members across different loan centers to keep track of the Volume, SLA and quality.
- Involved in Data analysis, interacted with business analyst to understand and gathering the requirements.
- Developed source to target mapping documentation based on the business requirement specifications.
- Involved in designing DataStage jobs (Parallel jobs, Server jobs & Sequence jobs) to load data into Data mart.
- Developed/Enhanced ETL Processes to load the data from different source systems including Oracle, SQL Server, Teradata, MS Access, MS Excel, Flat files into Data mart using DataStage.
- Importing and Exporting repositories across the projects, Datastage Componets and table definitions using Datastage Designer.
- Developed complex mappings in DataStage to load the data from various sources using different transformations like Join, Merge, Lookup, Change Capture, Remove Duplicates, Funnel, Filter, Copy, XML, Peek, Pivot, Aggregator, Modify, and Transformer Stages.
- Created error handling and reject file process to capture rejects for data analysis and data re-process.
- Extensively worked with Job sequences using Job Activity, Routine Activity, looping, Email notification and Execute Commands.
- Developed stored procedures and Functions using PL/SQL statements.
- Developed user defined routines for implementing complex business logic.
- Developed UNIX scripts to automate the Data Load processes to the target Data warehouse tables.
- Developed DataStage jobs using Change Capture Stage to compare the data changes between source tables and data mart tables.
- Used Autosys for job scheduing, developed Autosys jil scripts.
- Performed Unit Testing, Integration Testing and User Acceptance Testing (UAT).
- Documented the purpose of mapping so as to facilitate understanding the process and incorporate the changes when necessary.
Environment:IBM Information Server/DataStage 8.1/7.5 Oracle 10g, MS SQL Server 2005, DB2/UDB, AutoSys, Toad, SQL Server Management studio, PL/SQL, Shell Scripting, Windows 2003/XP and UNIX (Sun Solaris).
Confidential., CA Jul ’08 —Aug ‘09
Confidential is a leading financial services portal containing a unique breadth and depth of products and services. Banks.com Inc provides Internet search services through a combination of traffic aggregation and proprietary websites. The company operates in various areas of Internet commerce, including paid search, direct navigation, and online marketing. It owns irs.com, banks.com, camps.com, looksearch.com, upspiral.com and implemented a new Data Warehouse for business intelligence reports.
- Identified source systems, their connectivity, related tables and fields and ensured data suitability for mapping.
- Developed DataStage jobs (Parallel jobs, Server jobs & Sequence jobs) to extract data from sequential files, flat files, mainframe files, DB2 and Oracle SQL Server and other sources to load into Warehouse Database.
- Extensively used processing stages like Join, Merge, Funnel, Filter, Aggregator, Sort, Transformer, Lookup, File Set, Data Set, XML, Complex Flat File and Sequential File, Checksum, ODBC, and Native Database stages etc,.
- Extensively worked on Surrogate key Generator to generate Warehouse key before loading data into Warehouse.
- Expert in using Information Analyzer for Column Analysis, Primary Key Analysis and Foreign Key Analysis.
- Developed error handling process to collect rejects for data analysis.
- Worked on Optimizing and Performance tuning on targets, sources and mappings to increase the efficiency of the process.
- Developed stored procedures using PL/SQL statements for extracting as well as writing data.
- Extensively used Datasatage Director to execute, monitor execution status and log view, also used in scheduling jobs and batches.
- Preparation of Unit Test cases and Unit test results document and end–end testing of the jobs. Preparation of Integration and system level testing scenarios.
- Created job schedules to automate the ETL process.
Environment: IBM WebSphere Enterprise DataStage 8.0/7.5.2, Oracle, MS SQL Server 2005, Sybase, Toad, PL/SQL, CA Workbench, 24/7 Scheduler, Linux and Windows XP.
Confidential, CA Dec ‘07—Jun ‘08
National Pricing System Application
Confidential is America\'s leading integrated health care organization. Founded in 1945, it’s a nonprofit health care, with headquarters in Oakland, California. KP serves the health care needs in nine states and Washington, D.C. Confidential implemented an application called National Pricing System (NPS) to automate the quote process for their customers. NPS is utilizing IBM WebSphere DataStage, DB2 and SQL Server for ETL processes on Unix (AIX) and Mainframe Environment.
- Involved in Business Analysis, Data Analysis and requirements gathering.
- Involved in developing ETL mapping specification and design documentation.
- Developed various Data extractions, Transformations rules based on Business Requirements.
- Involved in extracting the data from different data sources like Oracle, DB2, several other databases and flat files.
- Developed parallel jobs, server jobs using different stages, which includes Aggregator, Join, Transformer, Sort, Filter and Lookup etc.,
- Used the DataStage Director and Manager to schedule running the solution, testing and debugging its components and monitoring the resulting executable versions.
- Developed QualityStage jobs using the stages including Standardize and Investigate.
- Used QualityStage Jobs to cleanse the data, by calling it from Datastage.
- Written functions, transforms, before and after job routines using DataStage basic.
- Extensively used and processed mainframe files (ASCII/EBCDIC) to load data.
- Developed UNIX Shell scripts to automate the Business processes.
- Created Datastage Sequencers using Job Activity, User Variables Activity, Execute Command Activity, Notification Activity, Sequencer activity and Nested Condition activity which house Datastage jobs.
- Extensively used Datasatage Director to execute, schedule the jobs and monitor execution status and log view of batch jobs.
- Performed Unit Testing and Integration Testing.
- Prepared documentation for Jobs.
Environment:IBM WebSphere Enterprise DataStage 7.5, Oracle 9i, DB2/UDB 8.1, Teradata V2R5, vWindows XP, UNIX (AIX 5.3) and MVS (OS/390).
Confidential, CA Apr ‘06— Nov ‘07
Confidential are one of the nation’s largest health care services companies. Primary operation includes health insurance products for individual and families. This is an Enterprise Data warehouse project. DataStage is used to extract the data from ODS tables, which are used by end users for obtaining the relevant data. Data is extracted from multiple data sources and transformed through the processes of translation and loaded into the oracle and SQL Server tables.
- Involved in entire product life cycle development from the requirement gathering through implementing in production.
- Involved in designing and development of data warehouse environment.
- Designed and developed DataStage jobs (parallel and server) to transfer data from source to data warehouse.
- Extracted data from various source systems like Flat files, MS Excel, Sequential files, XML files, Mainframe files, Oracle and SQL Server.
- Extensively used DataStage Manager in Importing and Exporting repositories across the projects, table definitions.
- Designed and created Parallel Extender jobs which distribute the incoming data concurrently across all the processors, to achieve the best performance.
- Extensively used different processing stages like Transformer, Join, Merge, Pivot, Aggregator to develop parallel jobs, server jobs.
- Created hash files for the best performance of Datastage jobs.
- Extensively worked with Job sequences using Job Activity, Nested loop, Execute Commands and Notification.
- Developed the audit components, which identify the source and target table records to audit each load.
- Developed and utilized the stored procedures, functions, packages using PL/SQL.
- Created local and shared containers to facilitate ease and reuse of jobs.
- Involved in the unit testing and UAT of provider and if Datastage jobs.
- Prepared documentation for load plan of the tables.
Environment: Ascential DataStage 7.5(PX), Oracle 9.0.2, SQL Server 2005, Sybase, Toad, PL/SQL, Shell Scripts, UNIX, Windows 2000.
Confidential, NJ Jul ’05 — Mar ‘06
Confidential, "The World's Networking Company." For more than 125 years, Confidential has been known for unparalleled quality and reliability in communications backed by the research and development capabilities of Confidential Labs, commitment to innovation has made AT&T a global leader in local, long distance, Internet, and transaction-based voice and data services. Confidential built a Data Warehouse to provide reports for the current and future invoices/contracts related situation/needs of the customers.
- Involved in analysis and requirement gathering.
- Involved in developing ETL Process to Load the Data from the Production Databases to the Data Warehouse using DataStage.
- Used DataStage Designer to develop jobs for extracting, cleansing, transforming, integrating, and loading data into data warehouse database.
- Developed Parallel, Server and Sequential Jobs using DataStage.
- Extensively used Sort, Merge, Join, Merge, Transformer, Lookup, Aggregator and remove Duplicate Stages.
- Developed ETL Processes to load the data from different source systems including Oracle, SQL Server, MS Access, MS Excel, Flat files into Data warehouse.
- Used DataStage Manager for importing metadata from repository, new job categories and creating new data elements.
- Extensively used Datasatage Director to schedule and execute the jobs and monitor execution status and log view.
- Developed UNIX shell scripts to automate file manipulation, data loading procedures, Data Conversion and Scheduling jobs.
- Involved in writing user documents and training.
Environment: Ascential DataStage 7, Oracle 9i/8i, MS SQL Server 7, DB2 7.2, MS SQL Server, Toad, Shell Script, AutoSys, Windows NT and UNIX (Solaris).
Confidential, NY Nov ’04 – Jun ‘05
Affiliate Management Program
Data Analyst/Database Developer
Confidential provides a broad range of thoughtful gift products including flowers, gourmet foods, candies, gift baskets and other unique items to customers around the World. Customers can purchase Confidential products online via company’s web site, Confidential as well as through online relationships with such leading ISP’s as AOL, Yahoo and other Affiliate Program Partners.
- Involved in data analysis and requirements gathering.
- Involved in ad hoc query solution development.
- Involved in Database design, Front end design and implementation of Affiliate management system.
- Involved in data validation and integrity to provide Sales, Commission Structures, target reminders, Multi mailing system and special offers to their partners, etc.,
- Developed database procedures, functions for data conversion and data feeds for affiliate partners.
- Created test Cases as per the business requirements and executed them.
- Maintained Affiliate program Enrollment, Agreements, licenses, sales and commission details.
- Provided Test Data to QA team to validate front end application, also performed unit testing.
- Involved to prepare user level and system level maintenance documentation.
Environment: Oracle 8i, PL/SQL, Toad and Windows NT.