We provide IT Staff Augmentation Services!

Etl Architect Resume

Plano, TX

Summary:
Over 13 years of experience in all phases of Data warehouse / Business Intelligence applications. Extensively worked on Systems Analysis, Dimensional Modeling, Data Modeling, implementing Star/Snow flake Schema, Enterprise Data Warehouse / Data mart applications. Hands on experience on Extraction Transformation and Loading (ETL/ELT) using tools like Oracle Warehouse Builder (OWB), Oracle Data Integrator (ODI), Informatica, DAC, PL/SQL, and implementing Business Intelligence reporting solutions using OBIEE Administrator, BI Answers, BI Dashboards, Oracle Reports Developer 10g, Discoverer, and Business Objects. Good exposure to Virtual Private Database (VPD) Security implementation on Facts / Dimensions. Past responsibilities include analysis, design, development, testing, change management, and program support. Provided high performance, reliable, maintainable, and mission critical decision support solutions to various industries like Utilities, Banking, Financial services, Retail, Education, Health care and Mortgage.

Software, Tools & Languages

  • OWB 11g R1, OWB 10g (R1, R2), OBIEE10.1.3.x, Informatica 8x/7x/6x/5x, BI Administration, BI Publisher, BI Answers, PL/SQL, SQL*Plus, PRO*C, Oracle SQL*Loader, C, C++, Unix, J2EE, DOS, OMB, BusinessObjects6x/5x, Designer 10g/9x, Oracle Discoverer 10g, Oracle Reports Developer 10g/2.5
  • Oracle DBMS 11gR1, Oracle DBMS 10g (R2, R1), Oracle 9i RAC, Oracle 8i/7x, MS Access, SQL Server 2005, SQL Server 2000, SYBASE X, DB/2, XML, Excel, and Flat files.
  • UNIX (AIX, HP), NT Server, Linux 4.2, AS/400, VMS, MS Windows XP/NT/Vista, VM Ware.

Education

MS in Computer Science.

Professional Experience

Confidential, Plano TX Oct 11 - Till date
ETL Architect / Modeler / Analyst

  • Analyzed the various (stores/ dotcom) systems and developed various datamarts to feed executive dashboards with metric like customer volume, financial resources, demographic details, demand, gap and alternative sales and international sales.
  • Prepared the technical design document with database objects definition, transformation logic, SQL performance improvements and pseudo code for the procedures / functions including SQL performance based on Business Requirements Document and regular meetings with business users.
  • Developed logical and physical data models to suit the executive dashboard metrics to analyze store traffic, demand, order fulfillment, alternative sales, mark downs, profitability year over year.
  • Involved in gathering user requirements, preparing design and ETL (Mappings, Process flows and PL/SQL) implementation.
  • Developed efficient process flows and stage mappings to load data into Warehouse from Oracle 11i applications.
  • Developed SCD Type2, Type3 and combination of Type2/Type3 Dimensions and fact mappings to address business requirements.
  • Created UNIX scripts to FTP/SFTP external source files and OWB MDL scripts to add / modify various operators in mappings.
  • Developed templates for Oracle BI user interfaces, such as Oracle BI Answers and Oracle BI Interactive Dashboards for Business Analysts.
  • Created implementation, migration, and production support production support documents.

Environment: OWB 11gR1, Oracle 11gR1, Oracle 11i GL, AP, PO, OBIEE10.1.3.3, BI Analytics 7.9.5, Informatica 8.6.1, TOAD 9.1, Erwin 7.3, Unix AIX 6.1, and Windows XP.

Confidential, Dallas, TX. Sept 10 - Oct 11
ETL Architect / Analyst

  • Gathered business requirements, involved in data model analysis and data profiling.
  • Developed PL/SQL procedures, functions and packages using ref cursors and collections to implement complex business requirements and error reporting for front end J2EE Business Analysis application.
  • Created Technical Design Documents with the required Transformation logic, Source Systems, Staging and Targets for Facts and Dimensions for Star / Snowflake structures.
  • Installed and configured the OWB repository, modules, users and privileges.
  • Developed PL/SQL programs, UNIX shell scripts to load high volume external source data into stage tables.
  • Developed mappings to incorporate all business rules to calculate patient stay at different facilities and addressing the providers delay times etc.
  • Developed mapping templates for Type1, Type2, Type3 and Facts, mapping documents, process flows and coordinated with the users to complete the tasks for various data marts.
  • Created materialized views for refreshing various facts at different granularity.
  • Involved in performance tuning using exchange partition, indexing and incremental loading.
  • Developed OWB migration strategy duly using collections and snapshots.
  • Set up all the documents for Backup, Recovery, Migration strategy and production support.
  • Involved in training and knowledge transfer to client resources to load various data marts.

Environment: OWB10gR2, Jdeveloper 10.1.3.3, Oracle 10gR2, Erwin 6, Linux 1.5, TOAD 9.0.1, UNIX AIX4.3 SQLDeveloper1.2.1 and Windows XP.

Confidential, Albany, NY. Oct 08 - Aug 10
Oracle Lead/Developer

  • Prepared the technical design document with database objects definition, transformation logic, SQL performance improvements and pseudo code for the procedures / functions including SQL performance based on Business Requirements Document and regular meetings with business users.
  • Involved in fixing the production issues along with necessary performance improvement strategies, and technical specifications.
  • Created technical specifications for various projects/tasks including implementation and test cases.
  • Developed PL/SQL packages with procedures, functions, cursors, dynamic SQL, table partitions for populating tables and associated index maintenance for various business processes.
  • Created process flow diagram to list out the various processes and sub processes to fit various pieces of the business process.
  • Created Integrated Process Flow with proper notifications and error recoverability to load Stage, Dimension, Fact and Summary Tables.
  • Implemented near real time solutions for transactional events to reflect in the Data warehouse.
  • Set up DB object security using Oracle VPD features and PLSQL triggers.
  • Involved in object migration from Development to QA and Production environments.
  • Created execution steps for QA and coordinated with business users during QA / UAT.
  • Developed workbooks for executives to present data at various aggregations and combinations.
  • Created overview and production support documents for various processes / issues.
  • Created shell scripts, PL/SQL procedures, and control tables to schedule DW load.

Environment: OWB 10.2.0.2, Oracle 10gR2, TOAD 8.6.1, Erwin 6.0, Discovesrer10.2.0.1, UNIX AIX 5.3, AS/400 and Windows XP.

Confidential, Principal Consultant, Feb 2006 - Sep 2008.

Confidential, Cincinnati, OH. Apr 08 - Sep 08
Oracle Lead / Developer

  • Experience in Performance Tuning and Explain plan, Sqlloader,DBMS_Scheduler, Utl_File, Triggers, Indexes, Import and Export utilities and Shell scripting for auto process.
  • Created complex functions, stored procedures and packages, Triggers by using PL/SQL.
  • Experience in conducting Requirement and Systems Analysis, Feasibility Studies, Design Reviews, Plan Reviews, Implementation, and Post Implementation surveys.
  • Involved in data profiling and data models design, defined associated transformation logic along with sorces and targets.
  • Developed efficient PL/SQL Procedures, Functions and packages to load and transform high volume data.
  • Developed UNIX shell scripts to FTP high volume flat files from external systems and to export / import of metadata from one environment to another environment.
  • Developed integrated process flow to load stage tables, dimensions and facts duly implementing sub processes and alerts to load approximately 16TB EDW in daily nightly window.
  • Developed MDL scripts to manage metadata and parameter values.
  • Implemented fine grained security at row/column level using Oracle Virtual Private Database features to restrict the various user groups appropriately.
  • Involved in Query performance optimization of the databases for source, stage and target by partitioning and creating clustered indexes by analyzing execution plan and explain plan details.
  • Coordinated with Developers, Testers, Business users and DBA during development and migration.
  • Trained the users to troubleshooting and monitoring process flow executions in work flow monitor.
  • Set up all the documents for Backup, Recovery, Migration strategy and production support.

Environment: Oracle 10gR2, OWB 10gR1/R2, SQL Server 2005, People Soft 8.9 AP, AR, GL, Siebel 8.0, Oracle OLAP AW 10.2.0.4, TOAD9.6.1, Erwin 6.0, Linux 1.5 and Windows XP.

Confidential, Raleigh, NC. Aug 07 - Mar 08
ETL Lead / Developer

  • Gathered business requirements, involved in data model analysis and data profiling.
  • Developed PL/SQL procedures, functions and packages using ref cursors and collections to implement complex business requirements and error reporting for front end J2EE Business Analysis application.
  • Created Technical Design Documents with the required Transformation logic, Source Systems, Staging and Targets for Facts and Dimensions for Star / Snowflake structures.
  • Installed and configured the OWB repository, modules, users and privileges.
  • Developed PL/SQL programs, UNIX shell scripts to load high volume external source data into stage tables.
  • Developed mapping templates for Type1, Type2, Type3 and Facts, mapping documents, process flows and coordinated with the users to complete the tasks for various data marts.
  • Created materialized views for refreshing various facts at different granularity.
  • Involved in performance tuning using exchange partition, indexing and incremental loading.
  • Developed OWB migration strategy duly using collections and snapshots.
  • Set up all the documents for Backup, Recovery, Migration strategy and production support.
  • Involved in training and knowledge transfer to client resources to load various data marts.

Environment: OWB10gR1, Jdeveloper 10.1.3.3, Oracle 10gR1, Erwin 6, Linux 1.5, TOAD 9.0.1, UNIX AIX4.3 SQLDeveloper1.2.1 and Windows XP.

Confidential, Manchester, NH Jan 07 - Jul 07
OBIEE Lead / Developer

  • Worked on Enterprise Data Warehouse project as a Developer at a large Healthcare provider.
  • Gathered user requirements, prepared design and ETL (Mappings, Workflows and PL/SQL) implementation specifications.
  • Involved in external source data analysis, data profiling and data modeling.
  • Developed shell scripts to FTP the data from external flat files.
  • Developed staging, Type1, Type2, Type3, combination of Type2/Type3 Dimension and CDC mappings and Fact mappings various data marts.
  • Developed end to end process flows to load various data marts with restart ability and proper alerts.
  • Developed PL/SQL packages to implement complex business requirements, data validations and error reporting based on vendor threshold values.
  • Developed Oracle BI Administrator Meta data for Oracle BI Answers and Oracle BI Interactive Dashboards for Business Analysts.
  • Installed Oracle workflow to monitor and administer process flow executions.
  • Involved in metadata migration, knowledge transition and trained the client resources.
  • Provided the documentation for system overview, implementation details, production support issues and resolutions.

Environment: OBIEE10.1.3.3.1, BI Analytics 7.9.3, DAC, Informatica 7.1.4, Oracle10gR2, OWB10gR2, DB/2, Erwin 7.0, Linux 1.5, SQLDeveloper1.2 and Windows XP.

Confidential, Piscataway, NJ Mar 06 - Dec 06
ETL Lead / Developer

  • Worked on an Enterprise Data Warehouse project as a Business Analyst and Developer at a large University.
  • Involved in gathering user requirements, preparing design and ETL (Mappings, Process flows and PL/SQL) implementation.
  • Developed efficient process flows and stage mappings to load data into Warehouse from Oracle 11i applications.
  • Developed SCD Type2, Type3 and combination of Type2/Type3 Dimensions and fact mappings to address business requirements.
  • Created UNIX scripts to FTP external source files and OWB MDL scripts to add / modify various operators in mappings.
  • Developed discoverer workbooks and ad-hoc reports for user analysis. Created Oracle BI metadata repositories with physical, Business Model and Presentation layers.
  • Developed templates for Oracle BI user interfaces, such as Oracle BI Answers and Oracle BI Interactive Dashboards for Business Analysts.
  • Created implementation, migration, and production support production support documents.

Environment: OWB 10gR2, Oracle 10gR2, Oracle 11i GL, AP, PO, OBIEE10.1.3.3.1, BI Analytics 7.9.3, Informatica 7.1.4, TOAD8.6.1, Designer9x, Unix AIX 5.3, Discoverer 10.2, Workflow2.6.4 and Windows XP.

Confidential, Seattle, WA Sep 05 - Feb 06
ETL Lead

  • Conducted business user meetings for gathering user requirements, prepared documents for design and ETL (Mapping, Process Flows and PL/SQL) implementation. Involved in data profiling and data modeling.
  • Setup security at user level and role level using Oracle Virtual Private Database Features.
  • Defined best practices like partition exchange, Dynamic SQL, parallel processing for loading the high volume data warehouse.
  • Installed and configured OWB Repository, Modules and users.
  • Developed efficient process flows to load data into Warehouse from external databases, flat files for a complex business process in allocated nightly window. Coordinated metadata migration across test and production environments.
  • Developed stage, SCD Type1, Type2, Type3 and combination of Type2/Type3 Dimensional mappings, various Fact mappings to address business requirements.
  • Developed UNIX scripts to FTP high volume source files, and Meta data migration.
  • Created discoverer workbooks and trained business users on various data marts.

Environment: OWB 10gR1, Oracle 10gR2, TOAD8.6.1, Designer, UNIX AIX 5.3, Discoverer 10.1, Erwin4.2, Workflow2.6.4.

Confidential, Cincinnati, OH Jan 05 - Aug 05
ETL Developer

  • Worked on an EDW project at US largest Power and Gas Distribution Company to the industrial, business and domestic consumers.
  • Instrumental in analyzing and re-architecting the existing process flows and mappings to improve the performance to ensure the completion of ETL runs in the allocated daily load window.
  • Developed parallel processes for power and gas transactions to load data against combined dimensions and facts.
  • Developed Functions, Procedures and Packages to implement complex business logic, and effectively included in the Mappings and Process Flows.
  • Involved in production support and documentation.

Environment: Oracle Warehouse Builder 9.2, Discoverer 10.1, Erwin 4.1, TOAD7.6, Oracle 9.2, Oracle11i, PL/SQL, Unix AIX 4.1, AS/400, TestDirector8.0, Cybermation ESP, WindowsXP/2000.

Confidential, Orange, CA Jan 04 - Dec 04
ETL Developer

  • Worked on an EDW project with a pool of various data marts. Involved in full life cycle Development of the Speed pay, Vision Ensemble pro Loan Servicing Call Center Data Marts.
  • Collected and analyzed business requirements with the management and converted them in to project documentation.
  • Created mapping documents with the required Transformation logic, Sources and Targets for Facts and Dimensions. Analyzed dependencies and developed transformation logic for various enhancements and integrating various marts.
  • Developed DOS batch programs to copy flat files from external sources and to use as sources for stage mappings.
  • Developed staging, target mappings, worklets and workflows for efficient loading of warehouse.

Environment: Informatica Power center 6.1, Discoverer9.0, TOAD7.0, Business Objects6.1b, Oracle 9i, SybaseX, SQL Server2000, PL/SQL, T-SQL, Sun Solaris7.0, HP-UX, and WindowsNT/2000.

Confidential, SanFrancisco, CA Aug 01 - Dec 03
Data Warehouse Developer

  • Worked on a Data Warehouse for one of the leading investment banks to analyze trading activities to capture market opportunities.
  • Involved in analyzing the data models of legacy implementations, identifying the sources for various dimensions and facts for different data marts according to star schema and snowflake schema design patterns.
  • Developed Type2 and Type3 slowly changing dimensions, sessions. Worked with pmcmd, pmrep to automate the processes and maintenance of users. Developed DOS batch scripts to copy source files from third party systems.
  • Involved unit testing, integration testing, migration and production support.

Environment: Informatica PowerCenterRTv6.0/5.1, Erwin3.0, TOAD6.3, Oracle 9i/8i, SQL Server2000, DB/2 UDBv6.1, PL/SQL, T-SQL, OS/390, Sun solaris7.0, Unix AIX4.0, Windows NT/2000.

Confidential, India Aug 98 - May 01
EDW Developer

  • Involved in the design and development of Operation Dashboard Metrics Reports for Productivity/Profitability analysis of various products Business Layers (Data Marts). Collected and analyzed business requirements as stated in the BRD with the management and converted them in to project documentation.
  • Created Forms, Reports, Triggers, PL/SQL procedures, Functions and Packages according to the business requirements. Created Technical Design Documents with the required Transformation logic, Staging and Targets for Facts and Dimensions.

Environment: Informatica 5.1, Erwin3.0, TOAD6.3, Oracle 8i, Oracle 7.3, SQL, PL/SQL, Pro*C, Developer*2000, Forms4.5, Reports2.5, SQL Server2000, DB/2 UDBv6.1, PL/SQL, Transact-SQL, OS/390, Brio SQR, Sun solaris7.0, Unix AIX4.1, Windows2000.

Hire Now