SUMMARY

• Over 9 years of experience with design, development and implementation of Data warehouse and Oracle based Applications.
• Expertise working with Oracle 11g, 10g, 9i, 8i, PL/SQL, Forms 9i, Application Design, Recovery Planning, Security Management.
• Oracle certified professional in Advanced PL/SQL programming.
• Experience in ETL methodologies for supporting data extraction, transformations and loading processing using IBM Data Stage, IBM Websphere Information Server.
• Data Processing experience in designing, developing and implementing jobs Data Mart applications, mainly transformation processes using ETL tool Data Stage Designer, Data Stage Manager, and Data Stage Director on Server Engine.
• Proficient in designing IBM Data Stage 8.7/8.1 Server and Parallel Jobs.
• Expertise in Performance Tuning, Query Optimization, Client/Server Connectivity, and Database Consistency Checks.
• Experienced and involved in Administration, Database Design, Performance Analysis, and Production Support for Large and Complex Databases.
• Experience in working with applications modules to import data from various sources using PL/SQL, Shell Scripts and SQL Loader.
• Experience in Data Modeling including Analysis of requirements and documenting, Designing of the Dataflow, developing ER Diagrams Erwin.
• Strong SQL, PL/SQL skills including stored procedures, Indexed views, User Defined Functions, Cursors, Triggers, and Distributed Queries.
• Experience in implementation of Data Warehousing, Data Marts, Star and Snow Flake Schema Modeling, Dimensional Modeling on OLTP and OLAP concepts using Ralph Kimball Methodologies.
• Performed backups, updated index statistics, monitored database and disk space. Involved in security issues and scheduled tasks.
• Expert in unit testing, system integration testing, implementation, maintenance and performance tuning.
• Knowledge in incorporation of various data sources such as Oracle, MS SQL Server, and DB2, Teradata, Sybase, XML and Flat files into the staging area.
• Strong knowledge of On-Line Analytical Processing OLAP , On-Line Transaction Processing OLTP , and Software Development Life Cycle SDLC concepts.
• Worked with Unix shell scripts and performance tuning and automating the process for using Unix k-shell
• Experienced in working with business users, business analysts, IT leads and developers in analyzing business requirements and translating requirements into functional and technical design specifications.
• Flexible, enthusiastic and project oriented team player with excellent written, verbal communication and leadership skills to develop creative solutions for challenging client needs.

SKILLS/TOOLS

WORK EXPERIENCE

Confidential

Oracle Analyst

Responsibilities:

• Extensively written Shell Scripts, PL/SQL Packages, Procedures, and Functions.
• Importing data from different sources like flat file, excel file and Oracle dump file into Oracle database source schema tables using SQL loader.
• Developed Server jobs to implement Slowly Changing Dimension SCD Type I for loading data from source to ODS and SCD II logic to load data from ODS to Data warehouse.
• Used Transformer, Oracle, Aggregator, Sort, Hashed File, Sequential File stages to develop Server jobs.
• Designed and developed ETL pattern for Incremental loading using DataStage designer to load data from Oracle, Flat File to staging database and from staging to the target Data Warehouse database.
• Populating the database, exporting and importing data, and creation of Packages, Triggers, and Functions and Stored Procedures for implementing business logic and database intensive logic.
• Managing tables, indexes, constraints, types, views, synonyms and sequences.
• Creating SQL Loader Control Files for moving data from Flat Files Fixed Record Length to Staging and working Tables.
• Extensively involved in writing PL/SQL stored procedures, functions and database triggers.
• Used Ref Cursors, Native Dynamic SQL and Bulk Collections to implement the Bulk Load.
• Writing complex SQL queries and PL/SQL procedures to extract data from source tables of data warehouse.
• Tuning the application for loading and extracts at the database level and at the query level to investigate the time for performance to specific user groups.
• Developed Packaged Stored Procedures for Testing the Transferred Bulk Load Data Using Bulk Collections.
• Expertise in UNIX Shell scripting for scheduling reports and setting up batch reports to make data synchronize.
• Developed views, functions, procedures and packages using PL/SQL and SQL to transform data between source staging area to target staging area. Extensively used PL/SQL tables, cursors and exception handling.
• Creating Korn Shell Scripts which act as a wrapper for various tasks like - for executing the SQL Loader command lines, for refreshing the materialized views and for updating the Fact and the dimension tables with the data from the Staging Area Tables.
• Involved in business process automation and generated adhoc-reports for the business as per the requirements.
• Performed administrative activities like creating user logins with appropriate roles, granting - denying login privileges, monitoring the user accounts, creating groups, granting database and application roles to users and groups.
• Performed Full backups and Transaction log backups. Restored them whenever necessary. Created backup strategies and worked on scheduling the backups.
• Partitioning the Fact Tables and aggregate materialized views for enhanced Performance.
• Involved in code reviews, designing technical specs, Unit testing, Query optimization and SQL tuning.

Environment: Oracle 11g/10g/9.2, IBM DataStage EE 8.1/8.7 Designer, Director, Manager, and Administrator , Autosys, IBM AIX, UNIX Shell Scripting, PL/SQL, Toad 11.1, SQL Navigator.

Confidential

DataStage and Oracle Sr. Programmer Analyst

Responsibilities:

• Interacted with End user community to understand the business requirements and in identifying data sources.
• Analyzed the existing informational sources and methods to identify problem areas and make recommendations for improvement. This required a detailed understanding of the data sources and researching possible solutions.
• Used Datastage to extract client data from Oracle, MS Access and mapped the data into a target Business Warehouse
• Created various Functions in Data Stage for mappings and other statistical calculations.
• Extensively used Pivot stage to pivot the source data to achieve required table structures like converting data from columns into rows and rows into columns
• Developed database objects PL/SQL Packages, Procedures, and Functions.
• Used shell scripts and PERL scripts for automating the processes.
• Optimized existing code for high performance using bind variables, bulk collects
• Responsible for checking code performance when applications are making huge amount of transactions, like creating, importing, exporting schemas from different Oracle instances
• Worked on day-to-day related production issues, importing/loading huge amount of data, user related, performance tuning.
• Worked on designing the target database in the data warehouse environment.
• Worked on ETL development in Scheduling the workflow of the sessions and creating user sessions.
• Helping application developers in data modeling and assist in the physical logical design of the database.

Environment: Oracle 9i, IBM Information Server DataStage 7.5, SQL, PL/SQL, UNIX, Perl Scripts, XML, Windows XP

Confidential

Oracle Programmer Analyst

Responsibilities:

• Understanding the customer requirements, analysis, design, development and implementation into the system.
• Developed Oracle application using Designer 9i toolset.
• Designed and developed PL/SQL functions/ stored procedures/cursors/packages/triggers
• Involved in performance fine-tuning of the queries/report using PL/SQL and SQL Plus
• Involved in writing complex queries to generate reports as per client request as a part of production support.
• Extensively used the advanced features of PL/SQL - Subtypes, Records, Tables, Object types and Dynamic SQL.
• Developed Unix Shell Scripts to automate backend jobs, loading data into the Database using SQL Loader.
• Designed and developed GUI front-end using Oracle Forms for user interaction with the database.
• Worked with modifying the existing application and adding lots of reporting capability using Power builder reporting tool.
• Designed and developed various reports using Oracle Reports.
• Modeling the business requirements, Diagrammatic representation of business processes, Define and represent entities, functions and flows between the entities, using the transformers to generate initial designs.
• Migrated data from Text file to Oracle database.
• Worked with Oracle Enterprise Manager and Toad.
• Involved in the design and development of Forms and Reports using Developer 9i.
• Support in the area of technical Writing/Editing by reviewing the grammar, writing styles and syntax of quality materials and technical report.

Environment: Informatica Power Center 7.1.3/8.0, DB2, Oracle 9i, SQL, PL/SQL, TOAD, Erwin, XML, T-SQL, Shell Scripts, SQL Navigator

Confidential

Database Developer

Responsibilities:

• Interacted with users to collect and analyze the specifications and designed documents, includes functional and technical specifications.
• Responsible for designing Schema and finalization of naming and coding standards throughout project life cycle.
• Responsible for making the strategy for backup and recovery procedures. Planned and Implemented backup and recovery of the Oracle 8.x Database. Capacity planning and creating and maintaining the Databases.
• Created Oracle instance, table space and UTL settings. Developed, Tested and Migrated the Oracle PL/SQL scripts to Production. Developed, Tested and Migrated Unix Shell Scripts.
• Coded well-tuned PL/SQL procedures at server side. Performed loading of data to the Oracle tables using SQL Loader Scripts.
• Responsible for creating and maintaining the Table spaces, Users and assigning Roles and Privileges
• Data modeling Java with HTML.
• Developed Date entry, query and reports request screens.
• Extensively used PL/SQL to implement cursors, triggers packages.
• Developed procedure and functions using PL/SQL and Developed critical reports.
• Interact with end users to gathering requirements.
• Used Oracle Application Express APEX for Spreadsheet upload, reporting and PDF printing.
• Responsible for performance tuning of the database.
• Monitoring load on the server and memory usage by different processes.
• Automated system administration using Perl.
• Create record groups for manipulation of data. Perform unit and system integrated testing.
• Co-Ordination with users and SQL tuning on existing reports.
• Moved data from one database to other database, Used SQL Loader for data migration.

Environment: Oracle 8i, PL/SQL, Unix Shell Scripts, Perl, Apex 2.2, PL/SQL procedures SQL Loader, Excel, Windows and UNIX, Windows 2000

Confidential

Database Developer

Responsibilities:

• Involved in cleansing and extraction of data and defined quality process for the warehouse
• Performed tuning the application includes Packages, Procedures, Functions, Triggers and Database Objects
• Created Database Objects indexes to boost the performance of the Application.
• Extensively involved in coding of Triggers, Packages in the Application Level to process transaction.
• Created Reports using Master-Detail, Matrix, Group Above etc.
• Designed forms using oracle forms with Procedures, Record Groups, LOV, Alerts, Parameters, Global Variables, Multiple Canvases, Multiple Windows, Multiple Blocks, Control Block and triggers at various levels.
• Coded database procedure to calculate average purchase price of material for current financial year for costing purpose.
• Created and executed unit and system cases and documented.

Environment: Oracle 8.1/7.x, SQL, PL/SQL, Developer 2000, Forms 6i, Reports 6i, TOAD, SQL Plus, SQL Loader, Windows NT, UNIX, Perl Scripts, XML, Windows XP