- Over 6+ years of IT experience in analysis, design and development of data warehouse and oracle related applications.
- 5+ years of solid experience in Extraction, Transformation and Loading (ETL) processes using Ascential DataStage 7.5.1/7.5and IBM Infosphere 8.5/8.x.
- Extensive knowledge of Software Development Life Cycle (SDLC), having thorough understanding of various phases like Requirements, Analysis/Design, Development and Deployment.
- Used IBM information analyzer extensively for complete source system profiling and analysis, to validate data values, validate column/table relationships and drill down to exception rows for further analysis.
- Ensureddata projects contain trusted information and lower the risk of propagating bad data by using information analyzer
- Performed Debugging, Troubleshooting and performance tuning of Datastage Jobs.
- Expert in Dimensional Data Modeling, Star Schema modeling, Snowflake Schema modeling, fact and dimension table design, physical and logical Data modeling using ERwin
- Used EPM strategy to review the overall business requirements, and how these requirements have been translated into a project plan and system design.
- Strong skills in fulfilling EDI requirements and experience with XML, CSV, and fixed field length file transformations
- Worked extensively with Data migration, Data Cleansing, ETL Processes.
- Extensive experience in designing and working with Oracle 8i/9i/10g and writing SQL queries and PL/SQL Procedures, Functions, Packages, Database Triggers, Exception Handlers, Ref Cursors, Database Objects using PL/SQL. Proficient in writing Unix Shell scripts.
- Expertise in Testing phase planning, preparation and execution.
- Expertise in creating schema objects, Performance Tuning of SQL statements.
- Able to work within an aggressive and demanding project schedule and environment.
Languages : SQL, PL/SQL, C, Unix-shell scripting, HTML.
Database Tools : Oracle, Sql, PL/SQL, SQL*Loader, DB2, Toad, Oracle Discoverer, Erwin, Oracle Designer, XML Publisher, EDI.
ETL Tools : IBM Information Server 8.5, 8.0.1, IBM Information Analyzer 8.0 DataStage8.5/8.01/7.5.
BI Tools : Business objects 6.x/5.x, Cognos Impromptu 7.1/7.0/6.0, Cognos Power Play 7.1/7.0/6.0, Crystal Reports.
RDBMS Servers : Oracle 7.x/8i/9i/10g/11g, MS Access, MS SQL Server 7.0, Oracle9iAS, Apache Tomcat 4.0.1, IIS.
Operating Systems : UNIX (Solaris 8/2.x, Red Hat Linux 7.x/6.x), Windows NT.
Confidential, RI Jan 10 Present
Senior Datastage Developer
- Involved in Analysis, Requirements gathering, function/technical specification, development, deploying and testing.
- Involved in Design and Data Modeling using Star schema.
- Created snapshot for the transactional tables in distributed databases.
- Utilized Data stage Parallel Extender PX, to optimize performance in this very large database environment.
- Involved in critical multiple instance datastage jobs which will send the outbound files for different Lobs (line of business) at the same time and monitored the jobs accordingly.
- Scheduled the parallel jobs using DataStage Director, which is controlled by DataStage engine and also for monitoring and performance statistics of each stage.
- Created Error Tables containing data with discrepancies to analyze and re-process the data.
- Extensively used Parallel Stages like Join, Merge, Lookup, Filter, Aggregator, Modify, Copy, Sort, Funnel, Change Data Capture, Remove Duplicates, Surrogate key Generator, Row Generator, Column Generator, and Peek for development and de-bugging purposes
- Used DataStage Manager to Import and Export DataStage components and Import table definitions from source databases.
- Used the Folder stage and RTI stage to extract data from .xml type files and load into Oracle database.
- Execute Backend Testing application by writing complex SQL Queries.
- Involved in the design, development and testing of the PL/SQL stored procedures, packages and triggers for the ETL processes.
- Written SQL scripts to create and drop the indexes those are passes as parameters in the pre & post sessions.
- Worked on Enterprise level Scheduling tool-control M to monitor and run Data Stage jobs on nightly basis.
- Developed shell scripts for job scheduling and logging.
- Involved in performance tuning of SQL queries by providing hints, obtaining Explain Plan, Analyzing tables and adding indexes.
- Involved in full integration test and code reviews of all jobs within each sequence before migrating the jobs and sequencers from the Development environment (Dev) to the QA environment, and from the QA environment to the Production environment.
Environment: IBM Information Server 8.5/ 8.0.1, IBM Information Analyzer 8.0, Ascential DataStage (Designer, Director and Administrator), Oracle 10g/11g, DB2, SQL, PL/SQL, SQL* Loader, SAP interface, UNIX Shell Script, TOAD 7.3, ERWIN 4.2, Win XP and AIX UNIX 4.2, Autosys.
Confidential, TN Nov 08 Dec 09
Role: Data stage Developer
- Analyzed various sources required to design, develop the data stage parallel jobs for extracting the data from the tables.
- Worked with the Business Analysts to gather the information required, business analysis and project co-ordination in perspective to the target data extracts.
- Identified and documented data sources and transformation rules required populating target data.
- Coded Data stage parallel jobs to generate the data extracts, which are flat files, which are to be later, processed and loaded into the tables.
- Migrated jobs from datastage v7.5 to datastage 8.5 and tested so as to generate the required results
- Used various stages like DB2 Connector, Join, Lookup, Transformer, Sort, Remove Duplicates, Copy and Sequential File etc.,
- Implemented Loop activity and Last row handling functionalities using Data stage 8.5 Transformer Stage
- Worked on UNIX scripting to automate the jobs and trigger the Master Sequence jobs.
- Fine-tuned the queries by changing the join orders and giving combinations of joins.
- Was involved and implemented in Performance tuning of Datastage jobs with proper DataStage partitioning techniques and environment variables.
- Used parameter sets in parallel jobs and also created multi-instance jobs.
- Developed Job Sequences with restart capability for the designed jobs using Job Activity, Exec Command, Job Sequencers, E-Mail Notification Activities and Triggers.
- Used Clear Case and Clear Quest to check in the Datastage code and Proper change management.
Environment: IBM Information Server 8.1, IBM Information Analyzer 8.0 IBM Data Stage 8.0.1 (Designer, Administrator, Director), Erwin 4.1, Windows XP, Quality Stage , Unix Shell scripting, TOAD 6.3, Oracle 10g, SQL, PL/SQL.
Confidential,CA Jan 08 - Oct 08
- Analyzed the transactional data model, data elements. Interacted with Business analyst, SME (Subject Matter Experts) on day-to-day basis to create technical specification for data conversion programs.
- Involved in the Development, Implementation of the backend systems for the Database.
- Created Data Model using Erwin from data sources and defined the dimensions, levels and measures in the model.
- Developed several Server jobs using DataStage Designer. Performed data transformations by using various stages like Transformer, FTP, conditioning, and validation and loading into the Data warehouse.
- Extracted data using different strategies from the various heterogeneous sources like DB2, Sybase, Oracle & flat files.
- Wrote system and user acceptance test scripts.
- Developed test plans and test cases.
- Designed UML diagrams using MS Visio.
- Designed and developed jobs to migrate data using Ascential DataStage.
- Developed DataStage Server jobs extensively using various stages like Transformer, Hash File, Aggregator, Sequential stage, ORAOCI etc.
- Imported and exported Jobs from DataStage Manager to different environments.
- Used DataStage Director to monitor, validate and execute the Server Jobs created in the DataStage Designer.
- Wrote SQL queries for Oracle to fetch the data.
- Wrote stored procedures, functions, views and triggers.
- Developed UNIX k-shell scripts for archiving files on remote machine.
- Responsible for Unit, System and Integration testing.
- Supported existing production systems.
Environment: Ascential DataStage 7.5, Oracle 9i, Sun Solaris 5.8,Sybase, TOAD, Perl, SQL, PL/SQL, DB2, UNIX Shell Scripts, C++, Seibel, MS Visio, WS FTP Pro.
Confidential, San Diego, CA Oct 07 - Dec 07
- Involved in the requirements gathering, analysis to define business and functional specifications.
- Responsible for Data Modeling, Functional and Technical Design for the Client.
- Involved in designing, developing and documenting of the ETL (Extract, Transformation and Load) strategy to populate the Data Warehouse from various source systems (Oracle) feeds using Data Stage, PL/SQL and Unix Shell scripts.
- Extracted data from various sources like DB2, Oracle, XML and Flat Files and loaded to target tables.
- Used Metabroker for importing Metadata using MetaStage and created new job categories and new data elements.
- Used Datastage Designer to design and develop jobs for extracting, transforming, integrating, and loading data targets.
- Developed Shared Containers, which can be reused several times.
- Automated the FTP Extraction Process by job control to use one job for several extractions.
- Developed Hash files for performance improvement by reducing the reference lookup time.
- Extensively used Datastage Designer stages such as FTP, Folder Stage, ODBC, OCI, Aggregator, Transformer and Hashed File.
- Used Job Control routines and Transform functions in the process of developing the job.
- Job Parameters were extensively used to parameterize the server jobs.
- Used DataStage Director and the runtime engine to schedule running the server jobs, monitoring and debugging its components.
- Developed star schemas and created custom star schema as per the business requirements.
- Applied Performance Tuning logic to optimize session performance.
- Developed PL/SQL stored procedures for source pre load and target pre load to verify the existence of tables.
Environment: Ascential Datastage 6.5.x (Designer, Administrator, Director and Manager), DB2 UDB, Oracle, PL/SQL, Unix Shell scripts, TOAD, SQL*Loader, UNIX AIX and Metastage.
Confidential, India Aug 07 Nov 07
- Performed database design and development.
- Involved in analysis, design and development of the system.
- Developed customized Forms like customer and supplier maintenance, bill, receipt, letter of credit, invoice, debit and credit notes.
- Developed Reports and PL/SQL libraries to monitor revenue earnings and expenses and to maintain the general ledger of the organization.
- Developed stored procedures and functions using PL/SQL for complex updates and audit trails.
- Wrote database triggers for audit and data validation.
- Wrote stored procedures & functions using PL/SQL.
- Designed the Entity-Relationship model and normalized database.
- Conducted re-sizing of data files, managed table spaces and data files.
Environment: Oracle, PL/SQL, UNIX, shell scripts, Windows NT.