DEEPANKAR VADUKA

Cell: 201-***-****.

(acrh16@r.postjobfree.com)

Summary of experience:

10+ years of work experience in Analysis, Design, Development and Maintenance of business Intelligence solutions.

Experience in designing databases to support resource intensive Analytical / Decision support systems and high responsive transactional systems.

ETL and data integration experience in developing ETL mappings and routines using Informatica Power Center 9.x/8.x/7.x.

Extensive hands-on experience on Data modeling (Dimensional & Relational) concepts like Star-Schema Modeling, Snowflakes modeling, fact and dimension tables.

Hands on experience in designing Enterprise Data Warehouse (3NF) and Data Marts (Dimensional) using Erwin.

Created, documented and maintained logical and physical database models in compliance with enterprise standards and maintained corporate metadata definitions for enterprise data stores within a metadata repository.

Extensive hands-on Experience in Data Analysis, Data Profiling, Data Mapping and Data Cleansing for Legacy Systems as well as Data Warehouse Applications.

Experience in the development, implementation, administration and support of ETL processes for large-scale data warehouses.

Proficient in developing strategies to extract, transform and load data using Informatica transformations like Source Qualifiers, Expressions, Filters, Joiners, Routers, Union, Unconnected / Connected Lookups and Aggregators, Stored Procedures and Normalizers.

Design, create and maintain Oracle Database tables, views, BTree, Bitmap and Function Based indexes, synonyms, MVs and partitions.

Developed Complex database objects like Stored Procedures, Functions, Packages and Triggers using SQL and PL/SQL.

Worked extensively on Dynamic SQL, Ref Cursor, External Tables and Collections.

Performance tuning using Oracle Explain plan, Hints, Auto trace and TKProf.

Extensive experience in development of new solutions, process implementation, estimating, planning and team management.

Creative Problem solver who flourishes in an ambitious, driven environment with diverse team members and continually delivers projects at levels exceeding all expectations. Self-Starter who can adapt and learn fast and proficient with excellent communication & interpersonal skills.

Technical Profile:

ETL Tools : Informatica Power Center 9/8/7

RDBMS : EXADATA, Oracle 11g/10g/9i, MS SQL Server 2008

Data modeling tools : ERwin r8.2, ER Studio V8.0.1and Oracle Designer

Tools : Goldengate, Oracle CDC, Toad, SQL Developer

Business Intelligence : Business Objects 5.1, Microstrategy 9/8

Languages : SQL, PL/SQL, Perl, C, Visual Basic

Operating Systems : Microsoft Windows 9x/NT/2000/XP/Vista/7, UNIX AIX

Education:

M.S in Electrical Engineering. (Fairleigh Dickinson University, NJ)

B. Tech in Electronics and Communications Engineering. (JNTU, India)

Professional Experience

Nextgen Revenue Optimization System

Starwood Hotels & Resorts, CT

Jun’12 – Present

Role: Principal Data Architect

Description: Ambitious undertaking by the organization to revamp the legacy revenue optimization system with the next generation of revenue analytics that was worth close to 50 million dollars. The goal was to provide the DORMs and revenue managers the ability to view the hotels metrics like occupancy, room rates and provide insights that help in making decisions to improve the RevPAR. The system would forecast the occupancy rates, demand patterns and suggest room rates and inventory control measures by analyzing the data in real time.

Responsibilities:

Responsible to architect an end-to-end database to support the day-to-day revenue management operations.

Hosted sessions with business users to gather requirements and review the BRD provided by the business analysts.

Responsible to deliver Conceptual, Logical and Physical Model using Erwin and provide metadata, data mapping and ETL packages.

Studied in house requirements for the Analytics and Web Applications and proposed architectural solutions to support varied demands of the both the functional areas.

Proposed model is a combination of normalized (3NF) tables and summarized facts and periodic aggregates.

The model consisted of 300 plus entities, which were sub divided into several subject areas for ease of use. The steady state database was estimated to be 30 TB in size.

Reverse engineered the existing database objects using ERwin. Used macros to customize the DDL generated by forward engineering.

Performed Data cleansing and Data profiling for detecting and rectifying inaccurate data from the databases and to track data quality and to assess the risk involved in integrating data for new applications.

Facilitated development, testing and maintenance of data quality guidelines and procedures along with necessary documentation.

Designed data flow and auditing processes to support reporting services and data analysis.

Worked with Database Administrators, Business Analysts and Content Developers to conduct design reviews and validate the developed models. Provided growth estimates of the database and worked with the DBA team to allocate storage actively involved in performance Tuning and formulating strategies to reduce the data redundancy without sacrificing the data availability. Partitioning tables and compression techniques were adopted to reduce the footprint of the database.

Extracted data from the source databases (Oracle, FLAT FILES) using Informatica and SQL* loader to provide seed data for the initial phase of implementation.

Provided solutions in PL/SQL packages and procedures.

Co-ordinated with tech leads and planned deployments of various releases as per the project schedule. Non-production and staging databases were maintained and synched up to facilitate simultaneous work on multiple releases.

Environment: ERWIN Data Modeling tool r8, Informatica 9.1, Microstrategy, R, Cplex, Oracle 11g & 10g, PL/SQL, Goldengate, Microstrategy 9, SQL Server 2008, MS Visio, TOAD, UNIX AIX 5.1

ISAC SMPR Reports

Starwood Hotels & Resorts, CT

Feb‘11 – Jun’12

Role: Lead ETL Architect.

Description: The project was aimed to provide sales managers with the ability to review performance of the sales associates based on the quotes and revenue generated from their leads. On the front end, the application was being revamped to provide integrated platform for the S&C system. My responsibility was to aid in the property transition to the new system, by porting the data over from the legacy Delphi system. Once the historical data is transferred to the new marts/dimension, daily transactional data is also processed into the same tables.

Responsibilities:

Interacted with Business Analysts for Requirement gathering, understanding of technical processes and Application flow.

Document the transition from the legacy system to the new ISAC system. The document helped to map the legacy artifacts with the new system. Crafted and collated data from legacy systems.

Responsible for technical architecture of defining data model for staging and reporting databases.

Provided a conceptual, logical and physical data model using ERwin 4 which was scalable and at the same time provided optimal performance.

Identified Multiple Dimensions and Fact tables. Used advance data modeling concepts of Degenerated dimension, sub-dimension, Fact less fact table, Aggregate fact tables in Multidimensional model.

Identified all the conformed dimensions to be included in the warehouse design and confirmed the granularity of the facts in the fact tables.

Provided data mapping and technical ETL specification documents for the team to implement the routines and processes.

Normalized the incoming files of different sources to 3NF at the staging area before loading into facts and dimension tables.

Applied data cleansing/data scrubbing techniques to ensure consistency amongst data sets.

Achieved optimal performance by sub dividing the tasks and implementing them in PL/SQL or Informatica as per the complexity of the requirements.

Used various Informatica transformations like Rank, Aggregator, Update Strategy, Joiner, Stored Procedure, and Union to develop robust mappings.

Extensively used workflow variables, mapping parameters and mapping variables.

Created mapplets to implement reusable logic in different mappings.

Developed mappings to implement type I, II slowly changing dimensions (SCD).

Developed PL/SQL procedures and functions, database triggers to be used by the team to provide consistent code with improved readability and maintenance.

Used Bulk Collections for better performance by reducing context switching between SQL and PL/SQL engines.

Created UNIX shell scripts to invoke Informatica workflows and automate the conversion process.

Environment: Informatica Power Center 8.1/7.1.1, Oracle 9i & 10g, PL/SQL, Goldengate, Microstrategy 8, SQL Server 2008, MS Visio, ERWIN Data Modeling tool, TOAD, UNIX AIX 5.1

Revenue Mgmt. Alerts & Reports

Starwood Hotels & Resorts, NY

May’10 – Feb’11

Role: Lead ETL Architect.

Description: The project was aimed to provide revenue managers with the ability to setup customizable alerts. When the data conditions are met for the alerts, an email and corresponding report will be sent to the property user. In addition, users would run the reports to view the current bookings, expected bookings and cancels. This information is calculated based off the historical demand patterns. The properties were spread across the globe and data was mandated to be available no later than 4 hours from the transactional system.

Responsibilities:

Coordinating with the business team and gathering the user requirements to create data model, mapping specs and test cases.

Responsible for design and implementation of the database model using ERwin.

Provide ETL design specification documents to outline data flow from sources to targets.

Responsible for designing, developing and implementing the incremental load and full/initial load.

Developed mappings in Informatica to load facts and dimensions from various sources into the Data Warehouse, using different transformations like Source Qualifier, Expression, Lookup, Aggregate, Update Strategy and Joiner.

Responsible for developing Mapplets/worklets to implement the reusable logic.

Involved in Performance Tuning by determining bottlenecks and optimizing various transformations like Aggregator, Lookup, and Joiner for better performance.

Created PL/SQL stored procedures using dynamic SQL to implement complex business logic and cleanup of incomplete data loads.

Improved database performance with Oracle Index techniques and partitioning concepts.

Schedule the jobs based on the property time zone. Properties were grouped into six batches based on their time zone. This approach enabled the availability of data no later than 4 hours, much earlier than the usual 24 hours delay of the data warehouse.

Created and modified several UNIX shell scripts according to the needs of the project.

Environment: Informatica Power Center 8.1/7.1.1, Oracle 9i & 10g, PL/SQL, Goldengate, Micro strategy 7i, SQL Server 2008, MS Visio, ERWIN Data Modeling tool, TOAD, Windows 2000,

UNIX AIX 5.1

Data warehouse Upgrade and Migration

Starwood Hotels & Resorts, NY

Aug’11 – Jun’12

Role: Database Migration/ ETL Specialist.

Description: The goal of the project was to upgrade data warehouse from Oracle 10g to Exadata and migrate the datacenter to a strategic location. The database was approximately 30 TB in size with hundreds of ETL processes and user reports hosted on Informatica and Microstrategy servers. In addition to the database upgrade, Informatica and Micro strategy were also upgraded. Deeply involved in design, planning and execution of migration plan to migrate / decommission all application instances and database without disruption to Business Groups.

Responsibilities:

Managed successful migration of critical production and contingency servers with no impact to business.

Created a parallel database and mirrored the existing ETL setup onto the new system to enable comprehensive analysis and comparison between the systems.

Meticulously tracked the discrepancies found and followed up with the respective teams to solve the issues. Any user-identified defects were quickly prioritized.

Categorized the tasks per business subject area and assigned the ownership to each of the team members.

Identified code behaving differently causing performance bottlenecks and promptly rectified them. Tracked any future scope of improvements within the current design.

Hosted daily status calls with on/off-shore support teams to track the status for system critical tasks and highlight risks and issues.

Led the triage team on the migration to ensure prompt resolution of all issues and successful migration.

Engaged business end users in validation activities, post cut over.

Improved the performance of the ETL by more than 30 percent after the upgrade.

Environment: Oracle 10g, PL/SQL, Goldengate, Informatica Power Center 8.1/7.1.1, Microstrategy 7i, TOAD, UNIX AIX 5.1

Goldengate POC and Implementation

Starwood Hotels & Resorts, NY

May’10-July’11

Role: Data warehouse Specialist.

Description: The project was aimed to replace the oracle CDC with a robust and reliable replication system as in Goldengate. Our team was trained by the Goldengate team and was provided hands-on experience in installation, set up, usage and monitoring of the application. My objective was to learn the system, design tables that get populated by the GG application and eventually drive the ETL processes on data warehouse.

Responsibilities:

Design the tables to support the data warehouse ETLs with ability to track and debug any issues in the replication process. The tables were to be designed with minimal impact to the existing ETL process and bringing in the advanced features of the GG application.

Get hands on knowledge on the working of Goldengate and its architecture.

Gather information on the aspects useful for enterprise data warehouse that can be leveraged for the audit process like change user, change time, change location etc.

Implement a system to track the delay in the replication process from various source extract systems. The process would alert the monitoring team if the delay exceeds a set threshold.

Provide initial support and monitoring of the system until the functional team was trained and brought up to speed.

Provided assistance to my manager in writing a book on the Goldengate process.

Environment: Informatica Power Center 8.1/7.1.1, Oracle 9i & 10g, PL/SQL, Goldengate,Microstrategy 7i, ERWIN Data Modeling tool, TOAD, UNIX AIX 5.1

Valhalla Reporting

Starwood Hotels & Resorts, NY

Jul’05 – Apr’10

Role: Informatica ETL Programmer.

Description: Starwood is one of the world's largest hotel and leisure companies, with a system that processes over $14 billion in bookings per year in online transactions. To support such a vast system they embarked onto this prestigious project. The main aim of the project was to replace its old and conventional Star link system with a centralized reservation system (Valhalla), which comprises of several engines called Booking, Promo, Rates, CPR, EDG, Inventory and Prop.

Responsibilities:

Coordinating with the client and gathering the user requirements to create mapping specs.

Handled the 2 Terabytes of data warehouse database.

Performance tuning on sources, targets, mappings and SQL queries in the transformations. Worked on flat files as sources, targets and lookups.

Responsible for migrating the folders or mappings/workflows from development to production environment, Creating shortcuts to share the repository objects

Developed various optimization techniques in Aggregator, Lookup, Joiner transformation

Designed mappings and scheduled workflows to Load data into Fact tables

Developed mapping to implement type II and type I slowly changing dimensions

Developed Informatica parameter files to filter the daily data from the source system

Used various debugging techniques and in built debugger tool to debug the mappings.

Creating Test cases for Unit Test, System Integration Test and UAT to check the data quality

Created Oracle Stored Procedure and functions to implement the business logic for better performance.

Used various Oracle Index techniques and partitioning concepts on databases to improve the query performance.

Created Materialized views for summary data to improve the query performance

Performed Administrative tasks as Informatica Administrator and participated on data-oriented tasks on Master Data projects like standardizing, cleansing, merging, de-duping rules along with UAT in each state.

Performed scheduling techniques for ETL jobs using scheduling tools, cron jobs through pmcmd commands, based on the business requirement.

Lead the efforts on knowledge transfer to the support team. Spent 3 months at the offshore location India to provide hands on training and know how on the ETL processes.

Environment:Informatica Power Center 7.1.1, Oracle 9i & 10g, SQL Server 2000, Microstrategy 7i, Mainframe DB2, MS Visio, ERWIN Data Modeling tool, TOAD, Windows 2000, UNIX AIX 5.1

Contact this candidate