Sr SAS Developer & Analyst

RAMKIRAN DANDE

Phone: 201-***-****

E-Mail: ********.***@*****.***

SUMMARY:

• Over 8 years of experience in Design, Development, Implementation and support Of RDBMS in Development, Test and Production environments using Oracle Tools.

• Extensive experience in Project Life Cycle including requirement gathering and Analysis, Database design, Database development, Application development, Test and “Red hat” Support.

• Extensive experience in Oracle 10g/9i/8i and hands on experience writing Oracle SQL queries and Oracle PL/SQL Stored Procedures, Functions, Packages, Database Triggers, Exception Handlers, Cursors and using latest features to optimize performance (Bulk Collects, Collections, Ref Cursors, Materialized views, Global Temporary Tables).

• Experienced in working with large tables containing an average of 1 billion rows.

• Proficient in writing and tuning complex SQL statements, complex joins co-related sub-queries and SQL statements with Analytical functions (Rank, Lead, Lag etc).

• Experience in tuning application using tools like Explain Plan, SQL*Trace and TKPROF for high efficiency.

• Extensive experience in using Oracle seeded packages like DBMS_SCHEDULER, UTL_FILE, UTL_SMTP, UTL_MAIL, UTL_TCP and DBMS_SQL, DBMS_STATS.

• Experience in writing dynamic SQL statements using EXECUTE IMMEDIATE and DBMS_SQL.

• Experience in defining Value Sets and Lookup types and registering PL/SQL packages as Concurrent Programs.

• Experience in using Oracle concepts Like Table Partitioning, Optimizer hints and Materialized Views (Snapshots).

• Good experience in asset pricing and data scrub/rescrub.

• Experience in working large Data warehouse environments.

• Good knowledge of Data integration and migration.

• Knowledge of UNIX SHELL Scripting and scheduling cron jobs.

• Good working experience in SQL Server Reporting Services (SSRS).

• Experience in using Oracle DBA utilities like Export, Import and Data pump.

• Experienced in developing batch programs by using PL/SQL and shell scripts and in using SQL*Loader for loading data from flat files into Oracle tables.

• Good capability of performance tuning, normalization and partitioning of data and indexing of partitioned tables.

• Expertise in testing phase planning, preparation and execution.

• Extensively worked with third party database tools like TOAD, SQL Navigator and SQL*PLUS.

• Good understanding of Relational database design and involved in developing various data models using ERWIN, ER/STUDIO and VISIO.

• Strong programming, debugging and problem solving skills and ability to translate customer requirements into technical solutions by utilizing business insights.

• Experienced in interacting with Business users, Business Analysts, IT leads and developers in analyzing business requirements and translating requirements into functional and technical design specifications.

• Ability to work within an aggressive and demanding project schedule and environment.

• Excellent Interpersonal and Communication skills, coupled with strong technical and problem solving capabilities.

Languages PL/SQL, SQL, C, C++, Pro*C, JAVA

RDBMS Oracle 10g/9i/8i ,MS Access,Essbase

Modeling Tools Designer 2000 (6i), ERWIN,ER/STUDIO, VISIO

Front end tools VB .NET, Power builder, Developer 2000(Forms and reports)

Tools/Utilities SQL Loader, Export, Import, Data pump, TKPROF, TOAD, Oracle Warehouse builder(OWB),SQL SERVER REPORTING , INFORMATICA,SERVICES(SSRS),SQL Navigator,PL/SQL developer,OEM (ORACLE ENTERPRISE MANAGER), ORACLE RAC,ENDUR

Operating Systems Windows XP/2000/ 2003, Unix (Red hat, Sun Solaris, HP, AIX)

Web Development Tools HTML, XML, Java, JavaScript, ASP.net, ORACLE Application 9ias

Version Control Rational Clear Case, CVS,SYNERGY

Issue Tracking Rational Clear Quest,CMIS

Misc Tools Kintana, Connect Direct, CMIS

TECHNICAL SKILLS:

EDUCATION:

• Bachelor of Technology, Madras University, India.

Professional Experience:

VERIZON TELECOM JUN 2009-PRESENT

Irving, TX

Sr. Oracle Developer, Consultant

Description:

The Arbor to RiBS project is being implemented for migrating existing VOL Arbor Billing active accounts and services into the RiBS systems. The objective of this project is to simplify Verizon retail billing and improve bundle billing by migrating all HSI, FiOS, Dial-Up, VASIP, Managed Messaging, HOA accounts to RiBS. The Conversion Engine role is to monitor end to end conversion & migration processes and to report real-time Conversion Metrics. The Metrics provided would be detail statistics of all conversion runs with detail to backup the numbers being reported. There would be web services available for systems to access conversion status among systems for their respective reporting.

Responsibilities:

• Involved in gathering business requirements, and designed the process flow for the tool.

• Prepared the Technical Design Document based on the Business Requirement for various modules.

• Involved in identifying the file formats, and structures from various tools for processing.

• Involved in design of the Database table structures related to the various file formats.

• Used SQL*Loader, UTL_FILE package and External Table concepts for loading data into the Database tables through an automated process.

• Analysis, design and development of Database procedures, functions and packages using PL/SQL as per the business process.

• Used External Tables to load test data from CSV files to the database tables from Front end.

• Involved in Optimizing SQL queries involving large tables.

• Involved in creating the Metrics Website which allows Business Users to look at the Conversion Progress through GUI which takes data from the back end tables & displays on the screen as & when a particular process is completed.

• Developed Various Shell Scripts for automation & validation.

• Created Unix Shell Scripts for archiving tables for a particular run & automated scripts to RUN STATS(ANALYZE) on various tables & create reports on the same.

• Developed Reports which pull the core Network Parameters like the DCR, Handoff Failure and the Blocked calls from the test data.

• Developed a generic tool to Use UTL_FILE Package to create processed data file for the clients.

• Used Database triggers for maintaining audits.

• Created Indexes and Materialized Views to improve the overall system performance.

• Tested the application to ensure proper functionality, data accuracy, and modifications had no adverse impact on integrated system environment.

• Involved in the maintenance and support of existing applications with the users.

• Involved in training and production support at various client locations.

Environment: Oracle 10.2.0.1/10.2.0.4//9.2.0.4/9.2.0.7, PL/SQL, SQL*Plus, Toad, ETL, Informatica, Oracle Forms, Reports, HTML, XML, RAC, SSRS, ENDUR, Red hat 2.1/3.0/4.1/4.3, Java.

AT&T OCT 2007-APR 2009

Alpharetta, GA.

Sr. Oracle Database Developer, Consultant

About AT&T: AT&T Mobility is the largest wireless company in the United States, with more than 61 million subscribers who use the nation's largest digital voice and data network.

Description:

Project Mercury is a management reporting initiative focused on providing accurate financial and operational information in a robust, easy to use reporting environment. This project is organized into five core modules - Subscribers, P&L, Billed ARPU, ASPEN, and Portal/Foundation. As an Oracle developer, I was responsible for developing different tools for these modules.

Responsibilities:

• Involved in SDLC - Analyzed the existing partially developed tool. Provided input to the requirements analysis team and supported ongoing release activities.

• Interacted with business users for gathering business requirements for some of the tools (E.g. Adjustment tool).

• Prepared the technical design documents (TDD) for most of the core modules of Project Mercury.

• Debugged the existing code, and listed out the changes needed for the tool to run efficiently, and also provided suggestions for future enhancements.

• Developed a generic process for bulk upload through the Frontend.

• Developed a full-fledged approval workflow suite customized for Revenue and Tax departments.

• Developed a Dynamic SQL tool where the end users can specify the various business rules to be used in the creation of a JE for ASPEN or Driver for P&L.

• Involved in Optimizing SQL queries involving large tables (1 billion rows).

• Used DBMS_PROFILER package for identifying Performance bottlenecks in PL/SQL packages and also for identifying the package behaviors.

• Developed a Scheduler tool which helps in scheduling jobs, and defining dependencies.

• Involved in writing SQL Scripts for creating drivers which enable AT&T to look at various activities of company at individual customer CTN level.

• Developed a tool for P&L which does Adjustments at the CTN (Customer Telephone Number) level.

• Developed an Automated Journal creation tool which takes data from source tables and creates journal entries at the CTN level for the various modules (like PRM, PREPAID, REVSHARE, ROAMER etc) based on user specified business rules.

• Used Global Temporary tables in the process giving ability to PROD support for running journals concurrently across multiple sessions.

• Used Ref-cursors, Collections and Bulk Collects for memory intensive processes and to improve traceability.

• Created a Manual Journal Entry tool which allows users to create journals, and does real time validation (Using Oracle API FND_FLEX_EXT) and submission to Oracle 11i GL.

• Created materialized views on some of the oracle 11i objects (Like GL_CODE_COMBINATIONS and FND_FLEX_VALUES_VL) to improve the performance on Manual Je validation process.

• Developed a process that posts Automated Journal entries to Oracle 11i GL interface, and monitors the status at all levels by creating a View on Oracle 11i GL Tables (GL_JE_HEADERS,GL_JE_BATCHES,GL_JE_LINES,GL_JE_SOURCES,GL_JE_CATEGORIES)

• Registered PL/SQL packages as concurrent programs which allowed users to execute them on-demand.

• Developed a generic email process using UTL_TCP, which can be used by any of the tools which need to send email alerts.

• Involved in data scrub/rescrub and asset pricing.

• Used Autonomous transactions in triggers for supporting Audit and also used in packages for logging the process information.

• Adept at creating migrations scripts for code migration.

• Created a package to gather table and partition level statistics.

• Created a package which allows PROD support to create partitions on their own without reaching DBA’s.

• Involved in “Redhat” Support and Tier 3 Support for existing production applications.

• Involved in creation of SIS plans for module deployments.

Environment: Oracle 10.2.0.1/10.2.0.4//9.2.0.4/9.2.0.7,Essbase, Endur, SSRS,PL/SQL, ETL, Informatica, SQL*Plus, Oracle reports, RAC, Toad, HTML, XML, Red hat 2.1/3.0/4.1/4.3

Nationwide Insurance, Columbus, OH APR 2006- SEP 2007

Oracle Developer, Consultant

Description:

Nationwide Insurance is one of the largest insurance and financial services company. As a PL/SQL Developer, I was responsible for the back-end development of loading data from different legacy systems into Oracle system.

Responsibilities:

• Analyzed the business requirements of the project by studying the Business requirement Specification document.

• Based on the requirement specifications designed the database model for OLTP applications.

• Prepared program specifications for PL/SQL (Procedures, Functions and Packages) for daily policies and for the development of the application.

• Involved in database development by creating Oracle PL/SQL Functions, Procedures, Triggers, Packages, Exception Handling, Records and Collections.

• Creation of database objects like tables, views, Materialized views, sequences,

Synonyms, database links and indexes.

• Fine Tuned procedures for the maximum efficiency in various schemas across the database using Oracle Hints, Explain plan and Trace sessions for Cost based optimization.

• Generated and maintained Keys, Constraints and Relationships between the tables for

OLTP instances.

• Optimized critical queries to eliminate Full Table scans reduce Disk I/O and Sorts.

• Wrote shell scripts for loading data from Flat Files to database tables using SQL*Loader.

• Tuned the SQL using Explain Plan, TKProf and Stats pack for better performance.

• Transferred the data between databases using Export, Import and Datapump utilities.

• Wrote UNIX Shell Scripts for automating the daily/weekly/monthly data process.

• Wrote shell scripts to transfer files using FTP to all the UNIX servers at one time for daily data processing.

• Prepared Technical Documentation. Trained the business users for the better understanding of the Business Application.

Environment: Oracle 10.2.0.1/10.2.0.4//9.2.0.4/9.2.0.7,Essbase,ETL, PL/SQL, SQL*Plus,RAC, Toad, HTML, XML, Red hat 2.1/3.0/4.1/4.3

Pillar Data Systems, San Jose, CA MAR 2005-FEB 2006

Oracle Developer, Consultant

Description:

The main functionality of Global e-marketing project was to do the performance analysis of the marketing campaigns launched by Pillar Data all over the world. The company’s data comes from different operational source like Asian, USA, European countries and stored in a staging database and then data is transformed and loaded into a centralized data warehouse for various strategic business reports. The main focus was to keep track of the money invested and keep a check of the flow of money by monitoring the quality of marketing in the project.

Responsibilities:

• Understanding the business requirements, analysis, design, development and implementation into the system.

• Designed and developed functions/ stored procedures/packages/triggers using PL/SQL.

• System analysis, Designing & Developing, Analyzing Data & Modifying Tables, enforcing constraint on tables.

• Designing Database & enforcing various constraints.

• Extensively used the advanced features of PL/SQL like collections, nested table, v-array & Dynamic SQL.

• Involved in performance tuning of SQL queries.

• Wrote Unix Shell Scripts for loading the flat file data (data from external source) into oracle table.

• Coded and implemented PL/SQL packages to perform Application Security and batch job scheduling.

• Documented business rules, functional and technical designs, test cases, and user guides.

• Analyzed the tables for better performance using Explain plan and Tkprof.

• Good analytical skills and extreme sensitivity for defects, faults and bugs in software systems. Good problem solving and trouble shooting skills. Good skills with performance improvement for preexisting applications.

Environment: Oracle 10.2.0.1/9.2.0.4/9.2./6/9.2.0.7, Toad 8.6, SQL * Loader, Windows 2000/XP, Sun Solaris 5.8/5.10.

North Star Telecommunications, Birmingham, AL JUL 2003– DEC 2004

Database Developer, Consultant

Description:

The Historical Database System stores detailed data for certain billing and financial systems scheduled for discontinuance. The company requires maintaining certain types of data for period of up to five years. The Historical Data Repository is to provide the ability to maintain and extract data, and provide some user specified views. The user has capability to generate an invoice in the appropriate format as well as have access to the data for various reporting needs.

Responsibilities:

• Writing complex stored procedures/triggers and functions using PL/SQL and optimized for maximum performance.

• Involved in the database design and re-structuring of the database.

• Developed complex procedures to handle errors and exceptions at database level using PL/SQL.

• Optimized the performance using PL/SQL by restructuring the extraction and summarization routines.

• Automating data loading, extraction, report generation using UNIX shell scripting.

• Coordinated with the Quality Assurance/Testing team members to perform system, integration and unit testing.

• Worked with Business Analyst and users to clarify requirements and translate the

requirement into technical specifications.

• Created dimensions/fact tables in data models using Erwin. Forward engineer objects and Complete compare data models to keep objects in synch with the database.

• Coordinated with the DBA for migrations of new/updated objects.

• Use Repository Manager to import metadata for sources and targets and the mapping designer to map source to the target.

• Used SQL and PL/SQL stored procedures, functions to implement business logics in Oracle.

Environment:Oracle8.1.7/9.2.0.1/9.2.04, SQLNavigator, Sun Solaris (5.7/5.8), Windows 2000/xp.

Global Marketing Inc, India JUL 2002-APR 2003

Oracle Developer

Description:

The System is used to keep track of the orders and information of the accounts generated. The orders are entered by the Marketing department and are authorized by the Accounting department. The order flows to the Production department on approval from Accounting. Progress of the orders is entered by the Sales & Marketing department and the status of the orders is maintained in the system, on completion, order flows back to Accounting for invoicing.

Responsibilities:

• Creation of stored procedures and packages in PL/SQL.

• Optimization of SQL queries, and performance tuning by using Explain plan and Analysis.

• Wrote and tuned queries for better performance.

• Involved in Technical Documentation of user interfaces.

• Designed fact and dimension tables using Erwin.

• Performed Unit and System Testing.

• Involved in fixing the bugs reported back after initial stages of implementation.

Environment: Oracle 8.1.5/8.1.6,AIX, Toad, Windows 2000.

Bangalore Hospital, India JUL 2001-MAY 2002

Hospital Management System

Software Programmer

Description:

This project was developed to automate and maintain the hospital. The various modules

involved in this project were Patient details, Doctor Details, Scheduling of operations, Appointments, Medical History, Billing, and Availability of rooms and Reports module.

Responsibilities:

• Created procedures and functions using PL/SQL.

• Create simple, complex create joins, calculated items and conditions.

• Analyzed the tables and indexes using SQL and PL/SQL scripts.

• Involved in unit and system testing.

• Monitoring existing code and doing performance tuning if necessary.

• Wrote database triggers for audit and data validation.

Environment: Oracle 8i, TOAD, Windows, SQL*Plus, PL/SQL

Contact this candidate