Sr. Oracle Database/etl Developer Resume
San Francisco, CA
SUMMARY:
- Over 12 plus years of professional experience in Oracle SQL&PL/SQL, Data warehousing, Business Intelligence, Sales/Marketing Intelligence, Campaign Management Data mining, Data modeling, SAS, Hyperion Essbase, Business Objects, Microsoft SSAS, Tableau, Informatica and naming conventions of Data ware housing Applications.
- Extensive experience of PL/SQL, Forms, Reports, SQL*Loader, SQL*Plus, Functions, Stored Procedures, Packages, Database Triggers, partitions, cursor management and exception handling , System variables, Ref Cursors, Dynamic SQL, Collections, varray, objects, PL/SQL tables, bulk binds and SQL & PL/SQL Performance Tuning, Unit Testing, UAT and Debugging.
- Experience in Informatica, Ascential datastage ETL tools. Deep understanding of the Data Warehousing SDLC and architecture of ETL, reporting and BI tools.
- ETL Team Lead and architect data mart experience as well as ETL Best Practice skills.
- Involved in all phases of the SDLC (Software Development Life Cycle) from analysis, design, development, testing, implementation and maintenance with timely delivery against aggressive deadlines.
- Experience in all aspects of analytics/data warehousing design and implement Data warehouse architecture solutions (Database issues, Data modeling, Data mapping, ETL Development, metadata management, data migration and reporting solutions)
- Worked with interactive oracle tools like TOAD, SQL Navigator and PL/SQL Developer
- 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/8.x/7.x and datastage ETL tools.
- Data warehousing ETL experience using, Informatica Power Center 9.1/8.6/7.1 (Source Analyzer, Repository Manager, Server manager, mapping/mapplet/Transformations, Repository Server, Workflow Manager) Power Connects for ERP, ETL Scripts using Oracle SQL/PL SQL, MS SQL Server DTS.
- Data modeling experience in Dimensional Data Modeling like Star Schema, Snowflake Schema - FACT and Dimensions Tables using design tools like Erwin and MS Visio.
- Extensive Knowledge of ERwin, Embarcadero ER Studio, BO,BOXI, Tableau, SAP Hana, SFDC, OBIEE, Hyperion Smart View, Essbase, Excel Automation, MS-SQL and MYSQL and Teradata.
- Expertise in writing T-SQL Queries, Dynamic-queries, sub-queries and complex joins for generating Complex Stored Procedures, Triggers, User-defined Functions, Views and Cursors.
- Experience in using SSIS tools like Import and Export Wizard, Package Installation, and SSIS Package Designer.
- Proven experience in Oracle Business Intelligence and OBIEE development.
- Extensive use of UNIX shells Scripting and PERL scripting.
- Working knowledge on XML parsing and Java Script Object Notation.
- Used Version control software like SVN, Github, Visual Source Safe, Clear Case, CVS
- Having Oracle DBA experience and familiar with RAC environment and Golden Gate replication tool.
- Experience in Bigdata technologies Hadoop, Hbase, Hive pig, kafka etc …
- Having database marketing experience including campaign list execution, modeling and reporting and analytics.
- Familiar with campaign management tools like unica and Market Switch etc.
- Thorough knowledge of external customer data sources like DNB, Global Insiht, Harte Hanks and McGraw Hill etc..
- Excellent business knowledge apart from understanding data and coding language
- Excellent interpersonal and communication skills, and am experienced in working with senior level managers and developers across multiple disciplines.
TECHNICAL SKILLS:
Operating Systems: UNIX (HP, Sun Solaris and AIX), Linux, Windows.
ETL: Informatica Power Mart, Informatica Power Center 9.5/8.6/7.1, Mapping Designer, Warehouse Designer, Ascential Data Stage and AbInitio.
Business Intelligence: SAS (Base sas and Enterprise Guide), OBIEE, Hyperion Essbase, Business Objects, Tableau, Cognos 7.0 (Impromptu, IWR, Power Play, Transformer), SQL Reports, Reports 2.5.
Data Modeling: Erwin, Star-Schema Modeling, Snowflakes Modeling, FACT & Dimensions, Visio Databases Oracle 11g/10g/9i/8i/8.0, MS SQL Server 2000/7.0/6.5, Sybase SQL Server, Teradata, DB2 Access 7.02R4.1, UDB,MS
Languages & Tools: TOAD, Benthic, SQL, SQL*Plus, PL/SQL, T-SQL, Oracle Forms, Oracle Reports, OEM, RMAN, SQL*Loader, Autosys, $Universe, Espresso CAWA, Pro C, UNIX Shell Scripting, Perl, C, Java, EJB, JSP, Servlets, XML, JSON, JAX, Python, HTML, EXP/IMP, VB, ASP, NDM, UML, Sql Navigator, Golden Gate PVCS, Clear Case, VSS, CVS, Tomcat, JUnit
Hardware: IBM and IBM Compatible computers, Sun Workstation.
Nerworking: WebEx, WebEx Connect, Tele Presence
PROFESSIONAL EXPERIENCE:
Confidential, San Francisco, CA
Sr. Oracle Database/ETL Developer
Responsibilities:
- Worked in Agile environment, Developed and test PL/SQL procedures/functions/packages, unix shell and perl scripts.
- Design the Data Model and write ETL Logic and wrote PL/SQL procedures and Unix Scripts for ETL.
- Created programming code using advanced concepts of Records, Collections, Bulk binds, ref cursors, objects, PL/SQL tables and Dynamic SQL.
- Involved and interacted with business managers and user to get business requirements.
- Worked on multiple projects managed under different Jira boards.
- Used Espresso application server to manage and automate scheduling jobs.
- Developed Technical design documents and Test plan documents.
- Used Electric commander to generate build and push builds.
- Outbound data to feed SAS system to customer scoring and flags. Created sas macros for this effort.
- Generate marketing campaigns in Epiphany.
- Responsible for work on Inbound and outbound marketable data to ingest SAS, Bigdata and Epiphany systems
- Responsible to refresh data daily incremental monthly and Quarterly refresh.
- Extract data from EDW to ODS using Teradata tools like FastLoad, MultiLoad, BTEQ, FastExport.
- Developed, tested and implemented Teradata Fastload, Multiload and Bteq scripts, complex SQL, DML and DDL.
- Worked with Informatica power center Designer, Workflow Manager, Workflow Monitor and Repository Manager.
- Developed and maintained ETL (Extract, Transformation and Loading) mappings to extract the data from multiple source systems like Oracle, SQL server and Flat files and loaded into Oracle.
- Developed Informatica Workflows and sessions associated with the mappings using Workflow Manager.
- 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.1.0.
- SQL performance tuning using TkProf, SQL Trace and Explain Plan.
- Optimize SQL queries for better performance.
- Created SQL*Loader scripts for data loading and conversion.
- Used SVN & Github as version control software.
- Involved in production support on weekly basis.
- Used BI platform tool tableau developed and maintained dashboards for data visualization.
- Designed, developed and maintained Tableau functional reports based on user requirements.
- Developed Tableau visualizations and dashboards using Tableau Desktop.
- Developed Tableau workbooks from multiple data sources using Data Blending.
Environment: Oracle 11g, PL/SQL, Toad, Espresso (CAWA), Informatica, AIX, Linux, SAS, SQL, Teradata, Epiphany, Tableau Confidential, Concord, CA
Application Systems Engineer
Responsibilities:
- Involved in CCS Hemisphere Integration re-engineering project.
- Developed PL/SQL stored procedures/cursors/packages and reports.
- Converted SAS macros and data step programs to Oracle SQL/PL SQL procedures.
- Integrated multiple line of business (card services, Personal Line & Lone, Auto Finance and student educational loans) in to one BAP accommodate product driven reporting.
- Implemented consolidated BAP (CS, PL & AFS) and automated.
- Developed and automated and optimized process for generating ATF automated waterfall reports for campaign management.
- Converted SAS models, scores and exclusion/selection criteria into Oracle equallent processes and incorporated new business requirements.
- Worked on SAS to SQL conversion of CS & PLL Models.
- Integrated Auto Finance and Student lending LOBS in to Card Services and PLL LOBS.
- Developed consolidated marketable universe for CS, PL&L, AF and EFS and streamlined Hemisphere campaign backend.
- Developed Credit Individual and Customer BAP
- Developed ITA Individual and Customer BAP.
- Standardized all existing and new campaign selection/exclusion criteria’s and campaign ATF waterfall reports.
- Design and architect MDSS data mart for consolidated BAP.
- Developed Technical design documents and Test plan documents
- Used CDB, DMF & CEF Framework to create and modify production operations.
- Interacted with Risk and modeling team and SME’s to understand the business requirements and compiled requirements to develop new consolidated BAP.
- Conducted meeting with senior management to obtain direction regarding the consolidate Risk Criteria.
- Experience with Hemisphere CDB, BMG, MDSS, DMF and CEF to develop and manage Confidential Production operations.
- Created Complex ETL Packages using SSIS to extract data from staging tables to partitioned tables with incremental load.
- Worked on SSIS Package, DTS Import/Export for transferring data from Database (Oracle and Text format data) to SQL Server.
- Created user documentation for the new consolidated CS and PL&L process.
- Designed and developed standardized application to create campaign reports.
- Involved in campaign list development.
- Involved through and detailed Functional, Regression and E2E testing.
- Support Business to provide adhoc reports.
- Provide operational support as needed.
Environment: Oracle 11g, PL/SQL, SQL Developer, Base SAS 9.2, SSIS, HP UNIX.
Confidential, San Jose, CA
Lead ETL Developer
Responsibilities:
- Developed PL/SQL stored procedures/cursors/packages, unix shell scripts and perl scripts.
- Responsible for Monthly/Weekly Refreshes of SAS Datasets, Essbase cube, combined process of ETL and Reporting. Complete responsible of end to end process.
- Extract data from multiple sources and loading into MI datamart.
- Enriching data in staging tables before copying into final Fact and Dimension tables.
- Led the migration efforts of Enterprise Data warehouse source from Oracle to Teradata within extremely tight deadlines with no interruption to business.
- Developed database objects like Stored Procedures, Functions, Packages and Triggers, tables, View, Materialized Views, Synonyms, Sequences, indexes, partitions, database links.
- Created programming code using advanced pl/sql concepts of Records, Collections, Bulk binds, ref cursors, objects, PL/SQL tables and Dynamic SQL.
- Involved migration Oracle to SAS and developed advanced (proc sql) and Base SAS code.
- Involved in writing code using BASE SAS and SAS/Macros to extract clean and validate data from oracle tables. Tested and debugged existing macros.
- Designed and implemented SFDC opportunity forecast metrics for the business, which is Opportunity id level month over month tracking of Forecast and Opportunity statuses in SFDC and enrichment with competitor and MI Customer Registry/DNB data elements.
- Developed Balanced Scorecard for top wallet share report and top product family discount reports.
- Involved into code changes for segmentation methodology, new sales hierarchy implementation.
- Involved in ongoing projects like gold rush, sales optimization (SOD), Early Warning Systems.
- Develop and manage Essbase scripts for Hyperion Essbase cube built.
- Development of OBIEE components and solutions.
- Develop and design data modeling concepts using OBIEE tools.
- Used Informatica Power Center for extraction, transformation and load (ETL) of data in the data warehouse.
- Extensively used Transformations like Router, Aggregator, Normalizer, Joiner, Expression and Lookup, Update strategy and Sequence generator and Stored Procedure.
- Developed complex mappings in Informatica to load the data from various sources.
- Used Informatica Power Center Workflow manager to create sessions, workflows and batches to run with the logic embedded in the mappings.
- Extracting relevant data in required format by other information systems, loading the external data received in various formats into the databases using SQL*Loader, SQL, PL/SQL procedures and packages.
- Developed procedures for computation and of data and loaded into data warehouse. Tuned the database for faster response and quicker loading by implementing Partition, Indexing and Materialized Views.
- Designed Star and Snowflake schema dimensional modeling using Erwin.
- Data loads into oracle database are been performed by using the SQL*loader, Benthic Imp/Exports, Exp/Imports.
- Used UTL FILE to load the data into oracle tables from CSV and Text files and from tables to OS files.
- Used BI platform tool tableau developed and maintained dashboards for data visualization.
- Designed, developed and maintained Tableau functional reports based on user requirements.
- Developed Tableau visualizations and dashboards using Tableau Desktop.
- Developed Tableau workbooks from multiple data sources using Data Blending.
- Created Tableau Dashboards with interactive views, trends and drill downs along with user level security.
- Developed reports/dashboards with different analytic views(Pivot table, charts, Column selectors, view selectors)
- Building, publishing customized interactive reports and dashboards, report scheduling using Tableau server.
- Perform SQL and PL/SQL tuning activities.
- Wrote UNIX wrapper shell scripts automate E2E monthly SAS refresh.
- Used Autosys for job scheduling, monitoring schedule jobs in autosys using jil
- PVCS used for version control software.
- Leverage PGP Encryption for DNB secure FTP file transfer.
- Involved in Production Support 24x7 for rotation basis.
Environment: Oracle9i/10g/11g, PL/SQL, SQL*PLUS, TOAD 9.7, Erwin, SAS Enterprise Guide 4.3, Base SAS 9.2, TeraData, Autosys, Hyperion Essbase 11, Informatica Power Center, OBIEE, Korn shell, Tableau, PVCS, SSH Tectia,, HP UNIX, Linux
Confidential, Santa Monica, CA
Sr. Oracle Database/ETL Developer
Responsibilities:
- Developed PL/SQL functions/ stored procedures/cursors/packages and reports.
- Created new tables for supporting ETL for burn DVD
- Design the Data Model and write ETL Logic and wrote PL/SQL Scripts and Unix Scheduling Scripts for ETL.
- Created PL/SQL stored procedures by using bulk bind for inserting, updating data.
- Developing back-end PL/SQL packages, building UNIX shell scripts for data conversion & batch processing.
- Extracting relevant data in required format by other information systems, loading the external data received in various formats into the databases using SQL*Loader, SQL, PL/SQL
- Developed procedures for computation and of data and loaded into data warehouse. Tuned the database for faster response and quicker loading by implementing Partition, Indexing and Materialized Views.
- Designed system using Star and Snowflake schema using dimensional modeling for various data Marts that involved various fact tables and multiple dimensions. Designed the Aggregate tables for summary calculations. Used Erwin as Data Modeling tool.
- Support for data warehouse life cycle management, capacity planning, performance tuning, dimension and relational schema configuration, database installation/backup/restore.
- Data loads are been performed by using the SQL*loader, Exp/Imports & UTL files based on file formats.
- Tuned the application code using the TkProf, SQL Trace and Explain Plan.
- Involved in data migration from 9i to 10g.
- Performed Month End process and provide support for technical and ad-hoc reporting.
- Worked with Oracle Enterprise Manager, Toad, exp/imp, sql*loader and RMAN.
- Interacted with database using JDBC and Java.sql packages.
- Developed UNIX and PERL scripts.
- CVS used for version control software.
- Involved in Production Support 24x7 for rotation basis.
Environment: Oracle9i/10g, PL/SQL, SQL*PLUS, TOAD 8.3, cygwin, XML, java, CVS, FTP Voyage, Solaris, UNIX shell scripting
Confidential, San Francisco, CA
Sr.Oracle Developer
Responsibilities:
- Involved implementation of AppStore Q2O and O2C modules.
- Configured and managed Custom objects
- Develop functional specifications.
- Designed and creation, of the database objects including tables, triggers, indexes, partitions, views, sequences, synonyms and other objects as needed
- Developed PL/SQL functions/ stored procedures/cursors/packages/triggers and reports.
- Created PL/SQL stored procedures by using bulk bind for inserting, updating data in process tables.
- Wrote Dynamic generalized procedures for truncating current and old partition data.
- SQL performance tuning using TkProf, SQL Trace and Explain Plan.
- Optimize SQL queries for better performance.
- Created SQL*Loader scripts for data conversion
- Created Stored Procedures and Functions to be called by Java Demons.
- Developed UNIX Shell scripts.
- Clear case used for version control software.
- Performed Unit testing, white box testing and bug fixing.
- Involved in Production Support 24x7 for rotation basis.
Environment: Oracle10g, PL/SQL, TOAD 8.3, Java, JDBC, JSP, Tomcat XML, JDeveloper, UNIX, shell scripting
Confidential, NC
Oracle/ETL Developer
Responsibilities:
- Created PL/SQL stored procedures by using bulk bind for inserting, updating data in control tables.
- Designed and developed PL/SQL functions/ stored procedures/cursors/packages/triggers.
- Involved in performance tuning of the sql queries/report and query optimization.
- Extensively used the advanced features of PL/SQL like Subtypes, Records, Tables, Collections, Object types, VARRAYS, associative arrays Dynamic SQL and parallel queries.
- Created Data Models Using Erwin.
- Implemented ETL using Oracle Workflow and Oracle Warehouse Builder.
- Developed PL/SQL and ETL mappings in Oracle Warehouse Builder. Utilized Oracle Jdeveloper and Oracle Forms.
- Worked on Informatica Power Center tools - Source Analyzer, warehouse designer, Mapping Designer, Workflow Manager, mapplets, and Reusable Transformations.
- Extensively used Transformations like Router, Aggregator, Normalizer, Joiner, Expression and Lookup, Update strategy and Sequence generator and Stored Procedure.
- Used Workflow Manager (Server Manager) for Creating, Validating, Testing and running the sequential and concurrent Batches and Sessions and scheduling them to run at specified time.
- Maintain Development, Test and Production Repositories using Repository Manager.
- Developed Unix Shell Scripts to automate backend jobs, loading data into the Database using SQL* Loader
- AUTOSYS is running to initiate the loading process and error notification.
- Involved in Production Support 24 x 7 in rotation basis.
Environment: Informatica Power Center 7.1, Oracle 9i, Teradata, PL/SQL, SQL Navigator 4.3 (Oracle Query Tool), Discoverer, warehouse builder, TOAD 7.4, Autosys, Erwin, TIVOLI, UNIX, shell scripting, Windows XP Professional, Test Director 8.0
Confidential
Programmer Analyst
Responsibilities:
- Studied functionality of existing manual system.
- Preparation of Technical documents.
- Designed screens for billing module
- Extensively involved in creating database packages, procedures, functions, cursors and triggers using PL/SQL.
- Written documentation to describe program development, logic, coding, testing, changes and corrections.
- Developed Oracle Forms and Reports and PL/SQL Procedures, triggers.
- Used TOAD tool to develop procedures.
- Generates reports from the sales and distribution package such as sales analysis reports, profile wise reports,
- Involved in writing procedures, functions for calculation of receipts.
Environment: Oracle, PL/SQL, TOAD, Reports, Forms, Windows NT