Senior Informatica ETL Developer

KWAN C. CHIANG

**************@*****.***

PROFILE

IT professional with over 15 years of ETL - Informatica, Data Warehousing, and Business Intelligence experience building data warehouse environments from conception to production (full system development life cycle) and support

CORE COMPETENCIES

Contributed and mastered in all levels of project development, including data warehousing development lead, database design, ETL mappings/workflows construct, testing, and QA.

ETL - INFORMATICA PowerCenter 9.6/9.5/9.1/8.1/6.2/5.1; Business Objects IDT/XI3/XIR2/5.1; ORACLE 11g/10g/9i; PL/SQL; Toad 9.7; SQL Developer 4.1; UNIX: Korne/Bash Shell; Tableau 10.5; Control-M Scheduler; TSO/ESP Scheduler; HP Service Manager 7; Policy Management System 5.3; Cognos 10.2.0; Cúram 6.1

APPLICATIONS

Sales and Marketing; eCommerce; Plans, Forecasts and Promotions; Warehouse Inventory Management; Retail Merchandising; Accounts Receivables and Payables; General Ledgers; Social Enterprise Management; Child Care Licensing; General Insurance

PROFESSIONAL EXPERIENCE

Walmart Canada Nov. 2017 – Mar. 2019

Position: Business Intelligence Software Developer, Technology (Consultant)

Senior Informatica ETL developer of the eCommerce BI team:

Engaged and worked on all phases of ETL development tasks using Informatica PowerCenter, Unix, and SQL

Utilized JIRA Agile for project development, with Scrum and Kanban methodologies

Gathered and analyzed task requirements, designed, developed and unit and QA tested all ETL components, i.e. mappings, sessions and tasks, and workflows

Performance benchmarking and improvements for long running workflows

Maintained database procedures, materialized views, and tables

Created and executed DDL scripts for database objects

Deployment and promotion of Informatica objects from Dev to Prod environments

QA of peers ETL work and Tableau reports

Participated in broader group team projects such as database migration projects

Technical Environment: INFORMATICA PowerCenter 9.1, Tableau 10.5.3, Oracle 11G, UNIX, Alteryx 11.7, JIRA Agile 6.7

Ministry of Education June 2016 – Mar. 2017

Position: Business Intelligence Specialist (Consultant)

Member of the BI development team within Child Care Analytics Solution:

Senior Informatica ETL developer in BI team for Licence Checklist and Licence Inspection Visit projects

Analyzed project development requirements, designed, developed and unit tested all the project’s ETL components, i.e. mappings, sessions, and workflows for Data Warehouse and Data Marts

Analyzed and developed performance enhancements where feasible

Participated in the SIT testing and supported User Acceptance Testing

Developed and tested SQL scripts for historical data loads for various fact tables

Created and maintained Source Target Mappings (STM) ETL process documents

Deployment and promotion of Informatica and database objects from DEV to SIT environments

Technical Environment: INFORMATICA PowerCenter 9.6/9.5, Cognos 10.2.0, Oracle 11G, HP ALM Quality Center 12.53, UNIX,

M/S SharePoint, WinScp, PuTTY

Ministry of Community and Social Services Jan. 2015 – Mar. 2016

Position: Senior System Analyst, L3 (Consultant)

Participated in the Tech Services team of the Social Assistance Management Systems (SAMS):

Installed and upgraded the Cúram COTS caseworker application releases through all project phases, working with development / performance teams to investigate issues and apply patches for uncovered defects

Coordinated with DB, ETL, and Control-M teams to deploy Business Intelligence Cognos 10 Reporting sub-system into multiple development and testing environments

Provided Middleware and Cognos reporting administrative support

Provided user requests supporting tasks such as shutting down, starting of Cúram apps servers and retrieving logs for technical review

Analyzed failures and associated logs, reported findings, logged defects, reported and triaged status to the Technical Services Manager

Created and updated accounts in Unix AIX, and executed Bash Shell Scripts to maintain logs and other activities

Followed strictly the IT policies, procedures, and standards set forth by the Government of Ontario

Technical Environment: Cúram 6.1, Cognos 10.2.0, Informatica 9.5, Oracle 11G, IBM AIX 6.1, IBM WebSphere, Control-M, WinScp, PuTTY

PepsiCo Foods Canada Apr. 2001 – Apr. 2014

Position: Senior Informatica ETL Developer / Enterprise Data Warehouse Analyst

Architected and developed Data Warehouse and data mart ETL solutions leveraging Informatica PowerCenter. Utilized project management and industry best practices to ensure sustainable solutions:

Enterprise Data Warehouse (EDW) (2009-2014) -- Established a formal Data Warehouse with dependent marts to support Operations and sales reporting across two diverse divisions (Quaker Foods and Frito-Lay)

Set ETL standards across numerous applications systems

Architected historical data load strategy and ETL routines and enhanced aggregate loading techniques

Formulated EDW Object Tracker and defined templates and standards for the EDW

Engineered Validation routines for sales/distribution orders, inventory movement, and physical inventory

Setup Unix pmcmd scripts for starting Informatica production workflow jobs

Essbase Financial (2013) -- Provided financial data extract for Essbase financial cubes

Extracted files from Enterprise Data Warehouse and regularly loaded to Essbase

Performed administrative support tasks and maintained Essbase cubes

Tibersoft Distributors Sales (2012) -- Provided visibility to indirect Foodservice customer sales

Integrated distributors sales data feed with PepsiCo’s internal customer and product dimensions to enable reporting

Created data profiling strategy, and conformed of different distributors data

Hand Held 4 (2010) -- Provided key sales metrics to Frito-Lay’s handheld application in support of Enhanced Route Ordering

Extracted 15-20 file feeds to merge with U.S. PepsiCo Sales Data Warehouse weekly

Coordinated with U.S. team to deliver outputs as expected in accordance with their technical requirement

Convenience/Grocery Store POS Category Management Engagement (2008/9) -- Oversaw the management of the Salty Snack category at two of the top convenience and Frito-Lay’s largest grocery customers including the tracking of customer profit, identifying listing and selling gaps at store and SKU levels

Integrated POS with internal product and customer information and created performance metrics

Account Planning and Execution (2005) -- Enabled the loading of historical sales information into the Trade Promotions Management system and creation of a data mart for tracking sales forecast accuracy

Created ETL routines for base sales and forecast analysis along with developing a forecast locking utility

Service Analysis Period (2004) and Service Analysis Week (2006) -- Prime tool for tracking customer service metrics

Created Service Analysis based on fiscal period and weekly aggregations, and implemented trigger files to launch the Business Objects reporting schedule

Route Engineering Interface (2003) -- Support of route re-engineering – optimizing store distribution between routes

Leveraged IBM mainframe's jobs and datasets to create file watcher communication between the mainframe and the Sales Data Mart

REI reverse-engineering data mapping from mainframe COBOL

Sales Data Mart (2002) -- Sales performance, service metrics, shipment trending and POS analysis. This environment is utilized by over 600 associates across the organization

Transformed and migrated Frito-Lay from an OLTP retail sales reporting system (COBOL, JCL, DB2, etc.) to Frito-Lay’s first OLAP sales data mart

Designated as prime developer of Informatica mappings, workflows and overall schedule leveraging project management principles

Counseled as Subject Matter Expert in the ETL space, set up direction on and load extraction strategy, use of mapplets and Informatica standards

Conducted performance tuning leveraging pre-sort, set cache size, parallelism, and used partitions types and index

Conceptualized and introduced value added development: dynamic timeframes, generic plan table, validation routines to guarantee accuracy of environment

Created Unix Korne shell scripts validating input dimensional and sales transactional files’ presence and records length

Defined the job schedule using IBM ESP tools

Created and maintained Business Objects universe and routinely imported LOVs and implemented Aggregate Awareness for new aggregation tables

Addressed any go-live issues and post implementation support and enhancements including the creation of a data validation process, ongoing performance tuning, and capacity planning

Maintained, supported, and sunset legacy reporting system

Year End (2001 to 2013)

Identified Year-End processing requirements and modified, where applicable, all Informatica mappings, parameter files, and workflows, Unix scripts, Stored Procedures, etc. to ensure accurate information

Reviewed and tested of all potential impacted Timeframes

Informatica Upgrades

Upgraded migration of Informatica from version v5.1 to v6.2, v8.1, v9.1, and v9.5.

Tested mappings for potential incompatibility: upgrades included modifying Unix scripts, testing, documentation, performance bench markings, and tracking of issues

Technical Environment: INFORMATICA PowerCenter 9.6/9.5/9.1/8.1/6.2/5.1, Business Objects XI3/XIR2/5.1/IDT, COGNOS 10.2.0,

ORACLE 9i/10G/11G, UNIX, IBM AIX5.1, TOAD 9.7, Control-M Scheduler, TSO/ESP Scheduler,

HP Service Manager 7, M/S Visio

TECHNICAL SKILLS

ETL Development

Designed and developed ETL processes and solutions

Translated functional design into detailed specification and created and maintained Source to Target Mapping documents (STM)

Constructed, tested, and implemented Informatica mappings, SQL scripts, and job workflows

Captured and documented the ETL rules and the corresponding test cases

Documented all test cases and corresponding test requirements

Provided test results/feedback to assist in optimizing the ETL process

ETL Innovations and Improvements

Established standards, created naming conventions, and best practices for Informatica

Attended monthly Architecture Review Board meetings to provide insight and review of ETL strategy and mapping quality

Contributed innovative ideas, created and developed many Informatica reusable Mapplets, Stored Procedures and Functions and shared with BI team

Design and Modelling

Normalized or De-Normalized dimensional tables as best suited for data mart environments

Utilized data modelling dimensional and fact tables

Designed and created Cognos Framework Manager dimensional models and published packages

Designed and created Business Objects Universes

Scheduling

Created and maintained job schedules using Control-M, Informatica Workflows, and IBM/MVS ESP schedulers

Created triggers and file watchers processes

Performance Tuning

Advanced enhancement and performance tuning of mappings/workflows resulting in over 30%- 50% processing time saved

Assisted with SQL tuning and optimization activities related to each use/test case

Re-organized weekend ESP proc schedule to improve loading and performance every couple of years resulting in weekend jobs completes, 3-4 hours ahead of time

Conducted benchmark performance testing for all scenarios

User Requirements Gathering and Specification

Led and conducted meetings with user groups to collect and analyze user requirements and ensure they are accurately communicated and signed-off

Created User Requirements Specification

Supervising

On boarded and trained Informatica developers and assisted them with repository connections, folders creations, etc.

Trained offshore resource to support Frito-Lay Informatica environment

Provided BI team routinely with technical assistance, juniors and seniors alike including project and team leaders

Baseline Support

Performed on call support 24/7

Adhered to the PepsiCo Support service levels for the Informatica environments and met Helpdesk SLA within 90% response for all supporting systems

Supported Cognos report authors and Business Objects reporting analysts solving complex custom SQL issues through View/Edit SQL Query

Resolved tickets and issues via HP Service Manager and worked closely with users to resolve technical and business issues and provided recommendation

Supported, maintained and enhanced of UNIX scripts

Maintained and supported Business Objects universe

Performed routine Essbase User Administration supporting tasks

Worked with offshore DBA support during off hours

Customized run schedule during periodical maintenance downtime of Informatica

BI Reporting backend SQL query scripting and support

Business Objects

Tableau

Cognos

DDL and DML Scripting and Databases

Constructed store procedures for designer mappings

Created and executed complex SQL queries

Created and tested scripts for Data Quality

Created database objects: tables, views, index, scripts, etc.

Setup testing criteria and plans for Oracle 11G migration including performance testing

SDLC

Agile software development methodology

Kimball DW/BI Lifecycle Methodology

LEGACY IBM MAINFRAME SYSTEMS EXPERIENCE

City of Toronto 1999 – 2000

Senior Programmer/Analyst

Led section of mainframe applications systems and data elements collected for Y2K project

Testing (Unit tests, System and UAT tests, Regression test, Millennium test, Leap year test and Rollover test)

Technical Environment: TSO/ISPF, COBOL, DB2, VSAM, CLIST, JCL

The Dominion of Canada General Insurance (Travelers Canada) 1997 – 1998

Programmer/Analyst

Supported and enhanced Auto and Habitation insurance rating modules of Policy Management Systems software package

Technical Environment: TSO/ISPF, COBOL, VSAM, JCL, Easytrieve Plus, File-Aid

CONFERENCES, COMMITTEES AND NETWORKS

Informatica World conferences: Las Vegas, 2008; Orlando, 2007; San Francisco, 2006

Informatica User Group regional quarterly meetings, Toronto

Oracle User Group meetings, Toronto

Ralph Kimball’s Data Warehouse Conference in Toronto, 2006

Joint Health and Safety Committee, 2011, PepsiCo Foods Canada

Manager Quality Team (founding member, 2008), PepsiCo Foods Canada

Asian Employee Network (founding member, 2006), PepsiCo Foods Canada

TRAINING AND EDUCATION

Training:

INFORMATICA PowerCenter 6 – Advanced Developer

Business Objects XIR2 Universe Design

Tableau Training: Basic and Advanced

ORACLE 9i: Program with PL/SQL

ORACLE 9i: SQL Tuning Workshop

Data Warehouse Database Design

Relational Database Data Modeling and architecture

UNIX: Shell Programming

Unix for Power Users (Unix Scripting)

Advanced Korne Shell (Unix Scripting)

Requirements and Process Modeling

Java Programming

Big Data and Hadoop

Education:

Bachelor of Science (Honours), University of Birmingham, U.K.

Contact this candidate