Lead Data Warehouse Developer/analyst Resume
Herndon, VA
SUMMARY
- Senior Database developer with over 12 years of diversified data processing experience in Data Warehouse/ client - server Design, Development and Architecture. Extensive experience in Data Transformations, Data Loading, Database Modeling and Performance Tuning.
- 4+ years of experience in customization and configuration of Oracle Business Intelligence Suite Enterprise Edition (OBIEE).
- Experience in development of complex OBIEE reports involving Combined Requests, Filter value from other reports and complex prompt selections.
- Experience in performance tuning of OBIEE Reports involving SQL tuning, Using Oracle Hints, Aggregate tables, Cache Management.
- Extensive experience in development of OBIEE RPD Administration, involving complex data modeling, Aggregate table, Multiple Fact tables, Snow-Flake Schemas.
- Experience in development of Row level security on OBIEE Reports as well as configuring the Object level security.
- 4+ years of experience in ETL Tool using Oracle Database Integrator (ODI).
- Solid understanding of Data Warehousing life cycle and strong working experience on Data Warehousing applications. Directly responsible for the Extraction, Transformation and Loading of data from multiple sources into Data Warehouse. Worked in a variety of Business domains which include Telecom, Flood Insurance and Finance.
- Worked extensively with Dimensional Modeling (Data Marts, Facts and Dimensions), Data Migration, Data Cleansing, ETL Processes, Data Mining.
- Experienced designing Customized, Complex Forms and Reports.
- Worked on Large Database up to 10 terabytes.
- Worked on integration and implementation of projects and products, database Creations, Modeling, calculation of object sizes, table spaces and database sizes.
- Extensive Experience with UNIX Shell Scripting
- Technologies used: Oracle database, PL/SQL, SQL*Loader, External Object, Business Objects, MS SQL Server 2005/2008, TOAD, Korn Shell scripts and SQL Navigator, Oracle Data Integrator 10g+.
- Excellent knowledge of UNIX shell script on Sun Solaris 2.8/2.7 and HP UNIX 12.x/11.x.
- Have in depth Knowledge of Perl scripting for data processing.
- Have in-depth knowledge in Data analysis, Data warehousing and ETL techniques.
- Experience with Star Schema Modeling and knowledge of Snowflake Dimensional modeling.
- Extensively worked on Packages, Functions, Procedures and Triggers in PL/SQL.
- Extensive experience in loading high volume data and Performance tuning. Experience in Performance Tuning using various types of Hints, Partitioning and Indexes.
- Experience with Data Modeling using ERWIN 4.0/3.5.2.
- Experience in debugging for Extraction Transformation and Loading processes.
- Data modeling and Design with Rational Rose, Documentation, ER Diagrams, MS Visio.
- Excellent leadership skills to work in team, analytical skills, logical reasoning, interpersonal skills and attitude to lead.
- Excellent communications skills, fast & enthusiastic learner, excellent work ethics and a good team player.
TECHNICAL SKILLS
Operating Systems: Windows XP, Windows2000, Windows Server 2003, Windows NT and UNIX
Languages: SQL, PL/SQL, DB2 SQL,Shell scripting (Bourne, Korn), Perl scripting, Perl DBI Module
Database Systems: Oracle 11g/10g/9i/8i/, SQL Server, DB2, MS Access 7.0.
Application tools: Toad, Sql Navigator, PVCS, Synergy CM,Harvest, Autosys and Control-M.
Case Tools: Clear Case, Erwin 4.0.
Data Warehouse Tools: OBIEE, Oracle Data Integrator 10g+, Informatica Power center 6.2/5.1 and Erwin.
Office: Microsoft Office Word, Excel, and Power Point.
PROFESSIONAL EXPERIENCE
Confidential, Herndon, VA
Lead Data warehouse Developer/Analyst
Responsibilities:
- Design and developing ETL routines for loading of student enrollment and transcript data into the DW using ODI.
- Design Dimensional models using kimball methodology.
- Extensive Data Profiling, Mining and Data validation responsibilities.
- Design and develop ODI packages,interfaces etc for ETL Processes.
- Defined and maintained topology settings(like contexts, physical and logical dataservers etc.) in ODI for various environments
- Worked on security modules in ODI to grant user access controls
- Modified IKM’s to handle running multiple sessions in parallel
- Worked on creating and configuring stand alone agents in ODI
- Developed packages and Loadplans for various requirements
- Worked with Java developers to implement webservice calls from Java to ODI
- Working with business groups to define requirements for reporting.
- Writing PL/SQL Routines for backend data processing, Matching for various process.
- Performing lot of Tunning at databse level for SQL performances and optimization.
- Configured Physical Layer in Repository: Import tables, Create Tables, add/change Physical Joins/keys.
- Configured Logical Layer in Repository: Create Logical tables/dimensions, Columns, Create logical Joins/keys, Set Levels to support Drill-Down, Aggregation/ Sum over Columns /Dimension Hierarchies to achieve the drill down functionality.
- Configured Presentation Layer in Repository: Create Folders/Sub Folders, Standard Queries for different user Groups. Create YTD Variation using Presentation Layer.
- Created Pivot tables, line chart, column charts and bar graphs and also used navigations according to the requirement.
- Architect the security for both projects, which involved data level security as well as Object level security in OBIEE.
- Experience in configuring the scheduler of OBIEE to deliver the reports to users.
- Using OEM, TkProf, Explain plan etc for performance and optimation of sql and pl/sql.
- Developing Complex Master and Detials Forms based on multiple Tables.
- Designed and developed custom apex screens
- Expert in design and development of the three layers( Physical/Business Model ?presentation)
- Hihly skilled at configuring OBIEE Metadata Objects uncluding repository, Variable, interative Dashboards, and Reports.
- Experienced in desigining customized interactive dashboards in OBIEE using drill down, guided navigation, prompts, filters and variable.
- Did a proof of concept using TALEND to demonstrate the capabilities of open source ETL tools
Environment: OBIEE, Oracle 11g,Oracle Data Integrator 11.1.1.7, PL/SQL, Shell Scripts, Toad,SQL Developer,Oracle Apex, SAS
Confidential, Herndon, VA
Lead Data warehouse Analyst
Responsibilities:
- Define architectural standards for the team.
- Design interfaces for interacting with external systems/vendors.
- Extensively Doing Data Mining and Data Profiling.
- Defining the check point for data validation at various steps for ETL Process.
- Design and develop ETL routines.
- Designed dimensional models using kimball methodology.
- Designed and develop SSAS multidimensional cubes and aggregations.
- Worked with business groups to define requirements for reporting.
- Created Unix Shell Scripting for ETL and job automation.
- Deveoped Custmized Forms for in house application for HR Team.
- Created SSIS packages for automation of incremental processing of SSAS cubes.
Environment: Oracle 10g, PL/SQL, Shell Scripts, Windows XP, Solaris, HP-UX platforms, TOAD, Rapid SQL, SQL Developer, MS SQL Server, SSIS
Confidential, Herndon, VA
PL/SQL Developer
Responsibilities:
- Actively involved in requirement gathering and technical review meetings.
- Transformed final requirement into high level and detail level design documents and get them reviewed and approved from federal architecture team.
- Worked closely with Business Owners to make sure requirements are converted correctly to Functional Specs and the correct logic was implemented in the technical design documents and code
- Worked on designing data mart including logical and physical design
- Design data mart entities and relationships
- Involved in Extraction, Transformation and Loading (ETL) of the data in the data warehouse for Oracle 10g database
- Involved in creating procedures for Database Extracts and Offline Reports using PL/SQL
- Worked heavily on Stored Procedures, Functions and Packages in Oracle 10g
- Review data transformation rules and provide technical suggestions in data transformation logic and pseudo code (PL/SQL)
- Worked extensively on Performance Tuning and Code Optimization
- Worked on Bulk Collections, Global Temporary Tables (GTT), Autonomous Transactions, Partitioning
- Created test cases for Unit and Functional testing
- Created automated Test scripts using UT-PL/SQL to test all ETL jobs and Reports.
- Providing 24/7 Support for Functional, Regression, Integration, Load, Performance and UAT testing
- Worked closely with Production Support team to Troubleshoot and fix any production issues
- Maintained the versions of scripts, documentation and code in Rational ClearCase
- Attended Design meetings, code review and test review meetings
- Worked with DBA team to analyze and suggest the improvements in the database change request (DBCR) process in a technical environment
- Worked with performance engineering group to identify and resolve the performance bottlenecks in the batch jobs, designed and developed by the development teams
- Prepared the deployment document for code deployment in Testing and Prod environments
Environment: Oracle 10g, PL/SQL, ODI, Shell Scripts, Windows XP, Solaris, HP-UX platforms, TOAD, Rapid SQL, SQL Developer, MS Visio, Rational ClearCase, Rational ClearQuest, Telelogic DOORS. Oracle Forms/Reports
Confidential, MD
PL/SQL Developer
Responsibilities:
- Responsible for the data modeling of the application.
- Collected and prepared requirement documents for the application.
- Created pl/sql packages, procedure, triggers for the application.
- Extensively worked on Performance tuning using Explain plans and various hints.
- Responsible to indentify the production issues on regular basis.
- Created Materialized views on regular basis for reporting.
- Developed a web interface written using Oracle Forms and Oracle Application Server to provide access to data stored in a central Oracle database.
- Designed the complexed forms which uses LOV's, Triggers etc.
- Developed Complex Master and Detials Forms based on multiple Tables.
- Design and development of custom forms
- Deveoped oracle Forms for developing user interface for data querying and data loading.
Environment: Oracle 9i/10g, PL/SQL,Shell Scripts, Oracle Forms/Report (version 10g),Erwin Windows NT/2000/XP, Solaris, HP-UX platforms, TOAD, MS Visio,, Oracle Application Server, Clear Case, Clear Quest
Confidential, Richmond, VA
Lead Analyst/Database Developer
Responsibilities:
- Responsible for the design of the enhancements to the system.
- Involved in implementing the design using UNIX, perl and oracle.
- Performed lot of coding to partition the data using the existing oracle partitioning techniques.
- Used the oracle with clause and analytical functions approach a lot to process the data.
- Extensively worked on Performance tuning by using Explain plans and various hints.
- Worked on table partitioning (Range and List) and deploying various methods of indexing like local indexes and global indexes on portioned tables.
- Used Oracle Parallel execution features for parallel query, Parallel DML, DDL.
- Used Oracle Parallel hintsto improving the performanceof queries.
- Used perl’s DBI packages, hashes and arrays a lot for data processing.
- Responsible for writing Unix Shell Scripts for base automation of processes for loading and testing.
- Extensively used External Table, merge update and new oracle feature to process the data faster.
- Wrote Unix Shell Scripts, undertook Code Optimization and Performance tuning of the application.
- Involved in debugging and Tuning the PL/SQL code, tuning queries, optimization for the Oracle database.
Environment: Oracle 9i/10g, PL/SQL, Perl,Shell Scripts,,Windows NT/2000/XP, Solaris, HP-UX platforms, TOAD, MS Visio,, Oracle Application Server, Harvest tool
Confidential, Reston, VA
Sr. Unix / Oracle Developer
Responsibilities:
- Responsible for sending the database rollout script to DBA for TEST and Production releases.
- Coordinate with production support team to provide solutions for ongoing production issues.
- Involved in Extraction, Transformation and Loading (ETL) of the data in the migration process for Oracle 9i database.
- Involved in creating procedures for Raw Data Reports using PL/SQL.
- Extensively worked on Performance tuning by using Explain plans and various hints.
- Worked on table partitioning (Range and List) and deploying various methods of indexing like local indexes and global indexes on portioned tables.
- Used heavily KORN shell scriptingfor automating the data loading anddata population jobs for data marts.
- Used UNIX Shell scripts (KORN Shell) for Extracting and Parsing data from the files.
- Responsible for writing Unix Shell Scripts for base automation of processes for loading and testing.
- Extensively used External Table, merge update and new oracle feature to process the data faster.
- Identified and mapped the various entities, referential integrity constraints and relationships.
- Create Logical Model and Physical Models using ERWIN
- Loaded data from flat files into to database tables using SQL* loader and External Tables.
- Used stored procedures in Oracle for retrieving the data from the database for solving complex queries.
- Wrote Unix Shell Scripts, undertook Code Optimization and Performance tuning of the application.
- Created Stored Procedures, functions and Packages in Oracle 9i using SQL and PL/SQL for the Audit Trail using Oracle 9i as backend.
- Performed the uploading and downloading flat files from UNIX servers using FTP Protocol.
- Involved in debugging and Tuning the PL/SQL code, tuning queries, optimization for the Oracle database.
Environment: Oracle 9i/10g, PL/SQL, Shell Scripts, Windows NT/2000/XP, Solaris, HP-UX platforms, TOAD, MS Visio, Telelogic Synergy CM 6.4., Oracle Application Server CVS, PVCS
Confidential, Herndon,VA
Sr. Unix / Oracle Developer
Responsibilities:
- Analyzed Internal & External Source systems, Functional Business Documents, and Reporting Requirements to develop Purchase Order and Spending Data warehouse.
- Involved in Extraction, Transformation and Loading (ETL) of the data in the data warehouse for Oracle 9i/8i/10g database.
- Created Stored Packages, Procedures and functions in Oracle 9i using SQL and PL/SQL for the Audit Trail using Oracle 9i as backend.
- Extensively worked on Explain plans and various hints for Performance tuning.
- Used PL/SQL’s BULK COLLECTION methodology for bulk processing of that data.
- Used Oracle’s Global Temporary Tables (GTT) for data processing.
- Involved in writing complex scripts for Data Transformation, ETL process (Extract, Transform & Load)
- Created PL/SQL packages, procedures and extensively used Varrays, PL/SQL tables, cursors, user defined object types, exception handling.
- Used extensively Cursor Expressions and Table Function for data retrieval and manipulation in Oracle 9i.
- Automated unit testing using UT/PLSQL.
- Attended meetings and review to discuss current issues and processes to tackle such issues.
- Worked with performance engineering group to identify and resolve the performance bottlenecks in the batch jobs, designed and developed by the development teams
- Responsible for designing, developing and tuning PL/SQL batch jobs
- Worked with architecture team to create coding standards for the development teams
- Created DLDs, HLDs, Data flow diagrams, Activity Diagrams, ERDs for batch jobs
- Wrote UT scripts for Component and Unit Tests of batch jobs
- Used TDR to store data used for testing. This ensures none of the data used for testing is lost when the local schema is refreshed
Environment: Oracle 9i/8.x, SQL Server 2000, SQL, PL/SQL, SQL Server Enterprise Manager, Shell Scripts, Business Objects, Windows NT/2000/XP, Solaris, HP-UX platforms, TOAD, MS Visio, UT/PLSQL, Rational Rose, Clear Case, Pl/SQL Developer
Confidential, Herndon, VA
Unix / Oracle Developer
Responsibilities:
- Interacting with Business users, gathering and analyzing requirements.
- Involved in design of logical and physical data model of the components.
- Wrote complex SQL queries and PL/SQL procedures to extract data from various source tables of data warehouse.
- Performance tuning of SQL queries because data warehouse consists of many tables with millions of rows of data.
- Loaded data from flat files into to database tables using SQL* loader.
- Loaded data from flat files into to database tables using External Table (Oracle 9i New Feature for ETL).
- Extensively used IMPORT utility to load the exported files from other different sources.
- Automating data loading, extraction, report generation using UNIX shell scripting.
- Responsible for the production support and bug fixing
- For Optimization Process actively involved in performed database capacity planning, created materialized views, partitions, tables, views, indexes etc as. Tuned and optimized SQL statements.
- Designed UML Models (Rational Rose, Visio) and Relational data Models for applications.
- Designed reports and customized existing reports using Business Object. Developed various Sub-reports, Running Totals.
- Used extensively in Functionality and Logic testing for all Mappings used in populating the Data layer.
Environment: Oracle 9i/8.x, SQL Server 2000, SQL, PL/SQL, SQL Server Enterprise Manager, Shell Scripts, HP 11.0,SUN-SOLARIS 2.8, TOAD, Erwin, MS Visio.
Confidential
Portal Developer
Responsibilities:
- Developed an automated request generation and tracking system to facilitate the communication between the Workers’ Compensation Division and West Virginia University physicians, to track this communication and to provide a metric measuring the responsiveness of requests
- Built forms, reports, web pages, content areas, applications and database objects using Oracle Portal 9i.
- Developed and designed portal with oracle portlets.
- Handled Oracle Portal Administrator tasks such as creating users and passwords for the Portal web pages.
- Installation & configuration for Oracle Portal.
- Created triggers, procedures and functions for the workflow using PL\SQL.
- Created PL/SQL packages, procedures and extensively used PL/SQL tables, cursors, user defined object types, exception handling.
- Designed and created database tables and other components.
- Used oracle util smtp package to generate emails from oracle database.
- Development of Transact-SQL stored procedures, views, queries and triggers for MS SQL Servers.
- Prepared and executed unit testing and integration testing plans; performed debugging and bug fixes.
- SQL code migration from development to test and production environment.
- Designed DTS/ETL to extract external data source (Oracle, Excel file)
- Maintaining SQL Server database production and development servers for day-to-day activities.