Client Solutions/O2 (Dublin, Ireland)
April 2005-August 2005
The Customer Enterprise Management team of O2 Ireland designs and develops the data warehouse systems for the Business Intelligence team. I was taken on to perform database design and development tasks as part of a major new campaign management implementation project.
Design and develop database structures to store a history of transactional data for later analysis.
Design and develop extraction, transformation and load (ETL) processes to populate the data warehouse with data from disparate sources.
Design and develop UNIX shell scripts to support the ETL infrastructure.
To provide defect analysis and resolution services to the data warehouse system.
To contribute to the overall technical development of the team with respect to software engineering processes, advice in shell script development, proposals for process improvement, etc.
HP-UX; Windows 2000; cygwin
GNU emacs for SQL and PL/SQL development; Oracle Forms 6i builder
ksh (UNIX scripting); SQL; PL/SQL; SQL*Loader control files;
UNIX scripting: Not actually a project, I became the point-of-contact for all issues and advice regarding the development of UNIX shell scripts. During this contract I developed a set of UNIX shell script guidelines.
USSD Data part I: To fix the legacy USSD Data system. This was a ksh and PL/SQL system that was failing due to some bugs. While not part of the main CRM project, until the alternative USSD Data system was in place, it needed to be fixed. This involved improving stability, improving logging and error reporting and implementing archiving of the data files. For this project, I
Analysed the system and proposed and implemented the fixes.
Documented the system for future reference.
USSD Data part II: To re-implement the legacy USSD system using the standard technologies: Oracle 9i database fed by Ascential DataStage. The main project of my time there, due to the differences in architecture and system design approach, this was in effect a new project to take the log-file data generated by the mobile ‘phone SMS service activity and to place it in the data warehouse for later analysis. For this project, I
Designed the new data structure for use with an Oracle 9i database.
Designed the ETL process for use with Ascential DataStage.
Implemented the new system.
Documentation: To document the ETL processes in place for reference purposes. The Business Intelligence data warehouse had many tables and many times more ETL processes feeding those tables. I was tasked with analysing these to document the source data and transformation performed on them for each column in each table.
ETL refactoring: To refactor an ETL process in PL/SQL for the purposes of improved performance. The original job takes up to 8 hours to run – to query data from the billing system, to transform them and to place them into the data warehouse. The project isn’t finished yet. For this project, I
Analysed the ETL process in order to determine what it does.
Designed a PL/SQL package to reimplement the job.
Developed the code.