Oracle ETL Showcase
GE Medical
As an Informatica Developer for GE, AGI designed, developed, and deployed Informatica mappings and associated functionality in an Item Costing and Product Inventory Data Mart. The environment at GE consisted of a blend of many Data Marts, which made-up the Data Warehouse. The warehouse employed highly complex data transformations including Slowly Changing Dimensions and a series of Stored Procedures, which made performance tuning and efficient mapping highly critical. Along with designing mappings from scratch, AGI re-wrote existing code to enhance performance and trouble-shoot errors in both Oracle8I and Informatica.
Motorola University
Designing and developing Informatica mappings for a Motorola University Data warehouse project. Created on data model. Creates update strategies for slowly changing dimensions.Creates UNIX shell scripts for batches.Converting data using PL/SQL stored procedures and functions through Informatica transformations.Using Brio as a OLAP tool to produce reports.
SC Johnson
Here is a brief synopsis of the project here at SCJ. It is an ETL project concerning a DataMart that was created from a DataWarehouse. We are using Ascential DataStage v3.6.3r2 on an AIX Unix server v4.3.3.0. The ETL tool connects to IBM DB2 v6 and SQL Server v7 databases. I have also been creating some Unix scripts written in Bourne Shell to send files and to automatically send email notifications.
ComEd
Electric Utility Company, Chicago, IL
Oracle and Informatica ETL Developer/Analyst
- Responsible for data integration/migration from various legacy systems into a Data Warehouse environment.
- As member of Data Warehouse development team, worked on the project to restructure the electric utility company into the retail market. Assisted the Business users in defining their requirements for the energy de-regulation process.
- Designed, developed and documented multiple interfaces using the Informatica Power Mart ETL tool. Created Requirement Definitions, Object designs and Process Flows in conjunction with Business Users.
- Mapped the requirements and process flows to create Technical Design Specs for the developers. Developed complex Informatica mappings to build key interfaces of the project, using Oracle as the target database in the Data Warehouse.
- Performance Tuning of Informatica data warehouse loads, including Informatica Server Manager and PL/SQL procedures.
- Developed various UNIX shell scripts and PL/SQL scripts to assist in test validation and preparation.
Identified bugs and suggested improvements for existing interfaces and processes.
Ameritech New Media -
Cable TV Division, Chicago, IL
Key team member in Oracle Data Warehousing effort. Increasing responsibilities and involvement in data deployment OLAP project for Marketing, Finance and Human Resources.
- Responsible for developing all data extracts and transformation programs using PL/SQL stored procedures, packages and functions. Responsible for testing and implementing new releases.
- Heavily involved in the development methodology, database design, data modeling, audit and process control including feeder systems, stage, fact and dimension tables.
- Responsibilities included managing the data warehouse production environment, job scheduling and dependency strategies as well as recovery procedures.
- Key team member in the migration of the data warehouse to a new server. Assisted DBA on database design and administration. Modified over 100 shell programs in the process of migrating them to a new server consisting of a reengineered Oracle database for the Data Warehouse.
- Project includes over 70 PL/SQL Packages and procedures.
Environment: Oracle 7.2.2/7.3.3, SQL*Plus 3.2/3.3, PL/SQL 2.2/2.3, HOLOS 4.0/6.0, Korn Shell, Business Objects 3.1/4.0 SQL Navigator Pro.
Hardware: Sun Sparc 1000 Solaris 2.4, Sun Ultra 4000 Solaris 2.6
United Stationers -
Office Supply Whlslrs., Des Plaines, IL
July to Oct 1999
Member of 4 person team in Data Warehousing planning and prototyping effort. Participated in JAD and JRP sessions and subsequent Data Modeling and Star Schemas. Helped to deliver complete system documentation, tools analysis, Erwin diagrams and three production ready cubes using Cognos. Responsible for ETL work from legacy mainframe data using Informatica and PL/SQL.