Projects:

Sales Edit - Coca-Cola Enterprises:
Part of development team tasked with adding enhancements to the Sales Edit application for processing incoming bottler data. The Sales Edit processes daily incoming bottler flat-files after first verifying file layout. Data is then collected into staging tables via MultiLoad. From there invoice headers and lines move separately via BTEQ through various edit and suspense steps after assigning synthetic keys. Invoices ultimately get loaded into corporate tables for reporting and analysis.

Food Service Edit - Coca-Cola Enterprises:
Implemented a multi-step BTEQ solution for taking Food Service specific data from the Sales Edit application and processing these records through various edit and suspense steps. The final step produced flat files to client specifications for loading into multiple external systems.

Mid Market Project - BellSouth Corp:
Mid-Market is a very large combination data warehouse / data mart that seeks to integrate a number of disparate monthly data feeds from different affiliates within Bellsouth. The purpose of the warehouse is to cleanse and standardize all data coming into the warehouse using synthetic keys with the goal of retaining the distinct hierarchical nature of the original source data. The purpose of the Mid-Market data warehouse team is to provide an ongoing series of system upgrades that continually enhance management's ability to monitor and maximize the marketing efforts of the BellSouth Account Executives.

Mid Market is part Oracle data mart, and part Teradata data warehouse. Each release involves enhancements to both sides of the fence (Oracle & Teradata) since they are highly integrated and interdependent via a series of scripted ETL ETL (Extract-Transform-Load)
is the process of extracting data from the operational data store or external source and transforming the data (e.g., cleansing, aggregating, summarizing, or integrating) and loading the data into the data warehouse. processes.

Customer Profitability & Reporting (CPR) Project - BellSouth Corp:
CPR is also a Teradata/Oracle project utilizing many different scripted ETL processes for the purpose of extracting, transforming, and loading data into different marts that provide a profitability reporting environment for the Finance group.

On CPR I also have the responsibility of maintaining a one-of-a-kind application which I designed and developed which enables an end-user to "push" data to the mart direct from their desktop via this custom Access / Visual Basic application.

This new Access-based application utilizes Teradata's MultiLoad & BTEQMultiLoad and BTEQ are command-line Teradata utilities used to batch-load millions of records quickly via UNIX shell scripting. utilities. Hours or days of load time are now reduced to seconds or minutes! Teradata MultiLoad is a very powerful tool, but prior to the development of this MS Access / UNIX solution these utilities could only be used by developers with a strong background in UNIX scripting. This MS Access application invokes MultiLoad and BTEQ via built-in Access modules. VBA within the application controls the entire process even triggering the ETL job to begin on UNIX. The new application performs the following steps saving a great deal of time: 1) export local tables to delimited text files while 2) simultaneously building custom UNIX scripts tailored specifically to the layout of the table being loaded, then 3) automatically FTP those files to the UNIX box, and finally 4) trigger MultiLoad (On UNIX) to run the ETL job. Additionally, it was required that my script-building VBA code automatically handle any last minute structural changes that the user might want to make to any local "user-defined" tables.

The other part to this solution is a series of UNIX scripts which I designed to sit poised to receive any input from the user and execute those commands to load data as needed. The UNIX part of the application then analyzes the success of the current load and reports back via email the status to the end user or anyone else the user has selected in the front end application.

Hover over image for larger view:

Other responsibilities on the CPR project involved client support of these applications as well as various ad hoc reporting.


Contract Wizard to CPR data feed - BellSouth Corp:
Enhanced the CPR (Customer Profitability & Reporting) data mart by adding a new UNIX-based ETL feed from the Contract Wizard data mart. This new feed provided a rolling 36 months of customer contract information including revenue discounts, product and USOC detail. CPR is a Teradata decision-support mart used by management to develop and report key marketing metrics about the BellSouth customer base.

The Contract Wizard group provided us with zipped/tarred data files to our UNIX server. My process uncompresses the archives and loads them to temporary tables. Since we were not in control of the source data files I used temp tables during the load process. (Initially loading to temp tables will prevent corruption of live data should a failure to occur due to an unexpected change in the source archives such as file layout, or data format.) After the data has loaded successfully to the live tables the application then checks for the existence of more than 36 months of data within the target tables. When found, those records (e.g., month 37) are deleted.


For more information please contact me.