- Over 7+ years of experience in Analysis, Design and Development and implementation of Relational Database (OLTP) and Enterprise Data Warehousing Systems (OLAP) using IBM Websphere DataStage, ORACLE, DB2 UDB, SQL Sever and Teradata.
- Over 7 years of experience in Ascential DataStage Server and Enterprise edition (Parallel Extender).
- Strong working experience on Ascential Datastage 7.5.2/7.5/7.1/7.0/6.0, IBM InfoSphere DataStage 8.1, 8.0.1, 8.5, SQL, PL/SQL, Stored Procedures and Triggers. Performed Debugging & performanceTuning.
- Expertise in translating business requirements into Data Warehouse and Data Mart design and developing ETL logic based on the requirements using DataStage.
- Expert in dimensional modeling, Star Schema modeling, Snowflake modeling, fact and dimension table design, physical and logical Data modeling using Erwin 4.0.
- Worked on various operating systems like UNIX, Sun Solaris and Windows.
- Designed and developed jobs using Parallel Extender for splitting bulk data into subsets and to dynamically distribute data to all available nodes to achieve best job performance.
- Expert in unit testing, system integration testing, UAT, implementation, maintenance.
- Proficiency in data warehousing techniques for data cleansing, Slowly Changing Dimension Type II phenomenon, surrogate key assignment and change data capture.
- Good experience on multiple Operating Systems (Windows NT/2000/9x, UNIX, AIX), Shell Scripting.
- Proficient in Data analysis, Data modeling, Database design and Data migration.
- Worked extensively on different types of stages like Join, Lookup, ODBC, XML Pack (XML Input, XML Output, XML Transformer), Hashed File, Aggregator, Transformer, Merge, Sort, Pivot, FTP, Change Data Capture, Filter, Modify, Shared Containers (Server, Parallel) for developing jobs.
- Used the DataStage Director extensively to run, schedule, monitor, debug and test the application on development, and to obtain the performance statistics.
- Good working knowledge of various Databases like Oracle 10g/9i/8i/7.x, DB2 UDB and SQL Server.
- Unique ability to understand long-term project development issues at all levels, from interpersonal relationships to the details of coding scripts with strong analytical, organizational, presentation and problem solving skills and Used Agile Development Methodology for project requirement gathering in advance.
Technical SkillsETL Tools
IBM InfoSphere DataStage 8.1, 8.0.1,8.5, Ascential DataStage 7.5.2/7.1/7.0/6.0, SQL*Loader, Quality stage, Profile Stage, Bulk Loader.
Oracle 10g/9i/8i, SQL Server, Teradata, DB2 UDB, MySQL, MS- Access
Shell Scripting, K-Shell, C, C++, SQL, PL/SQL, Mainframe Cobol
Unix Shell Scripting
UNIX, Win 95/NT/98/2000/XP,MS-DOS, Sun Solaris 2.x, IBM AIX 4.2.
Confidential, CA - Sunnyvale Mar 11 - Till date
Role: Sr. DataStage Consultant
- Interacted with Business users and Technical Architects to analyze the data & gathering the requirements from various sources.
- Developed jobs using DataStage to extract data from different operational Sources like Flat Files, CSV Files, delimited Files, XML Files and COBOL Files and performed business operations on data like Cleansing, Transforming and Load (Initial/Incremental) into Target DWH.
- Involved in daily meetings with the client for requirements and provide services to meet the required SLAs.
- Used the DataStage Director for monitoring the jobs and debugging the Issues.
- Created Design Documents and Unit Test cases with test Results Documents.
- Developed JOB using DataStage for Data Cleansing, Extraction and Transformation.
- Developed JOBS to load the data into the Warehouse environment using the Slowly Changing Dimension techniques.
- Check the output according to the specifications using Unit Test.
- Developed JOBS using Data Stage for Data Cleansing, Extraction and Transformation as per ETL Specifications and Performance Tuning of the JOBS.
- Developed JOBS to load the data into the Warehouse environment.
- Basically involved in providing Support to the built Application.
- Loading the data into the Staging and moving the data from Staging to ODS.
- Involved in Business Requirement gathering sessions
- Designing of the ETL jobs as per the client’s requirements.
- Involved in Unit, Integration, system, and performance testing levels and Involved in Performance tuning at source, target, jobs and system levels and Testing Jobs with Unit Test plan.
- Integrate the data from the Source system into the target database.
Environment: IBM InfoSphere DataStage 8.1,8.5, OBIEE, Teradata, Oracle 10g, Win 2000/NT and AIX UNIX
Confidential, CA - San Ramon Aug 10 – March31
Role: DataStage Consultant
- Involved in requirement gathering, analysis and study of existing systems
- Involved in preparing technical design/specifications for data Extraction, Transformation and Loading.
- Provide technical/user documentation and training.
- Extensively used DataStage Designer to develop various jobs to extract, cleanse, transform, integrate and load data into database tables.
- Worked with DataStage Manager to import/export metadata, jobs, and routines from repository and also created data elements.
- Scheduled the server jobs using DataStage Director, which are controlled by DataStage engine and also for monitoring and performance statistics of each stage
- Created Technical Specs and SQL document for the DataStage Jobs, Developed several Test Plans, Unix Scripts for Unit/Team Testing and Error Logs / Audit Trails were maintained.
- Implementing performance-tuning techniques along various stages of the ETL process
- Created Logical process flows and physical process flows of business requirements using Visio.
Environment: IBM InfoSphere DataStage 8.1.0, DataStage 7.5, SQL Server, Oracle 10g, Shell Scripts, Win 2000/NT and AIX UNIX .
Confidential, Weehawken-NJ Feb 10 – Aug 10
Role: ETL Developer
- Involved in Design, Source to Target Mappings between sources to operational staging targets, using Star Schema, Implemented logic for Slowly Changing Dimensions.
- Participated in the review of Technical specs, Business Transformation Requirements Documents and high level process maps.
- Used local and shared containers to increase Object Code Reusability and to increase thru put of the system.
- Involved in unit testing, system testing, UAT and integration testing.
- Participated in the review of Requirements Documents and mapping documents.
- Import/Exported source code and executables using DataStage Designer Client
- Created data jobs to extract data from different source files, transform the data using stages like Transformer stage, Aggregator to classify rows from a single input link to groups, Remove Duplicate stage to remove duplicate rows, Sort stage to sort the data in a particular order, Copy stage to copy data into more number of links, Modify stage to alter record schema of its input data set, Filter stage to filter the data, Join stage and then load to data warehouse.
- Extensively wrote user-defined SQL coding for overriding for Auto generated SQL query in DataStage.
- Used the Slice and Dice feature, set variables, alerts and group values in the reports with combined Queries.
Environment: IBM InfoSphere DataStage 8.0, Business objects, SQL Server, Oracle, Win 2000/NT and AIX UNIX
Confidential,CA- Sanjose Jul 09 –Jan 10
Role: ETL Consultant
- Created number of complex ETL jobs in this life cycle and Involved in both Functional and Technical side.
- Involved in the analysis & design of the Data Warehouse model with Facts and Dimension Tables.
- Developed and supported the Extraction, Transformation and Load process (ETL) for a data warehouse from various data sources using Designer
- Worked in importing and cleansing of data from various sources like flat files, excel files onto SQL Server with high volume data.
- Work closely with Project lead/Manager, Architects, and Data Modelers to understand the business process and functional requirements.
- Developed SQL scripts to augment the ETL process and to check on final target data involved in Program Specification document preparation.
- Extensively using FASTLOAD, MLOAD, BTEQ utilities in Teradata
- Developed UNIX shell scripts to automate the Data Load processes to the target Data warehouse.
- Created Mappings and workflows and tuned them for better performance.
Interacted with data model architect in developing data mart design with Facts and Dimension tables for data warehouse and reporting purpose.
Environment: Informatica, Oracle, Shell Scripts, Toad, Win 2000/NT and AIX UNIX
Confidential,CT Oct08 –July 09 Role: DataStage Developer
- Design and Develop Enterprise edition Jobs based on the specification.
Developed and supported the Extraction, Transformation and Load process (ETL) for a data warehouse from various data sources using Ascential DataStage Designer
- Designed and developed Parallel jobs to extract data, clean, transform, and to load the target tables using the DataStage Designer and Used XML Meta Data Importer to import XML structure from XML or XSD files.
- Involved in the development of Data stage parallel jobs using various stages and Sequencers and prepared scripts to run them, Involved in creation of Sequencers using Data stage Designer.
- Involved in Testing, UAT and integration testing along with business team.
- Extensively used the XML Stages like XML Input, XML output and XL Transformer stages to read and write the XML data source to the Target and Created classes, sub classes, objects in development of universe in BO.
- Experience in using XML metadata importer to import XML table definitions from the XSD document and identifying the repetition elements.
- Designed jobs to read VSAM files using CFF stage, while dealing with mainframe data sources.
- Extract data from staging table, lookup into conversion tables to get Market code, Product Type, Line code, Insurance Type codes. This is done to convert the values coming from the source for MKT, PROD, INS and LINE to Codes via MKT_CD, PROD_TYP_CD, INS_TYP_CD and LN_CD. Records failed during the conversion table lookup operation will be collected in an error table.
- Extensively involved in Quality Stage (Integrity Stage) for quality checking and error detection process.
- Perform lookups on the actual dimension tables to check for Referential Integrity (RI).The actual codes from the conversion lookups are matched for referential integrity in the dimension and the key values are pulled from the dimension table. Records failed during the RI lookup operation will be collected in an error table.
Environment: AscentialDataStage 7.5.2, SQL, PL/SQL, Shell Script, TOAD , ERWIN 4.2, Win 2000 and AIX UNIX.
Confidential,NY Feb 08 - Aug 08
Role: DataStage Developer
- Involved in gathering business requirements for reports and cubes and also came up with standard Requirement gathering documents.
- Extensively used DS Designer to develop various jobs to cleanse, transform, integrate and load data into target tables.
- Developed logical and physical source to target mapping documents for Data warehouse as well as Data mart, so as to translate business rules into technical specifications.
- Performed debugging for SQL scripts and developed complex to simple scripts to implement business logic.
- Shell Scripts were written to schedule the job.
- Created Interactive Dashboards for the business users to analyze the future of the commodities in present market and new trading opportunities.
- Expertise in Fast load and TPUMP in Teradata
- Improved the performance of the jobs by using Performance Tunings.
- Review of Technical, Business Transformation Requirements Document and ETL Documents.
Environment: Ascential DataStage 7.5, SQL, Oracle,Erwin, Windows 2000, AIX UNIX.
Confidential,CT – Hartford Apr 07 – Jan 08
Role: DataStage Developer
- Involved in the analysis & design of the Data Warehouse model with Facts and Dimension Tables using Erwin Data Modeler and creating Entity-Relationship diagrams.
- Involved in gathering the business requirements and create the detailed module specifications and source to target mapping and writing test cases.
- Interacting with Business users to analyze the data & gathering the requirements from various sources.
- Extensively used DataStage Designer to develop various Parallel jobs to extract, cleanse, transform, integrate and load data into Enterprise Data Warehouse tables.
- Responsible for High Level Design and Low Level Design for all the components.
- Worked with DataStage Manager to import/export metadata, DataStage Components between the projects.
- Improved the performance of the jobs by using Performance Tunings.
- Responsible for adopting the company Standards for Stage & Link Naming Conventions.
- Extensively worked with DataStage Shared Containers for Re-using the Business functionality.
- Actively participated in the Team meetings to gather the business requirements and developing the Specifications.
- Participated in discussions with Team leader, Group Members and Technical Manager regarding any technical and Business Requirement issues.
- Participated in the review of Technical, Business Transformation Requirements Document.
- Create master controlling sequencer jobs using the DataStage Job Sequencer.
- Programmed Stored Procedures using SQL, PL/SQL to extract client data from Oracle 8i and mapped the data into a target Data warehouse.
- Performed unit testing, data validation and testing of jobs using debugger to test the data flow and fix the bugs.
Environment: Ascential DataStage 7.5/7.1, Oracle, Teradata, SQL, PL/SQL, TOAD, ERWIN, Win 2000/NT and HP UNIX
Confidential, CA Nov 06 – Mar 07
Role: ETL Developer
- Involved in gathering requirements for jobs and created mapping documents for all jobs.
- Involved in identifying the mapping between databases and data items from source systems to target systems and designing the process diagram.
- Identification of various Data Sources like Relational databases, Application systems and Flat files to extract, transform and load data from these sources into Data warehouse.
- Integrating data into data warehouse tables, from various sources like flat files, relational databases like Oracle, DB2 UDB and SQL Server etc and Improve DB2 query performance using the REOPT bind option
- Extensively used the Modify, DataSet, DB2 Enterprise, Filter, Funnel, MQ Series, JOIN, Lookup, Copy, Aggregator, Change Capture and other Database plug-ins to do transformation and load the data.
- Extensively worked with Job Sequences to control and execute the DataStage jobs using Job activity, Exec Command, Routine, Sequencer, Exception Handler, and Wait for file and Triggers.
- Used DataStage Director and its run-time engine to schedule, validate, run and monitor the resulting executable versions.
- Used DataStage Manager for creating new job categories, metadata definitions, and data elements, import/export of projects, jobs and DataStage components, viewing and editing the contents of the repository as well as writing routines.
- Used Before and After Stage subroutines to further enhance performance of the system.
Environment: AscentialData Stage 7.1, Oracle 9i, DB2 UDB, SQL Server, SQL, PL/SQL, MQ-Series, Oracle Designer, Erwin 4.0, SQL *Loader, Windows 2000, AIX UNIX.
Confidential,India Jul 05 – Sep 06
Role: Data Warehouse Developer
- Interacted with the end users in finalizing the requirements and assisted in preparing the Functional Requirements Documents.
- Developed Jobs using Ascential DataStage 7.x to Extract and load relational data into Oracle9i, DB2UDB Databases
- Implementation of Surrogate key by using Key Management functionality for newly inserted rows in Data Warehouse and Designed complex job control processes to manage a large job network.
- Defined reference lookups and aggregations, external procedures and update transformations according to the business rules.
- Worked extensively on different types of stages like Sequential file, ODBC, Hashed File, Aggregator, ORA Bulk, Transformer, Merge, Join, Lookup, Sort and Containers (Server, Parallel) for developing job.
- Performed data manipulation using BASIC functions and DataStage transforms.
- Used the DataStage Designer to develop processes for extracting, transforming, and loading data into data warehouse database.
- Used DataStage Manager for importing metadata from repository, new job categories and creating new data elements.
- 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).
- Worked on programs for scheduling Data loading and transformations using DataStage from legacy system to Oracle 8i using SQL* Loader and PL/SQL.
- Assisted Systems Administrator in DataStage installation and maintenance.
- Enforcing data Integrity rules/ Business Rules. Maintaining huge volume of data.
- Involved in the process design documentation of the Data Warehouse Dimensional Upgrades. Designed DataStage Server jobs and Server shared containers using Transformer, Aggregator, Pivot, and Hash file, Sequential file, ODBC stages.