Keith Klotzle

Lead ETL Developer for Business Intelligence team in support of the Enterprise Data Warehouse. Projects included object conversion from Informatica PowerCenter to Cloud (IDMC), integrating PowerCenter workflows with Microstrategy cubes, and analyzing, documenting, and then restructuring the entire nightly EDW load to improve performance.

Enterprise-wide data warehouse sourcing multiple legacy data warehouse platforms and OLTP databases including current and historical personal auto and homeowner policies. Roles & Responsibilities: Perform administration of all ETL tools including Informatica, Talend, and Data Integrator. Create Center of Excellence for ETL applications including best practices documentation and procedures. Perform all ETL code migrations including coordination of database and AIX shell script changes. Manage development teams providing guidance, mentorship, and evaluations. Determine resource availability and team/project assignments. Ensure all ETL load SLA's are met on a daily, weekly, and monthly basis. Implement Informatica Web Services Hub for processing all commercial auto new business via web service requests. Design and implement delta load strategy from OLTP source systems to ODS and ODS to EDW. Design and implement QA/UAT EDW environment. Convert ETL loads in all environments from DB2 to Netezza databases. Design and implement Audit strategy for ODS and EDW loads Upgrade Informatica and Talend applications regularly. Determine and, in most cases, design and resolve all ETL performance issues. Work closely with DBA's and Reporting application leads to improve performance and identify any issues. Responsible for using various error handling techniques to avoid duplication of rows and/or missing rows. Monitor the ODS and EDW loads and assume role of highest level of production support/problem resolution. Encourage use of newer technologies and tool cross-training for all developers. Provide training material and/or documentation for introduction to ETL tools. Environment: Informatica PowerCenter 8.6.0, Oracle 10g, DB2, Netezza, AIX, UNIX, Linux, Microstrategy, Data Integrator

Maintenance of global financial planning and reporting data warehouse and implementation of new OBIEE solution to replace. Develop subject matter expertise of the Repository Project Design objects in OBIEE. Design, build, test, implement and maintain any new or modified RPD and Datastage objects. Prepare and maintain technical documentation for all new or modified RPD or Datastage objects Prepare Code Migration requests using automated code migration tool. Participate in design of new data warehouse tables as needed. Interact with business units to understand their needs and apply to RPD objects Participate in performance testing and tuning. Develop expertise in BI Publisher and support end user's efforts to use this reporting tool. Provide internal consulting assistance to the report writing teams. Participate in the implementation of any OBIEE product upgrades. Environment: OBIEE, Oracle 10g, DataStage, Siebel Analytics

Enterprise-wide data warehouse for financial and property data sourcing multiple OLTP databases and receiving flat file historical and delta feeds. Designed and built ETL infrastructure in Informatica 7.1/8.1 for enterprise wide Oracle 9/10 data warehouse. Supervised ETL team in construction and testing of over 200 Informatica mappings extracting and loading 2 TB of data for 10 subsidiary companies. Designed and built load process consisting of 20 Informatica workflows which met all nightly SLA's consistently. Designed and implemented strategies for extensive mapping, session, and worklet re-use in all Informatica development. Assisted in design of ODS and Data Warehouse financial and order processing data models. Implemented and documented Informatica best practices for all First American Companies including sample mapping and workflow templates and mapping logic guidelines. Design and implement Audit strategy for ODS and EDW loads Planned, tested, and executed major Informatica upgrade from version 7 to 8. Determined and resolved ETL performance issues. Work closely with DBA's and Reporting application leads to improve performance and identify any issues. Responsible for using various error handling techniques to avoid duplication of rows and/or missing rows. Monitor the ODS and EDW loads and assumed role of production support/problem resolution at all levels. Environment: Informatica 8.1, Oracle 10g, 9i, SQL Server 2003, UNIX shell scripts (Sun/Solaris), Microstrategy

Maintenance and new development of Accounts Payable, Purchase Order, and Research Informatics data warehouses and data delivery. Assisted in development, testing, implementation, and documentation of Accounts Payable and Purchase Order systems being transitioned to Oracle 8.1 data warehouse via Informatica 6.1. Responsible for maintaining Oracle SQL views for downstream business units and data warehouse users. Assisted in the analysis and development of new Research Informatics data warehouse. Built source to target matrices for new Informatica development and led mapping development. Environment: Informatica 6.1, Oracle 8.1, Business Objects

Development of accounting data warehouse for North American operations including 19 subsidiary companies. Created Informatica process to convert and load all accounting activity including conversion of 17 different code block elements and summing at the 6 digit and 9 digit account levels. Implemented ETL strategy for loading accounting processes and business rules including allocation, elimination, and consolidation of all accounts. Implemented strategy for code re-use for subsidiary companies using Informatica mapplet and worklet objects. Worked with Informatica consultants to implement Informatica best practices and all recommended standards and methodologies. Environment: Informatica 6.1, AIX/UNIX scripts, DB2

Rewrite of legacy sales and marketing data warehouse load and Informatica application upgrade. Analysis and documentation of legacy ETL process for sales and marketing data warehouse written in MicroFocus COBOL pulling data from mainframe VANTAGE and DSS systems in VSAM file formats. Redesigned, built, and implemented with Informatica, PL/SQL, and UNIX scripts. Built all specifications and documentation for system. Implemented audit, backup, recovery, and user notification processes. Designed and built ETL process for loading Mutual Fund data into the data warehouse. Upgraded 'model office' and production environments from Informatica Powermart version 4.5 to version 4.7 providing all testing documentation and procedures. Environment: Informatica 4.5/7, Oracle, AIX/UNIX scripts, IBM Mainframe/VSAM files, Microstrategy

Built ETL process loading data warehouse for retail clothing distributor. Constructed and tested 100+ Informatica mappings loading staging and ODS tables and data marts in Oracle. Assisted in designing ODS and data warehouse load process consisting of 100+ Informatica sessions and batches. Sources and targets were both flat file and relational. Designed and built ETL process loading data marts for home entertainment sales subsidiary of film corporation. Data warehouse consisted of 15 dimension and 5 fact tables being loaded by 25 Informatica mappings. Load process driven by users submitting files via the internet kicking off UNIX scripts calling Informatica sessions loading an Oracle database. Source file formats included delimited files of different formats. Built ETL process for Insurance firm converting mainframe VSAM files and loading an Oracle database. Sources included complex COBOL copybook structures (e.g. multiple level OCCURS clauses, REDEFINES clauses, etc.). Installed and upgraded various versions of Informatica PowerCenter and PowerMart for many clients on both NT and UNIX platforms. Worked with a variety of technologies in a diverse number of environments.

Constructed ETL processes to load insurance fund data into Oracle Hierarchical Data Store using MicroFocus COBOL. Constructed ETL loading ODS and data marts in Oracle for mutual funds data using MicroFocus COBOL. Tables were loaded with data extracted from mainframe VSAM files. Constructed UNIX jobs and scripts to load these data marts on a daily basis. Built user interface in VisualBasic for the purpose of adding, deleting, or modifying Brokers and Dealers and all their accompanying transactions in the system.

Worked on two different projects for SSI at Franklin Templeton and Pacific Life. Developed data warehouse ETL in MicroFocus COBOL on both HP and SGI hardware on a UNIX platform.

Gathered requirements from managers of Loan Processing system for purpose of building a new online system. Built process and program specifications detailing online and batch processes involved in the system. Designed user interfaces using PowerBuilder. Held meetings with PowerBuilder developers to discuss detail of processing and design of new interfaces.

Outsourced at Vons and joined company. Worked as consultant for Systemhouse at Transamerica in 1998.

Responsible for maintenance and development of all COBOL/JCL programs in the Store/Database Interface system. System calculates and transfers all pricing and product data from the mainframe to company's 340 stores nightly. Responsible for maintenance and development of all batch programs in Price Maintenance system (PMS). System reports store and pricing data to marketing department directors. Responsible for maintenance and development of all online (CICS) and batch programs in the Distribution/Allocation system. System distributes and allocates product to stores based on special requests from store's management. Built reporting system tracking sales of all produce items by store. Sales data was reported via CICS and hardcopy. Built system transferring store and pricing data over the internet to be posted on home shopping website. Environment: IBM Mainframe/VSAM files, CICS