'Intelligence Partner has given us the necessary technical knowledge during the most complex processes of the project and has helped us to travel the way from the transactional databases to the Data Warehouse and Tableau.' —Antonio Molina, Systems Director of SLCA.
SLCA was created in 2008 as a joint venture between British Petroleum and Repsol. Since then, it supplies fuel to hundreds of commercial airlines and private clients at the Spanish airports in which it operates. Its mission is to offer its customers a high-quality service, contributing significantly to the smooth functioning of national and international air transport. Our experience accumulated over the years, and especially thanks to the specialised and qualified staff working at SLCA, has allowed the company to be in an excellent position to lead the market in the coming years.
It is, in short, a company with a highly qualified staff, with a great commitment to its customers, where safety and respect for the environment are part of all its operations processes.
Our values? Excellence in service, maximum operational safety, business sustainability and social and environmental responsibility.
‘After 10 years of generating data, in SLCA we had 3 different transactional systems which we wanted to interconnect in order to analyse business information and help control and make business decisions.’ —Antonio Molina, Systems Director of SLCA.
Within the transactional systems available to SLCA, there are already defined metrics and representations which cover the scope of each system. But it was necessary to be able to agglutinate the standardised information of these systems into a single interrelated and exploitable data source (avoid data silos) in order to provide a holistic view of the company.
The first was to create a data culture in the company. For this, Business Intelligence courses were held and the main tools of the market were presented to the management.
‘We selected the technological base (Google Cloud Platform and Tableau Server) because they were already G Suite customers and because we saw that it would provide us with more value than other solutions and at a reasonable cost.’ —Antonio comments. The solution focused on 2 key aspects:
- A cloud architecture, for the advantages which the cloud provides in terms of scalability, performance, reduced administration costs (serverless philosophy) together with payment based on usage. Google was chosen for two reasons: it is the company with the most innovative cloud services in the field of Big Data and Machine Learning, and already had good experience with other Google services.
- From the analytical point of view, a solution was sought which would allow the business analyst of the different functional areas (operations, management control, human resources, etc.) to be self-sufficient when it comes to creating business analyses and discovering information, without depending on the technology area. Here the choice was Tableau Software both for being a leader in the concept of agile Business Intelligence (as stated by Gartner in its annual reports) and in its perfect integration with the Google platform, specifically, for being optimised to work against Google BigQuery as Analytical BBDD.
The current transactional systems may be affected by queries made to their databases, affecting the speed of registration of operations or their operation; this happens because these systems are designed to have a rapid response to relatively small transactions, but they are not geared towards the mass information process for reports with complex analyses which require access to thousands of records.
To solve this issue, a Data Warehouse was created on the Google Cloud infrastructure, with the aim of having an analytical database which would respond to this type of question, including being able to analyse logistical operations which are taking place in multiple airports in real time. The reason for this creation was its great scalability and high security, both for the detailed control of the management of identities and accesses, as well as for its data encryption, at rest and in transit.
By separating the DWH from the current transactional system, queries to it do not affect the performance of the current operational systems. At the same time, the Data Warehouse was built thinking that it had a great capacity to obtain analyses which involve the processing of huge amounts of information in the shortest possible time, and that this will be updated daily obtaining the data of the different current transactional systems.
Once the Data Warehouse was created, a series of reports were made to cover the information needs of the Operations area, with the unified information of its three systems already available.
‘Intelligence Partner has helped us in two aspects: the management of the project and the technique. On the one hand, they helped us to interconnect the ERP, the operations system and the maintenance system, in order to cross-check data and obtain dashboards (by company, department and cost centre) and analyse existing data by projects. And on the other, they have given us the necessary technical knowledge during the most complex processes of the project (extraction, transformation and loading) and to travel the way from the transactional databases to the Data Warehouse and Tableau.’ —concludes Antonio.
Some reports were created with immediate results in the improvement of the operation, for example, thanks to the possibility of exploiting the data with a granularity of minutes, it has allowed the study of the simultaneous use of assets, in this case supply trucks, for optimising their use in the facilities as well as deriving part of the underutilised resources to other new installations.
To improve the control of the efficiency of the facilities and their personnel, reports were prepared which analyse the aggregate data of the average efficiency, calculating the difference between the duration of an operation and its historical average calculated according to the date, installation and the aircraft model.
The next challenge for SLCA is to stabilise Data Warehouse data and be as self-sufficient as possible, in order to be able to draw up its own dashboards, reports, analyses, etc.
ELEMENTS OF THE SOLUTION
As a guideline, the components of the solution which has been launched in the initial phase in SLCA are indicated:
- Data sources – transactional systems hosted in the cloud:
- KEROS application for “Airport operations” solution developed in .Net about DB SQL Microsoft SQL Server.
- Application for financial area (accounting, billing -SAGE 200c).
- Application for “Asset Maintenance” on BBDD Microsoft SQL Server.
- DataWarehouse platform:
- Extraction, transformation and loading of information: Talend as an ETL tool for managing the files stored in Cloud Storage with the information extracted from the data sources and loading it in the final database. The developed processes of loading and data transformation with Talend are managed through the task planner of Google Compute Engine where it is installed.
- BBDD Analytics: Google BigQuery. BigQuery is a columnar analytical database based on Map Reduce which allows unique response times against hundreds of millions or billions of records (up to Petabytes of information) in an “unmanaged” environment with zero administration by the client.
- Analysis tool:
- Tableau Server on Google Compute Engine for the collaboration and sharing of reports and dashboards with the more than 50 expected users.
- Tableau Desktop as a solution for the report creator analyst.