lastminute.com

MIS manager – lastminute.com – October 2002 – April 2004

I started working at lastminute.com on an initial 3 month contract to help out with certain technical problems facing the MIS department. These were mostly concerning the retrieval and parsing of search logs generated by the retail site into a SQL server database. It quickly became apparent that there were also several other areas that required my expertise, and I was offered an extension to this contract shortly before christmas.

During this contract extension I totally re-wrote most of the backoffice systems that MIS used, and generated new sets of reports based on the enhanced backend using Business Objects – all this time gathering feedback from the board as well as business users.

Seeing the immediate effect of the enhanced reports on the business prompted the board to invest more money in the MIS department, conseqently resulting in an increase in staffing within the MIS department. At this point I took the role of technical architect and proceeded to migrate the systems from a SQL Server/DTS(Data transformation services) system to a more reliable Oracle/Perl extraction system. This system was slow in implementation due to lack of co-operation from the business and the difficulty of retrieving data from some of the e-commerce backend databases.

Unfortunately, due to budgetry constraints, a set of resignations and the slow speed of development it was decided that the MIS department was to close – and the entire department was informed that they were made redundent shortly after easter.

Thankfully, due to my close relationship with many business users I took on another project within the company – one which involved extraction of data from the various CRM systems(Genesis call-center switches, Kana e-mail managment systems and many more) using Perl into an aggregate Oracle database with Business Objects as the reporting frontend. During this time it was clear that an MIS function was required within the business and after discussions with the CTO and CEO it was decided that I would dedicate a small part of my day maintaining the existing MIS systems and releasing the UK and global daily reports.

Increasing demand from the business for MIS data resulted in a new MIS department being formed, and I was conseqentely asked to help out with this new department. I offered to lead this new department in early July – an offer that was taken up by the business quickly.

With 2 staff(one programmer and one accountant) we took up the challenge to re-build the systems and over the next 2 months, and within 1 month we were back to full capacity, supplying the business with better, quicker reports and increased data quality. Amongst the report requests from users I took it upon myself to build a new kind of reporting system that would give the business immediate access to real-time data – a system that was built in a little over 2 weeks and was accepted with many good remarks.

Over the coming months a set of backend changes within the e-commerce engine as well as the need for more accurate data from our european subsiduaries required a new MIS finance backend to be built. Since money was tight, I decided to move all of our systems to a Postgres database(from Oracle) and after 3 months a transactional level, inter european database was built and delivered on January 1st 2004. To help out with the implementation 2 more staff were recruited, bringing the team size to 4.

The time between January and April was spent enhancing this new system, and building reports based on it – I decided to leave in late February since I felt that the MIS department was in a state where it no longer required my expertise.

Technology used

Perl

  • Data extraction and cleansing from flat files generated by the search backends
  • Automated feeding of data from one database/data source to another(ETL) – from over 30 different failure prone sources daily by 9am
  • Generation of HTML for realtime frontend – providing details of all transactions made and advanced analytical metrics refreshed every 2 minutes
  • Building data entry tools for european subsiduaries and some UK affiliates
  • Scheduling extraction through interdependent critical paths
  • Generation of scheduled e-mail reports with natural language data analysis
  • Generation of Excel spreadsheets detailing partner transactions

Business Objects

  • Reporting interface to the business – many reports built utilising drill-down, alerters and BOScript
  • BCA used to schedule certain longer running yet frequently required reports

Postgres

  • Backend for search DB – over 400,000 records populated on a daily basis resulting in a DB of over 40GB in size but still returning reports within minutes thanks to partitioning, materialised views and indexing.
  • Backend for MIS financial systems(Dec ’03 thru present)
  • Backend for interdependent critical path system

Linux

  • Highly customised application configuration to allow swift query response and database interconnects
  • Customised Apache configuration(including ‘home-brew’ apache module creation)
  • General disk/user/backup management

Oracle

  • Backend for MIS financial systems(Feb ’03 thru Dec’03)
  • Backend for CRM backoffice system
  • Some systems that were interfaced with used Oracle – hence Oracle syntax/SPs used in ETL.

MySQL

  • Backend for real-time reporting system – used instead of Postgres due to speed of queries on small datasets

SQL Server

  • Backend for original MIS finance and search backend
  • DTS(Data transformation services) used for original ETL into backend

Informix

  • lastminute e-commerce backend/CMS all based on Informix – hence Informix SQL syntax used in ETL