- 7+ years of Data Warehousing experience in the Design, Development and Testing of ETL methodologies in all the phases of the Data Warehousing life cycle.
- Extensive knowledge in translating business requirements into Data Warehouse and Data Mart design and developing ETL logic based on the requirements using DataStage.
- Worked extensively with different stages of DataStage like Oracle, DB2, Datasets, Aggregator, Transformer, Merge, Join, Lookup, Funnel, Filter, Sort, Pivot, Change capture, Remove Duplicates, Sequential file, Copy, Peek, and Shared Containers for developing parallel jobs.
- Efficient in in corporation of various data sources such as Oracle, SQL Server, and DB2, XML and Flat files into the staging area.
- Wrote various SQL to perform data profiling and to extract data out of tables and do joins as per requirements and requests.
- Expert in SQL and RDBMS.
- Experience in using UNIX shell scripts for job automation, grep, awk & sed commands.
- Familiarity with ProfileStage and MetaStage for further repository management.
- Use the DataStage Director extensively to validate, run, schedule, monitor, debug and test the application on development, and to obtain the performance statistics.
- Good understanding of Data Warehouse and Business Intelligence concepts and fundamentals e.g. keys, OLTPs. Vs. DW structures, normalization, fact and dimensional tables, star & snowflake schemas etc
- Expertise on Oracle development tool set (Including PL/SQL, SQL*Plus, SQL Loader, PL/SQL developer, TOAD, SQL Explorer).
- Worked on Oracle pre defined functions, Joins, sub queries to extract the data from oracle database by using DataStage ETL.
- Experienced in scheduling Sequence and parallel jobs using DataStage Director, UNIX scripts and scheduling tools such as Control M.
- Experience in troubleshooting of jobs and addressing production issues like data issues, performance tuning and enhancements.
- Experience in using Erwin as Data modeling tool for logical (LDM) and physical data model (PDM).
- Involved in conceptual, logical and physical data modeling processes.
- Technical and analytical skills with clear understanding of ER modeling for OLTP and dimension modeling for OLAP.
- Avoided repartitioning of data and maintain load balancing by using proper partition technique in DataStage.
- Extensive experience in unit testing, system integration testing, implementation, maintenance and performance tuning.
- Detail oriented with good problem solving, organizational, analysis and requirement gathering skills. Strong interpersonal skills and leadership abilities. Excellent communication skills, Good organizational skills, outgoing personality, Self - motivated, hardworking, ability to work independently or cooperatively in a team, eager to learn, ability to grasp quickly.
ETL Tools/ data base: Oracle 11g,10g,9i,DB2-SQL SERVER 2005/2008/2012/2014, IBM InfoSphere Datastage 8.7/8.5/8.1/7.5
Languages: Sql, Pl/sql, C, Unix-shell scripting
Database Tools: iSql plus, Sql developer, Toad
Data Modeling Tools: Erwin 4.0-7.3
Scheduling Tools: DS-director, Crontab, Control M
DataStage Tools: IBM DataStage designer, director, administrator
Environment: s: Unix, Aix 5.2/5.3, Linux, WinNT 4.0
SDLC Methodologies: Waterfall/Agile
Confidential, Parts Roanoke, VA
- Working closely with Team leader, Group Members and Technical Manager regarding any Technical and Business Requirement issues.
- Extensively Using Partition Parallelism, the same job would effectively run simultaneously by several processing Nodes each handling separate subset of Total data.
- Using DataStage Director to view logs for any errors and warnings.
- Using Surrogate Keys to keep track of Slowly Changing Dimensions (SCD).
- Adaptable, flexible, and scalable architecture: handled high data volumes with common parallel processing technology, and utilized common services such as connectivity to access a wide range of data sources and targets.
- Extensively using Pivot stage to pivot the source data to achieve required table structures like converting data from rows into a column.
- Using development/debugging stages to test the environment by creating samples of data from given high volume data or by creating mock data.
- Importing and exporting repositories across projects.
- Implementing ETL applications using both Oracle database and SQL Server database as sources and targets.
- Collaborating with multiple business analysts, data modelers, data analysts and solution architects to successfully implement and maintaining data transformation, interface designs, data quality and standards.
- Interfacing and working with quality assurance teams, database and web administrators, and business users to maintain corporate protocol in successfully deploying applications through different environments up to production.
- Extensively working with various stages like sequential file, dataset, lookup, peek, transformer, merge, aggregator, row generator, surrogate key generator, slowly changing dimension(SCD) and many more to design jobs and load the data in to fact and dimension tables.
- Implementing Local Containers for same job and Shared Containers for multiple jobs, which have the same business logic .
- Using stage variables for better performance and to reduce the complexity of defining expressions
- Extensively using DataStage Director for Job Scheduling.
- Extensively working on Error Handling.
Environment: IBM Info Sphere DataStage 8.7 (Designer, Administrator, Director), Oracle 11g, SQL Server 2012/2014, Toad 9.6, SQL Plus, UNIX Shell Scripting, SQL Loader, Agile
Confidential, Columbus, OH
- Involved in the design and development of Data Warehouse.
- Worked closely with data modeler and database administrator to understand the business process of the data warehouse model.
- Used Parallel Extender for distributing load among different processors by implementing pipeline partitioning techniques.
- Diligently used job parameters and environment variables to provide the flexibility in the job designs.
- Developed UNIX Shell scripts to automate file FTP move to and from directory environments and to send status messages via SMTP email.
- Performed and provided support in fine tuning, trouble shooting, bug fixing, defect analysis and enhancement of the existing and aged DataStage jobs for the improvement of processing time.
- Applied thorough unit testing and functional testing in DEV, QA and PROD environments.
- Involved in Designing Parallel Extender Jobs.
- Worked with Business customers to identify the different sources of data in operational systems and developed strategies to build data warehouse.
- Developed several complex DataStage jobs for loading Participants to the data warehouse.
- Provided technical support to team members for design and developing DataStage Jobs.
- Developed various jobs using Oracle, Aggregator, Sequential file stages.
- Extensively used Parallel Stages like Join, Merge, Lookup, Filter, Remove Duplicates, Funnel, Row Generator, Modify, Peek, xml input/output etc. for development and de-bugging purposes.
- Used DataStage Director and the runtime engine to schedule running the jobs, monitoring scheduling and validating its components.
Environment: Ascential DataStage 8.5 (Designer, Director, Administrator), Oracle 10g, DB2, Teradata, UNIX Solaris, Windows NT, MS SQL Server 2005/2008
Confidential, Detroit, MI
- Extensively worked with Parallel Extender/Enterprise Edition (EE) using Parallel Processing (Pipeline and partition parallelism) techniques to improve job performance while working with bulk data sources.
- Involved in modeling database Schemas like Star Schema and Snowflake Schema used in relational, dimensional and multidimensional modeling.
- Participated in Design and development of Data model using Erwin.
- Designed and implemented slowly changing dimensions (SCD's) methodologies.
- Used different types of Stages like Sequential File, Lookup stage, Transformer, Change capture, Remove Duplicates, External source, XML input/output, Funnel, Sort, Aggregator, and ODBC for developing different Jobs.
- Wrote SQL/PLSQL Procedures to improve the Performance while extracting and loading tables.
- Migrated Jobs from Development to QA and to Production Environments.
- Wrote UNIX scripts for record count in a file.
- Scheduled the jobs using Control -M Scheduler.
- Analyzed the performance of the jobs and project and enhance the performance using standard techniques.
Environment: DataStage 8.1, SQL, Oracle 10g, DB2, Toad, Erwin, UNIX Shell scripting, Control-M.
- Involved in Development of Extract and Transform jobs using DataStage.
- Validated the source files with the help of Shell script then Extracted project related data from the source files (Flat files) with the help of DataStage jobs.
- Developed the DataStage jobs by using different stages like Transformer, Column generator, Sequential file, Dataset, Funnel, Filter, Sort, Aggregator etc.
- Involved in development of sequence jobs and Unix Scripting.
- Done the Unit testing for all the jobs.
- Involved in code and Job reviews before delivery.
- Supported system testing and fixed all defects.
- Working on the development of DataStage jobs for loading the load ready data into the target tables.
Environment: DataStage 7.5 (Parallel Extender), TOAD, DB2, Oracle 9i, UNIX, Shell Scripting, PL/SQL, SQL Server2005.