Manager Data

KRUNAL GALA

Informatica/PL-SQL Developer/Designer/Analyst

Cell: 508-***-**** email: ******.****@*****.***

Work Experience Summary

4+ years of IT experience in various phases of SDLC such as analysis, design, coding and

testing and development in various Data warehousing environments. Extensive experience

in the implementation of data base management systems for Insurance, Financial and

Manufacturing clients.

Extensive IT experience in Client-Server architecture, Business Intelligence/Data

Warehousing applications design/development maintenance, production support, programming

and software development in a heterogeneous environment. Good Exposure in identifying Business

requirements, Technical requirements, Database Design, Troubleshooting.

Participated in Joint Application Design/Development (JAD) sessions and involved in

discussions with clients/business users to determine the dimensions, hierarchies and

levels, basic measures, derived measures.

Extensive experience in Oracle Development in table design, writing stored procedure, Database

Triggers, Functions and PL/SQL packages. Strong knowledge in Oracle Cursor management

Exception Handling and ETL scripting using PL/SQL.

Around 3+ years of strong experience in installation, configuration and administration of

Informatica Power Center Designer, Workflow Manager, Repository Manager and

Workflow Monitor for designing/developing complex mappings, from varied transformation logic

like Unconnected and Connected lookups, Router, Filter, Expression, Aggregator, Joiner,

Update Strategy and more. Proficient in using Workflow Manager Tools such as Task

Developer, Workflow Designer and Worklet Designer.

Around 2 years of experience in Oracle Business Analytics using Oracle Discoverer for ad-

hoc querying, reporting, data analysis.

Extensive experience in Materialized View, Ref Cursor, Function based Indexes.

Extensive experience in performance Tuning SQL and PL/SQL code by using Explain Plan

Table, Traces. Have excellent exposure of working with VLDB with 2-5 TB of data. Also

have a good exposure on Informatica Performance tuning. Tuned SQL queries and

performed refinement of the database design leading to significant improvement in

system response time and efficiency.

Used data modeling tools like Erwin, MS Visio and ER diagrams, Normalization Techniques for

designing database with Relational technology (RDMS) on N-Tier Architecture under Windows

2000/NT/98 and UNIX. Extensive Experience in Relational and Dimensional Data modeling for

creating Logical and Physical Design of Database and ER Diagrams with all related entities and

relationship with each entity based on the rules provided by the business manager using ERWIN.

Proficient in Linux and Unix (SUN Solaris 2x) /Perl shell Scripting

3+ years of experience in scheduling tools like Unix Cron jobs and Appworx.

Have 2+ years of experience as working on call for various Production Support projects

and have a proficient exposure in Job Monitoring and Process Improvement activities.

Have good Experience on the Audit and Control mechanisms at the data entry and data

storage levels.

Have good exposure on Data Cleansing using various Data Cleansing tools.

Experience on BO for creating reports for specific business requirements.

Good team player with excellent work ethics, self-motivated and quick leaner.

Excellent communication and interpersonal skills.

TECHNICAL SKILLS

ERP Oracle Applications (11i, 11.0.3) Human Resource Management System

RDBMS ORACLE 8i/9i/10g, MS-Access, SQL-Server 2000/2005, Post Gre Sql

LANGUAGES PL/SQL, SQL, Linux and Unix (Sun Solaris 2x) Shell Scripts

ETL TOOLS INFORMATICA PowerCenter 7.x/8.1.5, Oracle BI tools, DataMirror CDC

FRONT-END TOOL Oracle Forms 6i/9i/10g, Visual Basic (VB-6.0), Developer 2000, Magic 7.1G

REPORTING TOOL Business Objects 5.1/6.5/XI, Brio Reporting tool

ORACLE BUSINESS ANALYTICS Oracle Discoverer 9i/10G

DATA MODELING Erwin 7/6/4, MS VISIO

SCHEDULING Unix Cron Jobs, Appworx

VERSION CONTROL PVCS, VSS

OPERATING SYSTEMS Windows, UNIX Sun Solaris 2.x, Linux

OTHER Hyperion Essbase Cubes, MS-Office, MS- Excel, Ms- Word, Power

Point, Toad, Group1 Address Cleaning Software, Unica Campaign

Generation tool.

EDUCATIONAL QUALIFICATIONS

B.E. Bachelor of Engineering (Computers), University of Mumbai, India

Professional Experience:

Client: EMC Westborough, MA April 2008 – Current

Role: ETL Developer/Designer/Analyst

Project: Business Analysis and Dashboard Portal Reporting

EMC is into designing and development of various storage products used to support, store and

preserve data in the data centers. EMC has primarily three kinds of Storage products – Symetrix high

range, Clarion mid range, Celerra low range.

The scope of the project includes Business Analysis and Financial reporting for EMC’s Celerra

Business Line and dashboard reporting by creating portals to automate the financial reports created.

The scope also includes creating pivot charts and pivot reports to help analyzing the raw data

graphically. The pivot reports created helped the Financial group to take business critical decisions. The

dashboard reporting was done by creating the same reports in a portal format. The portal development

helped the automation of the reports and provided an online view of the statistical data which can be

refreshed at runtime to provide better visibility.

Responsibilities:

Business Analysis which included creating pivot reports and charts to covert the raw data into a

graphical view.

Involved in all phases of SDLC from requirement, design, develop and testing.

Extensively involved in requirement analysis from the users and design document.

Designed the relational data model for operational data store and staging areas, Designed

Dimension & Fact tables for data marts

Extensively used ERwin to design Logical/Physical Data Models, relational database design.

Extensively used PL/SQL programming in backend and front end Functions, Procedures

and Packages to implement business logics/rules.

Involved in all phases of Data quality assurance.

Created Schemas, Databases, Tables, Views and Indexes

Worked on Informatica Power Center Source Analyzer, Data Warehousing designer,

Mapping Designer, Workflow Monitor, Workflow Manager and Repository Manager.

Involved in Normalization of data to remove the redundant data

Generated the Exception error list, reports, and correction list.

Developed PL/SQL stored procedures and functions to handle various activities outside of

Informatica ETL process.

Used Oracle Discoverer 9i (Oracle Business Analytics tool ) to fetch data and generate

financial reports from an Oracle Source

Used the BRIO reporting tool to extract data from Hyperion Essbase Cube and create

BRIO reports.

The UNIX jobs were scheduled through cron jobs and had active involvement in job scheduling.

Involved in query performance tuning using Explain Plan, Hints and optimizing the query.

Gained around 70% performance improvement in some of the major critical Interfaces.

Participated in Unit testing, Integration testing, User Acceptance testing. Created Unit test plans

and performed the unit testing on the mappings developed.

Participated in the any enhancement, and Change Request due to production issue or any added

bussines functions for the Interfaces.

Environment: Oracle 9i/10g, Informatica Power Center 8.1.5, Oracle Discoverer 9i (Oracle

Business Analytics tool), BRIO reports for Hyperion Essbase cubes, Erwin 7, PL/SQL, SQL,

SQL, UNIX, Windows XP, Jboss Reporting, Post Gre Sql.

Client: Genworth Financials Richmond, VA Sep 2006 – Apr 2008

Role: ETL/PL-SQL Developer/Designer

Project: Mep/ Cloas Ods data Warehouse

GENWORTH provides Financial Security Solutions to consumers by delivering high quality,

competitive investment and insurance products designed to help individuals build, protect and transfer

wealth. GENWORTH uses a variety of business applications at Richmond to administer and service the

investment and insurance policies.

The scope of the project includes enhancement, maintenance and support of the various data

warehousing applications like MEP, Cloas Ods, Calypso Ods and Experience. The project includes

development of new Ods through various business intelligence tools like pl/sql unix scripts, powercenter

informatica and Data Mirror CDC replication. The scope also includes revisiting old processes, do the

performance tuning and improve their performance. Some of the highlights include the migration of old

applications from older environments and databases to newer environments. These applications are

based on the LTC business of the Non life Insurance domain. To build the applications like new ODS we

have implemented the policy and claims knowledge bases from the LTC world. These data warehouses

capture the online transactions from the source system, store the data and process some business

login and validations to meet the reporting needs. Mep and Cloas Ods process the policy agent and

client information for the policy sources where as Calypso Ods maintains the claim information. The

datamirror is yet another tool used to implant CDC between the sql server source and oracle target.

Responsibilities:

Involved in all phases of SDLC from requirement, design, development, testing, pilot, training and

rollout to the field user and support for production environment.

Extensively used ERwin to design Logical/Physical Data Models, relational database

design, forward/reverse engineering, publishing data model to acrobat files, created

ERwin reports in HTML, RTF format depending upon the requirement, Published Data

model in model mart, created naming convention files, co-coordinated with DBAs to apply

the data model changes.

Design of data warehouses/ Operational Data stores using Pl/Sql unix scripting,

Informatica and datamirror.

Involved in database development by creating PL/SQL functions, procedures, triggers,

packages using Oracle

Migration of all old applications from old environment to new environments like from

Oracle 8i to Oracle 10G

Worked on Informatica Power Center tool Source Analyzer, Data Warehousing designer,

Mapping Designer, Workflow Monitor, Workflow Manager and Repository Manager.

Worked extensively on different types of transformations like Source qualifier, Expression,

Filter, Aggregator, Rank, Lookup, Stored procedure, Sequence generator and Joiner.

Incremental Aggregate used for updating target tables, which needed daily incremental

aggregate updates and different Mappings according to Requirements.

Extensively used Oracle Discover 9i (Oracle Business Analytics tool) for ad-hoc querying,

reporting, data analysis and report publishing in HTML, PDF formats.

Created and scheduled Worklets, Configured email notifications. Set up Workflow to schedule the

loads at required frequency using Power Center Workflow Manager, Generated completion

messages and status reports using Workflow Manager. Wrote the shell scripts for automating the

job.

Used inner joins, outer join, union, union all and sub queries for filtering the Data

Created Materialized views to analysis complex queries

Used Refcursor, implicit / explicit cursor in PL/SQL modules extensively

Performance improvement of old processes by implementing performance tuning techniques.

Used Exception Handling extensively for the ease of debugging and displaying the error messages

in the application

The design document was finalized through conduct of design walkthroughs, and independent

review by the user. Prepared Technical Specifications for all the utilities, as per the

company's standards.

The unix jobs were scheduled through cron jobs and had active involvement in job scheduling.

Prepared Technical documents for all the modules developed by our team

Actively participated in the meeting with management and the team member.

Environment: Oracle 8i/9i/10g, Informatica PowerCenter 7/8.1.5, Oracle Discoverer 9i

(Oracle Business Analytics tool), OWB 9i, Erwin 4, PL/SQL, SQL, SQL*PLUS, SQL*LOADER,

Datamirror CDC replication, UNIX shell scripting, Linux shell scripting, Group1-Code 1 Plus,

Toad, Windows 2000

Client: Genworth Financials Richmond, VA Oct 2005 – Aug 2006

Role: Informatica/Oracle Developer/Designer

Project: HQBSA (Business Support Application)

The scope of the project includes enhancement, maintenance and support of the Enterprise Wide

Disbursement System (EWD), Oracle HR datamart, SAFARI and Phoenix belongs to the HQ Business

Support Applications.

EWD is Mission critical Application of GENWORTH; the minimal support recovery time for EWD is 48

Hrs. This System takes the payment request data from various GENWORTH Administrative Systems

and manual input, validates all the data, provides approval routine and then generates output in the

form of check data, positive pay files, Electronic Fund Transfer Data and General Ledger Data. Creating

setup under Appworx Scheduler application to execute EWD Application Batch Processing Task.

SAFARI application stores all the data regarding the employee’s expense details and generates

GENWORTH specific Reports. Data is extracted from external data feeds, transformed and loaded in the

data mart using Informatica. Reports are developed through BO reports as per client requirements.

Responsibilities:

Involved in all phases of SDLC from requirement, design, development, testing, pilot, training and

rollout to the field user and support for production environment.

Coded Oracle stored procedures and functions utilizing tables and triggers to control referential

integrity.

Created scheduled batch jobs using PL/SQL for downloading data into temporary tables to generate

the data required for some of the reports.

Involved in Optimization of PL/SQL procedures, functions and packages

Involved in identifying and verifying Tables, Indexes and Constraints after the database

migration.

Developed complex mappings in Informatica to load the data from various sources into the Data

Warehouse, using different transformations like Source Qualifier, Expression, Lookup,

Aggregator, Update Strategy, Joiner, and Rank.

Created and Configured Workflows, Worklets, and Sessions to transport the data to target

warehouse Oracle tables using Informatica Workflow Manager.

Scheduling the sessions to extract, transform and load data in to warehouse database on

Business requirements. Created different jobs using UNIX shell scripting to call the workflow and

run the preload and post load on UNIX server. The UNIX jobs were scheduled through Appworx

and have been involved in the administration role for the same.

Created partition tables, sizing the data and debugging the partitioning parameters

Created workflow design document, mapping design document, Integration test plan document,

Unit test plan document etc.

Supported the migration activities to develop Oracle 9ias Server for forms and reports.

Informatica Repository Manager - Admin works.

Design of Ad-hoc reporting using BO Universe.

Development and review of BO Reports.

Involved in Data Quality Assurance, created validation routines, unit test cases and test plan.

Environment: Oracle 9i, PL/SQL, SQL*PLUS, SQL*LOADER, Developer 6, Business Objects

5.1/6x/XI, Informatica Power Center 6.2.1, Windows 2000, Toad, PL/SQL, Oracle Forms and

Report 6i, Oracle 9ias migration.

Client: Genworth Financials Richmond, VA Dec 2004 – Sep 2005

Role: Oracle Developer/Designer

Project: TRANSCEND Annuity Policy Admin

Project Description:

GENWORTH uses a variety of business applications at Richmond to administer and service the

investment and insurance policies. One such business application is Transcend. Transcend currently

administers and services variable annuity policies and it has been customized by the vendor to cater to

the specific needs of GENWORTH businesses. The objective of the proposed GENWORTH-Transcend

project is to do the maintenance, configuration and enhancement of the Genworth Transcend system as

per the service requests received from GENWORTH. Transcend is a GUI based client-server system for

administration of Annuities policies. It caters to complete business needs for Annuities business. The

application is modular in design and comprises of subsystems for new business, policy administration,

agent/agency management, extracts, financial systems, Document control etc. The project involves

enhancing Transcend by adding new reports, enhancing the existing interface applications, developing

new interface application, automating batch jobs, improving efficiency, and bug fixing.

Responsibilities:

Understand the existing Annuity processing system and servicing RFS (Request For Service

Change) under maintenance/enhancement activities

Extensively used PL/SQL Procedures/Functions to build business rules

Extensively used Unix shell scripting

Developed reports Consumer wise, Electricity Consumption wise, defaulter payees list, heavy

load power Consumption, Meter Bill Register, Meter Blank, Stubs corrections list

Developed Row level and System level Triggers for the updating or any modification in the

Master Database

Normalize the database to remove redundant data

Used Toad to create new tables, partitions, indexes or to alter the existing tables as well as

forward those changes to the corresponding schemas

Was involved in migration of Magic frontend tool.

Prepared all the documents regarding process flow, PL/SQL packages, log files, possible error

messages, process code and descriptions

Actively participated in the meeting with Consumers and the management

Environment: Oracle 9i, Oracle forms and reports, Toad, PL/SQL Developer, SQL*Plus, UNIX,

Magic 7.1g, Windows 2000.

Client: Patni Computer INC, India Aug-2004 – Dec 2004

Role: Oracle APPs Developer

Project: L2HRMS Conversion kit

Project Description:

HRMS is a system, which keeps complete details of employee, designation, location, job and grade. The

prerequisite is that the data must be provided by the legacy system. The project deals with transfer of

data from legacy system’s HRMS to Oracle Applications HRMS. Development of CV40, CV60, control

file, data file, test data, packages. The important activity of this project is to supply an Oracle HRMS

format type data and use other details of legacy type data to map the descriptive type flex fields.

Responsibilities:

Creation of proper documents for the existing and modified implementation.

Developing setup for HRMS for data entry and validation.

Review and unit testing of code and interfaces.

Review of api’s for proper validations.

Prepared checklists for maintaining Standards.

Reviewing the codes developed by other team members.

Environment: Oracle Applications (11i, 11.0.3) Human Resource Management System, Oracle

9i, Toad, PL/SQL Developer, SQL*Plus, UNIX, Windows 2000.

Contact this candidate