Alpha LSG
BI Development
On-site
Alpha LSG Sky Chefs operate from all major UK airports, supplying in-flight food, drink and retail products and aircraft cleaning. They have numerous data systems to manage and report on these operations but they lacked a consolidated view of all systems and a way to link customers between different systems. Their existing data warehouse solution had experienced a large amount of data loss, incorrectly processed data and general system failures and they did not know why or where the issues occurred.
Lucid Support Services Limited (agency) contacted me on behalf of the Alpha Group in January 2012 regarding an initial audit of their existing solution. Following a week-long investigation, I presented them with comprehensive documentation on where failures were occurring and how to amend the situation which led them to request that I redesign and rebuild their data warehouse solution. I worked with their permanent in-house resource to provided training on how to build and manage the new system.
I used Kimball Data Warehouse technology with SQL Server for storage, Integration Services (SSIS) for ETL, Analysis Services (SSAS) for the data cube and Excel connected to the cube for reporting (SQL Server Reporting Services was considered too restrictive).
All on-site work was undertaken from their Manchester Bond which is a secure area within the Manchester Airport and therefore required that I undergo vigorous security screening and sign non-disclosure agreements.
Key achievements
- The principal issues of the original system were; lack of error handling, consistency checking, auditing and process reporting resulting in data issues and subsequent lack of consumer confidence. The replacement system solved these issues resulting in a very low maintenance and high-reliability data warehouse solution.
- By implementing conformed dimensions, I provided Alpha with the ability to quickly ascertain the cost / revenue balance for their customers across multiple systems without having the perform manual operations (in Excel or on paper) as they had done previously.
- With many systems, operating across many UK airports and each system holding its data in different formats including IBM Informix, SQL Server and proprietary data formats, ETL operations were varied and complex. Comprehensive and automated auditing, data cleansing, consistency checks and issue alerting now ensure data integrity and provide Alpha LSG with confidence in the data.
- Extensive technical documentation was produced to assist with on-going development and maintenance of the system.
- I implemented a new source control system.
- I reconfigured existing hardware systems to make better use of existing licences and later moved the solution over to Alpha LSGs new IT infrastructure provider.