Developer Data Warehouse

Frank Wang

Telephone: 1-450-***-**** E-mail: *************@*******.***

Objective:

To seeking a challenging position as an ETL developer/data warehouse/BI specialist.

Personal Strengths:

Developed strong Database analysis, Data warehouse/mart architecture, ETL developing, report creation skills through 12 years of work experiences in the telecommunication, information technology, insurance, retail, medical industries.

Extensive experience in database analysis: 12 years of data warehouse/mart/DB analysis, 7 years of ETL including 4 years of Informatica Powercenter (9.0.1/8.6/7.1/6.1), 1.5 year of SSIS, 0.5 year of Datastage(7.5) and 1 year of report creation with BO, Microstrategy and Cognos.

Rich experience in the whole life-cycle development of data warehouse/Mart including data warehouse/mart star-schema designing, Data profiling, data cleansing, data modelling, ETL mapping specification, coding, data flow, error handling, recovery and performance tuning.

Implemented Conceptual/Logical and Physical Data Modeling of RDBMS, Data Warehouses and Data Marts using Erwin, Visio and MicroStrategy.

Solid experience in PL/SQL, T-SQL, Teradata SQL programming and 6+ years experience in various enterprise application systems development lifecycle.

Rich experience and solid knowledge of DML, DDL, SQL, Java, Unix Shell Script, etc.

Good team player and self-motivated worker, quick learning and hard working.

SKILLS

Development Tools : ETL: Informatica Powercenter (6.1/7.1/8.6/9.0.1), DataStage, Datastage PX, SSIS, OWB;

Report: Business Objects, Microstategy, Cognos, SSRS, SSAS;

DB architecture: PowerDesigner, ER/WIN, Visio;

DB clients: Toad, SQL Detective, Teradata SQL Assistant, SQL developer

UNIX Shell Scripting, Oracle Forms 6i/9i, Oracle Reports 9i, PL/SQL, T-SQL

Operating Systems : UNIX, Windows NT/2000/2003/2008, Linux

Databases : Oracle (8i/9i/10g/11G), SQL Server 7/2000/2005/2008, TeraData

Methodologies: Waterfall and agile.

EDUCATION

1.5 Specialized Master in Computer Science University of Montreal (Ecole Polytechnique), Montreal, Quebec, Canada

4 Bachelor’s degree in Engineering Beijing University of Aeronautics and Astronautics, Beijing, China

LANGUAGES

English, Mandarin and French.

CERTIFICATIONS

• Informatica Certified Developer - 8.x

• Informatica Certified Administrator - 8.x

• MCITP for SQL server 2005 Business Intelligent

• MCITP for SQL server 2008 Database Developer

• Pass Oracle OCP 9i exams

• MCSE for windows NT4/2000

PROFESSIONAL EXPERIENCE

TEKSYSTEMS (JAN. 2009 – TO PRESENT)

Yale University DWH Support and Enhansement

Title: ETL developer Montreal, CA

Jun.. 2011- present

Project Highlights: Provided support and enhancement to the DWH of Yale University.

Accomplishments: Quickly master knowledge of complex DWH and transfer to India.

Environment: Windows 2003, Informatica 8.6.1, AIX 5.1, Linux, Oracle 10G, Autosys

Responsibilities and Duties:

• Master knowledge of ETL process of DWH, including hundreds of Oracle PL/SQL packages, tens of Informatica workflows, hundreds of Autosys jobs, and transfer them to India support team;

• Follow the current standards to create new or enhance existing PL/SQL packages and Informatica mappings, workflows for the new issues found.

• On-call support for the process of ETL during night.

• Rewriting System Design Documentation for current ETL process;

• Fully documented every PL/SQL package, mapping and workflow process following the standards of Yale University.

AFC Data Warehouse – Adesa

Title: ETL developer Montreal, CA

Nov. 2010- Jun.2010 - 8 months

Project Highlights: Provided an improved solution for AFC data warehouse for Adesa.

Accomplishments: The client was very satisfied with the performance and robustness of the enhanced ETL processes.

Environment: Windows 2003, Informatica 8.6.1, Oracle 10G

Responsibilities and Duties:

• Improved current mapping by adding PCM (Process Control Manage) with a concurrent implementation of CDC (Change Data Capture) logic to enhance performance.

• Separated extremely large FACT load mappings to facilitate developing, testing & debugging.

• Improved existing workflows by integrating auditing, notification and reconciliation logic.

• Fully documented 30 mappings and workflows.

Content Builder & Loader – McKesson

Title: ETL architect/lead and DBA Montreal, CA

Jun. 2010 - Dec.2010 - 7 months

Project Highlights: Provided an improved ETL solution to replace current ETL process for McKesson.

Accomplishments: The client was satisfied with a dramatic 700% performance improvement of the critical ETL load, which saw overall execution time drop from 35 hours down to 5;

Environment: Windows 2003, Oracle 10G.

Responsibilities and Duties:

• Redesigned the ETL architecture to improve the current ETL process.

• Made name convention, store procedure template for other ETL developers.

• Configured and created Development, QA and production environment databases.

• Created Oracle PL/SQL stored procedures and packages to extract, transform and load data;

• Performed performance tuning on SQL scripts, configured database and hardware platform;

• Revised team ETL developer’s PL/SQL scripts to ensure compliance to corporate standards and to improve performance;

Pay Stub Portal – First Data Corp. (FDC)

Title: Database developer and DBA Montreal, CA

Mar. 2010-Dec.2010 - 8 months

Project Highlights: Created a new Pay Stub Portal for First Data Corporation.

Accomplishments: Client was very satisfied and extended the project several times to add new functionality.

Environment: Windows 2003, Oracle 10G, Eclipse.

Responsibilities and Duties:

• Created Logical Data Model;

• Implemented the Physical Data Model;

• Created Stored Procedures, PL/SQL procedures/packages for JAVA developers;

• Assisted JAVA developers in solving Oracle database problems they were encountering.

MAS for University of Rochester

Title: ETL Architect/developer Montreal, CA

Mar. 2009-Dec.2009 - 10 months

Project Highlights: Developed ETL processes for Medical Advance System for University of Rochester.

Accomplishments: The client was very satisfied with the cleansed, normalized and consolidated data.

Environment: Solaris 10, Oracle 10G.

Responsibilities and Duties:

• Designed hand-coded complex ETL process flows with full features of ETL auditing, checkpoint, recovery, data validation, error logging, error handling.

• Created consolidation workflow to merge data amongst patient and guarantor.

• Developed UNIX (Ksh) scripts to feed flat files into, and extract from, third party server,

• Developed Oracle packages, stored procedures, sequences and functions to audit ETL process,

• Cleansed, transformed and consolidated data from flat files into staging area tables that were subsequently loaded into target tables.

• Deployed code from DEV to QA and provided completely documented the ETL process.

CGI (NOV. 2006 – DEC.2008)

PSP-DataMart Bell Canada

Title: DataMart architect, ETL and report developer Montreal, CA

May. 2008-Dec. 2008 – 7 months

Accomplishments: Functionally designed Datamart architecture, developed ETL code and created Datamart reports.

Environment: Informatica Powercenter 8.5, Oracle 10G, Teradata, Sybase PowerDesigner 10, MicroStrategy 8.0.1, SQL Detective

Responsibilities and Duties:

• Adhering to business requirements, designed a star-schema DataMart for Bell’s Voice Degradation system that included 13 dimension tables & 6 fact tables (PowerDesigner).

• Worked through whole ETL development lifecycle in Informatica Powercenter mapping environment: extracting source to staging area, transforming integration layer data to data-mart, error handling etc.

• Authored ETL mapping specifications from source to staging area database, DataMart, and aggregation tables.

• Created SQL queries to profile the data in from different of sources and created several stored procedures to cleanse data and to populate the date dimension tables.

• Developed ETL code with a Changed Data Capture (CDC) process, that performed both an initial load and incremental load with Powercenter 8.5 for 7 dimension tables, 4 fact tables.

• Created temporary report with SQL query from the DataMart for the purpose of validation.

• Created schema objects of attributes, facts, metrics, and hierarchies with MicroStrategy. Subsequently MicroStrategy reports were created to meet the customer’s requirements.

• Involved in performance tuning and error handling processing for Powercenter and Oracle database.

JH Capacity - John Hancock Insurance

Title: ETL and report developer Montreal, Canada

Mar. 2007 –May. 2008 – 13 months

Accomplishments: Worked through the whole ETL development lifecycle with SSIS of SQL Server 2005.

Environment: Windows XP, Windows 2003 Enterprise, Windows 2003 Server, SQL Server 2005 Developer Edition, SQL Server 2005 Standard Edition, SSIS

Responsibilities and Duties:

• Cooperated with data warehouse architect to create tables, indexes, constrains in the pre-staging area, staging area and data warehouse database.

• Developed stored procedures to populate Date, Hour, Minute dimension tables.

• Developed more than 100 SSIS packages to parse AIX, SOLARIS, and WINTEL text files, to clean, transform data and load data into data warehouse.

• Deployed all the SSIS packages from DEV to TEST and Prod.

• Created BO universe and 3 fixed reports.

• Authored JH Capacity ETL Specification document for the ETL support team.

Sympatico Visibility Reporting - Bell Canada

Title: TeraData SQL Developer Montreal, Canada

January 2007 to March 2007 - 2 months

Accomplishments: Developed complicated queries, stored procedures, and views with Teradata Assistant to create Sympatico Losses, VAS reports in a complex data warehouse environment

Environment: Windows XP, Teradata SQL Assistant 7.1, Teradata Data Warehouse, Excel

Responsibilities and Duties:

• Manually developed queries, views with Teradata Assistant and Excel files to create summarizing report for Sympatico losses on a monthly basis (month prior) that was used to determine the variance of total Sympatico Losses related to local losses.

• Manually developed queries, stored procedures and views with Teradata Assistant and Excel files. Complicated reports were created summarizing Sympatico VAS (Value Added Service) Net Units.

FRANCIS INTERNATIONAL TRADING INC. (Nov.2004 – Aug. 2005)

Sales Data Mart Analysis System - Francis

International Trading Inc.

Title: DataMart/ETL Developer/DBA Montreal, CA

November 2004 to August 2005 - 9 months

Accomplishments: Business analysis, data profiling, data mart, ETL development

Environment: Linux, Windows NT/2000/2003, MS SQL 7/2000, Oracle 9i/10G, Access, Excel, Flat files, Informatica Powercenter 7.1, PL/SQL, T-SQL, TOAD, ERWin, Unix Shell

Responsibilities and Duties:

• Worked with business analysts and database architect to identify appropriate sources for Data Mart and collected business needs for decision support data.

• Analyzed relationship of more than one hundred undocumented legacy data tables using ERwin.

• Participated in creating logical and physical data modeling with STAR schema techniques using Erwin in Data Mart.

• Designed the ETL processes using Informatica to load data from Oracle, SQL Server, Access, Flat Files, XML Files and Excel files to target Oracle Data Warehouse database.

• Used Workflow Manager for creating, validating, and running complex mixtures of sequential and concurrent Batches and Sessions that were scheduled to run at specified times and frequencies.

LENOVO GROUP LIMITED (Dec.1996 -Feb. 2002)

DaZhong Electronics Ltd

Sales DDS Data Warehouse for DaZhong Electronics Ltd Data Warehouse/ETL Developer

February 2002 to December 2002 - 10 months

Accomplishments: Data warehouse hardware platform architecture configuration, logical/physical E/R design, and ETL development

Environment: Linux, Windows NT/2000, Oracle 8i/9i, Ms SQL, Access, Excel, Flat files, Erwin, Informatica Powercenter 5.1

Responsibilities and Duties:

• Analyzed the requirements for a new Oracle 9i data warehouse server configuration and the data storage strategy according to business rules and data processing activities.

• Participated in the logical/physical sales data warehouse architecture design using Erwin in accordance to the customer’s business needs.

• Extracted and loaded heterogeneous data sources into Oracle 9i data warehouse primarily using Informatica.

• Designed dozens of mappings stored procedures, scripts and workflows with Powercenter Designer and Server Manager. Created and scheduled Worklets.

• Reviewed error logs, bad files, session logs and rejected files to trouble shoot production issues. Validated and reviewed the results of loading fact tables and dimensional tables with the customers.

Lenovo Group Limited

Management Information System Oracle Database DBA and developer

January 1999 to March 2001 - 26 months

Accomplishments: Oracle, SQL Server database administration; performance tuning

Environment: Windows NT, Windows 2000 AD, Windows 98, Oracle 8i, Oracle 9i, SQL server 2000, SQL server 7, PL/SQL, T-SQL

Responsibilities and Duties:

• Participated with the Oracle application developer in implementing the database architecture design, establishing the database security and finally implementing performance and tuning procedures.

• Developed, tested and implemented the Oracle8i/9i production databases and provided technical support to clients and resolved any errors or issues that were identified.

• Optimized data queries and database performance, reduced data redundancy and fragmentation, migrated/loaded data from Oracle8i and SQL2000 into Oracle9i

• Wrote portable, reusable functions and procedures with PL/SQL.

• Developed Oracle applications with Oracle Forms & Report Developer Suit.

• Implemented online/offline Database Backups and managed database recovery with RMAN.

• Reviewed disaster recovery strategy, revised any disaster recovery plans and subsequently conducted tests to validate the process.

Contact this candidate