Data Project
Resume:
Vidhya S
Senior Data Architect
SUMMARY:
Over 10+ years of hands on experience in the software industry with the know-how of the Software Development Life Cycle through Requirement Gathering, Design & Development, Testing, Implementation and Production Support.
Analyze business requirements and translate them into functional and technical design specifications and implementation plans.
Highly experienced in Software Design, Analysis, Coding, Development and Testing with specialization in ORACLE based Client Server, Database Design and Development, Implementation, Production Support, Maintenance and Quality Assurance.
Expert level Understanding of SQL Server and Oracle Databases.
Efficient in writing and tuning complex SQL statements, complex joins co-related sub-queries and SQL statements with Analytical functions.
Expert in creating T-SQL objects, working with DDL/DML, perform most of the SQL Server Enterprise Manager and Management studio functionality using T-SQL Scripts and Batches.
Experience in writing Distributed Queries between SQL Servers.
Expert in creating Indexed Views, complex Stored Procedures, effective functions, and appropriate Triggers to facilitate efficient data manipulation and data consistency.
Hands on experience in Creating Star Schema and Snow Flake Schemas.
Documenting the types and structure of the business data (logical modeling)
Analyzing and mining business data to identify patterns and correlations among the various data point
Efficient in Analyzing data, Report result, data management and monitor changes.
Strong expertise in designing and developing Business Intelligence solutions in staging, populating Operational Data Store (ODS), Enterprise Data Warehouse (EDW), Data Marts / Decision Support Systems using Informatica Power Center 9.x ETL tool.
Experienced in Repository Configuration/using Transformations, creating Informatica Mappings, Mapplets, Sessions, Worklets, Workflows, Processing tasks using Informatica Designer / Workflow Manager to move data from multiple source systems into targets.
Extensive experience in Logical / Physical data modeling using ERWin
Profound Knowledge and ability to write Packages, Stored Procedures, Functions, Temporary Tables, Views, Constraints, Sequences, Partitions, Indexes and Database Triggers using SQL and PL/SQL in Oracle.
Effectively made use of PL/SQL features such as Bulk Collections/Exceptions/Inserts, Bulk Binding, Ref-Cursors, Multi Table Inserts, SQL types in Bulk Operations for better Performance and Readability.
Experience in design, analysis, development, testing and implementation of applications in Oracle E-Business Suite R12/11i, Oracle 11g/10g/9i/8i, Oracle Forms 6i/9i/10g, Oracle Reports 6i/9i/10g.
Working Experience in Business Objects.
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 Performance Tuning and Optimization of SQL statements using EXPLAIN PLAN, TRACE, HINTS and Profiling using TKPROF.
Proficient in loading the data from flat files to the Database Tables using SQL * LOADER and EXTERNAL TABLE.
Extensive Experience in using Oracle 10g/9i/8i and tools like Toad, SQL Navigator and Data Loader.
Strong in using tools such as SQL*Plus, PL SQL Developer and TOAD. Experience in Performance Tuning & Optimization of SQL statements and Procedures.
Developed UNIX shell scripts for calling pl/sql packages/procedures to run batch jobs.
Proficient in data loading/extraction using SQL*Loader and Oracle utilities.
Extensive knowledge in tuning T-SQL (DDL and DML) queries to improve the database performance and availability.
Broad experience in Query Optimization and performance tuning of stored procedures, functions etc.
Experience in RDBMS Concepts, Database Management Systems, Database Physical and Logical design, Data Mapping, Table normalization, Data Modeling, Creating ER Diagrams using tools such as MS Visio.
Skilled in Business Intelligence tools like SQL Server 2005 Integration Services (SSIS.
Excellent Verbal & Written Communication skills and strong in Documentation.
Flexible, enthusiastic and project oriented team player with solid communication and leadership skills to develop creative solution for challenging client needs.
EDUCATION / CERTIFICATIONS:
AWS Certified Solutions Architect
Master of Computer Applications, Anna University, India
Certified R Programmer from John Hopkins University
Certified in Data scientist Toolbox from John Hopkins University
Getting and Cleaning Data from John Hopkins University
Exploratory Data Analysis from John Hopkins University
ESS 100 – Introduction to Big Data from MapR Academy
Big Data and Hadoop essentials from Udemy
TECHNICAL SKILLS:
Informatica, Teradata, Datastage, Oracle 11i/10g/9i/8i SQL, PL/SQL, MS SQL Server 2008 R2, TSQL, SQL Integration Services (SSIS), ERWIN, Microsoft Visio, SQL Reporting Services (SSRS), Oracle RMS, Netezza, UNIX Shell Scripting C, C++, Java HTML, XML, SQL*Loader, SQL* PLUS, TOAD, SQL Developer, ERWIN, Windows XP/NT/2000, Windows 95/98, UNIX
PROFESSIONAL EXPERIENCE:
Direct Energy, Houston Feb 2016 to Till Date
Sr. Data Architect
Description:
Currently working in a project called Amplify. This project collects the information of invoices and settlements for the Texas and California customers of Direct Energy. Earlier the entire process was manually run by the analyst team. I was hired to automate the entire process.
Responsibilities:
Determines database structural requirements by analyzing client operations, applications, and programming; reviewing objectives with clients; evaluating current systems;
Develops database solutions by designing proposed system; defining database physical structure and functional capabilities, security, back-up, and recovery specifications.
Installs database systems by developing flowcharts; applying optimum access techniques; coordinating installation actions; documents actions.
Maintains database performance by identifying and resolving production and application development problems; calculating optimum values for parameters; evaluating, integrating, and installing new releases; completing maintenance; answering user questions.
Wrote complex procedures in Oracle.
Created XML files using Oracle procedures.
Conduct research and make recommendations on database products, services, protocols, and standards in support of procurement and development efforts.
Applying third party upgrades to existing databases. Coordinate and work with other technical staff to develop relational databases and secondary databases (i.e. Access).
Identify inefficiencies in current databases and investigate solutions.
Diagnose and resolve database access and performance issues. Plan and coordinate data migrations between systems.
Develop, implement, and maintain change control and testing processes for modifications to databases.
Development and maintenance of the database stored procedures, views and functions for hosted web applications.
Ensure all database systems meet business and performance requirements. Produce ad-hoc queries and develop reports to support business needs. Creation and maintenance of technical documentation.
Environment: Oracle, XML, Control-M, Erwin.
Alcon Laboratories, FortWorth Aug 2015 to Jan 2016
Sr. Data Architect
Description:
The Project Pharma Analytics involves sourcing data from various subsystems, Staging, integrating and aligning to build the fact table. This process works on weekly and monthly jobs. The source data is analyzed, cleansed and moved using Informatica. The process of alignment to current and previous is done using sql server procedures.
Responsibilities:
Meeting with Cross functional team and doing the validation of cases and its implementation.
Identify opportunities for process/cost optimization, process redesign and development of new process.
Participated on designing Start Schema Model. Extract and load DB tables for a customer.
Create, update and maintain project documents including business requirements, functional and non-functional requirements, functional design, data mapping, etc.
Own, develop and nurture the overall logical & physical Data warehouse/Data-mart data model and data architectures using ERWIN
Responsible for Business Analysis and Requirements Collection.
Responsible for maintaining the integrity of the SQL database and reporting any issues to the database architect.
Assisted in mining data from the SQL database that was used in several significant presentations.
Assisted in offering support to other personnel who were required to access and analyze the SQL database.
Wrote CTE in SQL Server and migrated the data using Informatica.
Wrote complex procedures in SQL Server.
Created several tables, views using SQL Server management studio, based upon the functional specifications and primary key & foreign key constraints.
Defined constraints, rules, and defaults and involved in Database Normalization.
Manipulating, cleansing & processing data using Excel, Access and SQL. Responsible for loading, extracting and validation of client data.
Liaising with end-users and 3rd party suppliers. Analysing raw data, drawing conclusions & developing recommendations
Writing T-SQL scripts to manipulate data for data loads and extracts. Developing data analytical databases from complex financial source data.
Developed mappings, sessions and workflows in Informatica Power Center.
Identified performance issues in existing sources, targets and mappings by analyzing the data flow, evaluating transformations and tuned accordingly for better performance.
Worked with heterogeneous source to Extracted data from SQL Server and flat files and loaded to a relational warehouse.
Involved in debugging Informatica mappings, testing of Stored Procedures and Functions, Performance and Unit testing of Informatica Sessions, Batches and Target Data.
Developed Mapplets, Reusable Transformations, Source and Target definitions, mappings using Informatica 9.0.1.
Created Schemas, Partitions and Indexes in Sql Server.
Creating the weekly project status reports, tracking the progress of tasks according to schedule and reporting any risks and contingency plan to management and business users.
Environment: Informatica 9.0.1, SQL Server Management Studio, Erwin, Ctrl-M, Teradata, BTEQ, Fastload.
Charter Communications, St.Louis Mar 2015 to July2015
Data Architect
Description:
The project Credit compliance reporting was built to verify the customers orders based on their credit reporting. This projects sources data from Salesforce and CARP (SQL Server DB). Used Informatica to pull data from Salesforce (Online) and CARP and migrated the data to Teradata. All sources are staged in Teradata and procedures are built on top of it to be used for this project. Appworx was used as a scheduling tool for this project.
Responsibilities:
Responsible for Business Analysis and Requirements Collection.
Perform Data analysis and understand Business requirements. Lead the effort on Data Validations. Implemented File management system including File Validation for source.
Worked on maintaining the quality standards on ETL, Data and report level.
Created views and aggregate tables to support reports, Wrote Database Scripts in order to support the project.
Develop and reframe solutions based on performance Optimizations.
Worked on Data Encryption, Validation and Standardization.
Worked on Informatica Power Center tools- Designer, Repository Manager, Workflow Manager, and Workflow Monitor.
Collecting, collating and carrying out complex data analysis in support of management & customer requests.
Performing daily system checks, Data entry, data auditing, creating data reports & monitoring all data for accuracy.
Designing, developing and implementing new functionality.
Monitoring the automated loading processes.
Advising on the suitability of methodologies and suggesting improvements. Carrying out specified data processing and statistical techniques.
Parsed high-level design specification to simple ETL coding and mapping standards.
Developed and maintained ETL (Extract, Transformation and Loading) mappings to extract the data from multiple source systems like Salesforce, SQL server and Flat files and loaded into Teradata.
Developed Informatica Workflows and sessions associated with the mappings using Workflow Manager.
Designed and developed complex mappings by using Lookup, Expression, Update, Sequence generator, Aggregator etc.
Wrote complex Stored procedure in Teradata.
Worked on performance tuning of code in Teradata.
Scheduled jobs using Appworx.
Wrote CTE in SQL Server and migrated the data from SQL Server to Teradata using Informatica.
Involved in debugging Informatica mappings, testing of Stored Procedures and Functions, Performance and Unit testing of Informatica Sessions, Batches and Target Data.
Developed Mapplets, Reusable Transformations, Source and Target definitions, mappings using Informatica 9.0.1.
Environment: Teradata 14.0, Informatica 9.0.1, SQL Server Management Studio, Appworx, Salesforce, BTEQ, FastLoad.
Academy Sports + Outdoors, Katy, TX Nov 2012 – Feb 2015
Sr.Data Analyst
Description:
This project deals with building the datawarehouse for Academy Sports. Our team built the ODS having sources from various subsystems like PIM, PPM, RMS etc. Worked in maintaining the Product Information (PIM) and Ecommerce (ECom) support using T-SQL. The Academy.com website uses the data from PIM to show the price and discounts on the products. So, the major task was to have the data being more precise and reflect the updates based on the store. The dataflow was from PIM to ECom. The initial data comes from Oracle RMS to Netezza Database.
Responsibilities:
Checking of supplied data for sense, consistency and accuracy.
Sound understanding of relational, object and dimensional databases
Analysis of Oracle Retail RMS design to build Retail Data Warehouse Data model in Netezza.
DW Project planning, ETL Design architecture, project scoping, project estimation, project execution.
Analyze the client’s current DW standard, BI Design and designed recommendations, best design practice, ETL standards and ETL Design methodologies.
Designed ETL multi instance job template for Data pull from source system, design shared containers for CDC, Job templates for dimension and fact build and load.
Designed and developed process flow for entire data warehouse using UC4 automation.
Developed Complex Procedures, indexes, Partition Schemas, Partition Functions, triggers using T-SQL on Microsoft SQL Server.
Developed Simple reports using SSRS.
Created Views, tables and procedures in Netezza replicating RMS.
Performance tuned on Oracle RMS procedures .
Loaded the data from flat files to the Database Tables using SQL * Loader and External Table.
Created Triggers to enforce data and referential integrity.
Generated complex stored procedures and functions for better performance and flexibility.
Created indexes for the quick retrieval of the data from the database.
Defined constraints, rules, indexes and views based on business requirements.
Used Execution Plan, SQL Profiler and database engine tuning advisor to optimize queries and enhance the performance of databases.
Environment: IBM DataStage 8.7, IBM AIX, UC4 Automic, Oracle RMS 13.2, Netezza Striper 4, SQL Server 2008 R2, MS SQL Server 2008 R2, TSQL.
National Processing Company, Houston, TX Dec 2011 – Oct 2012
Sr.Database Developer / Oracle Applications Technical Consultant
Description:
Worked in creating Positive Pay File for the banks. All the issued and voided checks for that day were to be presented to the bank. Designed the checks and worked on the NACHA Files. Did more of a production support role apart from working on few development projects.
Responsibilities:
Designed the check for NPC using Flexform Developer Software.
Wrote a PL/SQL procedures to create positive pay file.
Worked on forms and reports to send NACHA files to the banks.
Supported Comerica and Compass bank to get the daily payment batches.
Developed custom responsibility and menus in Oracle Applications.
Involved in Importing the Orders data from legacy system to Order Entry Interface tables using SQL*Loader, validating the data using PL/SQL Script and Creating Orders using Order Import Program
Extensive use of PL/SQL programming like Procedures, Functions, Triggers & Packages and Used Dynamic SQL in stored procedures.
Extensively used EXPLAIN PLAN, HINTS for performance tuning.
Involved in debugging and Tuning the PL/SQL code, tuning queries, optimization for the Oracle database.
Defining setup option, Accounting Flex fields, set of books, security rules, recovery Journals, Budget, Budgets organizations mass budget formulae, roll up groups in General Ledger Modules.
Environment: Oracle 11g, Oracle Applications 11i,, PL/SQL, Reports, SQL*Loader, TOAD, UNIX,
Flexform Developer.
Sun Microsystems/Oracle, Menlo Park, CA Nov 2007 – Oct 2011
Database Developer
Description:
This project deals with the marketing team of SUN Microsystems. The aim of this project is to identify the number of people trying to download or register Sun products. It acts as a funnel systems where the process goes from registering the customer, going for a free trial of the product, downloading it for free, buying the product etc. The customer details are being given to Rainmaker for campaigning. This project includes all sun products like Java, glassfish, Solaris 10, NetBEANS, open storage, Java FX etc.
Responsibilities:
Understanding business process and Requirements analysis.
Developed Data Model for Solaris Monetization process and later was involved in customizing the model further based on new additions.
Involved in conceptual, logical, physical data modeling for Solaris monetization marketing projects using ERWin.
Involved in developing conceptual model during enhancements.
Fine Tuned procedures for the maximum efficiency in various schemas across databases using Oracle Hints, Explain plans.
I have extensively worked on Project Metrics which includes preparation of Global Metrics (viz) Resource Estimation, Monthly Project Billing etc.
Generated server side PL/SQL scripts for data manipulation and validation and materialized views for remote instances.
Involved in the creation of database objects like tables, views, stored procedures, functions, packages, DB triggers, Indexes and Collections.
Coordinating with the onsite manager to train the offshore guys on the project.
Coordinate with the front end design team to provide them with the necessary stored procedures and packages and the necessary insight into the data.
Documented the entire project.
Developed shell scripts to automate file manipulation and data loading procedures.
Prepared the System Test Plan.
Environment:
Oracle 11i, PL/SQL, SQL*LDR, UNIX, SQL
General Re-insurance Jan 2005 – May 2007
Milford, CT
Sr Oracle/PL/SQL Developer
Project Name: Ajax Lot 2
Description:
Gen Re is a German based Re insurance company located in Cologne (Germany). It basically insures the insurance companies. This project deals with the creation of estimates and ratios. The estimation is calculated based on the previous year’s values. Based on the ratio created, the withdrawal amount is calculated. The withdrawal amount depends on the time the amount is withdrawn and the type of cedent who withdraws it. Finally the net profit is calculated based on the estimated value deducted from the withdrawn amount. The backend process is done in PL/SQL while the calling part is done from Java.
Responsibilities:
Offshore module lead for the withdrawal reinsurance model.
Involved in preparation of conceptual design for the given module.
Coordinate with the front end design team to provide them with the necessary stored procedures and packages and the necessary insight into the data.
Generated server side PL/SQL scripts for data manipulation and validation and materialized views for remote instances.
Involved in the creation of database objects like tables, views, stored procedures, functions, packages, DB triggers, Indexes and Collections.
Designed and developed BO report for creating daily flash reports
Worked in migration project to convert all existing SQL reports to Business Object reports.( BOBJ)
Used Eclipse 3.0 to call the procedures.
Developed shell scripts to automate file manipulation and data loading procedures.
Developed the webpage using JSP.
Used J2EE as a front end tool.
Prepared the traceability metrics.
Fine Tuned procedures for the maximum efficiency in various schemas across databases using Oracle Hints, Explain plans.
Implemented the six sigma concept in the project by reducing the fault rate.
Prepared the System Test Plan for the project.
Helped in UAT support.
Prepared the Functional specification document telling the functionality of the project.
Before giving the code for delivery passed Internal Quality (IQ), External Quality (EQ) and Final Inspection (FI).
Prepared the Global Metrics for the project by calculating the number of tickets received, number of tickets solved types of tickets, hours worked on it by the team etc.
Environment:
Oracle 11i, PL/SQL, Java, SQL, BOBJ.
General Re-insurance Nov 2006 – May 2007
Team Lead
Project: Reserve Roll Forward
Description:
This Project deals with the reserves and payments. We have annuity reserve, monthly reserve, annuity payment, monthly payment. The payment or reserve made for a year is under the category of annuity while the payments or reserve made for months belong to the category of monthly. The amount of reserve is calculated based in cash or annuity and is deducted based on the payments accordingly. The balance amount is rolled forward for the subsequent month as Reserve Roll Forward. This project mainly uses PL/SQL as its backend.
Responsibilities:
Developed views, functions, procedures, triggers, packages using PL/SQL & SQL to transform data between source staging area to target staging area.
Wrote SQL, PLSQL programs required to retrieve data from the Data repository using cursors and exception handling.
Wrote shell scripts for automation.
Written Shell Scripts for taking backup and recovery, written various procedures using PL/SQL, designed and developed new Forms and Reports.
Developed SQL queries to fetch complex data from different tables in remote databases using joins, database links and kept logs.
Fine Tuned procedures for the maximum efficiency in various schemas across databases using Oracle Hints, Explain plans.
Prepared the Software Requirements specification document for the project.
Prepared the System Test Plan for the project.
Developed the webpage using JSP.
Used J2EE as a front end tool.
Fine Tuned procedures for the maximum efficiency in various schemas across databases using Oracle Hints, Explain plans.
Helped in UAT support.
Prepared the Functional specification document telling the functionality of the project.
Before giving the code for delivery passed Internal Quality (IQ), External Quality (EQ) and Final Inspection (FI).
Environment:
Oracle 10g, PL/SQL, SQL, UNIX
General Re-insurance Sep 2006 – Nov 2006
Team Lead
Project Name: Cancelled Vouchers
Description:
This was a short term project. It mainly dealt with the voucher transactions. There were vouchers which were wrongly posted in the production run. To cancel those vouchers which were wrongly posted, this project was implemented. It has a few changes in the existing code. The code changes were done from PL/SQL. A new procedure was implemented to the existing package to implement the logic.
Responsibilities:
Modifying and developing database triggers, cursors, procedures, functions and packages to meet business requirements.
Writing (Back-end) PL/SQL code to implement business rules through triggers, procedures, functions, and packages using TOAD.
Requirement gathering from the onsite people.
Fine Tuned procedures for the maximum efficiency in various schemas across databases using Oracle Hints, Explain plans.
Prepared the Software Requirements specification document for the project.
Prepared the traceability metrics.
Coded in PL/SQL.
Developed the webpage using JSP.
Used core JAVA functionalities.
Prepared the System Test Plan for the project.
Coordinated in UAT support.
Prepared the Functional specification document telling the functionality of the project.
Before giving the code for delivery passed Internal Quality (IQ), External Quality (EQ) and Final Inspection (FI).
Environment:
Oracle 10g, PL/SQL, TOAD, JSP, Java
General Re-insurance May 2006 – Sep 2006
Programmer Analyst
Project: Ajax Lot 1
Description:
This project deals with the creation of estimates and ratios for non-life insurance. It has both treaty and facultative business involved. The estimates are based on previous month deposits and ratios are calculated based on the created estimate & amount received. Based on the ratio created, the withdrawal amount is calculated and the net profit is found out. The amount is handled in different ways for the existing customers, dead customers and inactive customers. The jobs are run on both the monthly basis and on yearly basis. When there is no need to run a job for a specific month, we schedule the job for two subsequent months (batch processing) to avoid unwanted runs. The backend process is done in PL/SQL while the calling part is done using JAVA.
Responsibilities:
Optimized and tuned PL/SQL to reduce the loading process - Utilized TOAD to perform bulk data loads into database tables from external data files.
Used J2EE as a front end tool.
Developed the webpage using JSP.
Created Custom Triggers, Stored Procedures, Packages and SQL Scripts.
Successfully created and debugged many PL/SQL procedures, functions and Packages for the application.
Developed SQL queries to fetch complex data from different tables in remote databases using joins, database links and kept logs.
Fine Tuned procedures for the maximum efficiency in various schemas across databases using Oracle Hints, Explain plans.
Migrated data from Flat Files to Oracle database using SQL*Loader.
Created and managed primary data base objects such as Tables, Views, Indexes, and Synonyms.
Responsible for the development and maintenance of end-user documentation.
Was involved in post production support for 3 months by handling user training, documentation and ticket handling.
Environment:
Oracle 10g, PL/SQL, TOAD, SQL*LDR
General Re-insurance Dec 2005 – May 2006
Programmer Analyst
Project: Agamemnon.
Description:
This project calculates the segment level information. It calculates the risk load factor. The risk factor depends on the type of cedent and the amount of money invested. Each cedent detail is collected and based on the segment, the profit or loss is calculated. The calculated profit / loss is written to a temp table and after verification its posted back to the main table and the temp table is deleted after it is posted to the main table. We made a performance tuning in this project to make it work so efficient.
Responsibilities:
Review and study of High Level and Low Level Design documents.
Created Custom Triggers, Stored Procedures, Packages and SQL Scripts.
Involved in creation of Implementation and Testing Documentation.
Extensively performed DML, DDL, DCL, and Transactional Control Statements.
Helped in UAT support.
Took technical session on performance tuning for my team members.
Implemented the six sigma concept in the project by reducing the fault rate.
Prepared the System Test Plan for the project.
Prepared the Functional specification document telling the functionality of the project.
Before giving the code for delivery passed Internal Quality (IQ), External Quality (EQ) and Final Inspection (FI).
Followed the coding standards and implemented new standards to make the project grow even better.
Prepared the Global Metrics for the project by calculating the number of tickets received, number of tickets solved types of tickets, hours worked on it by the team etc.
Environment:
PL/SQL, SQL*LDR, Java
SBC Yahoo / HCL Technologies Nov 2004 – Nov 2005
Technical Support Engineer
Description:
HCL Technologies is a global IT Services company headquartered in Noida, a suburb of Delhi, India led by Vineet Nayar, HCL Technologies, along with its subsidiaries, had consolidated revenues of US$5 billion, as of 2010, and employed more than 58,129 workers. The name HCL is an abbreviation on Hindustan Computers Limited. Worked as a technical support engineer for various hardware related issues. It involved troubleshooting of internet services, modems, routers etc. I have also taken calls to resolve issues related to hardware troubleshooting.
Responsibilities:
Identifying the problem with modem and routers.
Troubleshooting various internet related issues.
On call support for all technical issues.
Deals with all wired and wireless issues connected to networks.
Documenting the solution as how the problem was solved.
Contact this candidate