Name: Lennart Prins
Title: Head of Business Information
Company: Dubai Airports
Overview of Use Case
Dubai Airports manages the operation and development of both of Dubai’s airports – Dubai International (DXB) and Dubai World Central (DWC). DXB receives 100 million passengers annually, and DWC 27 million passengers with plans to surpass DXB and become much bigger.
Using Alteryx they were able to help different departments migrate from tedious spreadsheets and custom code to automated solutions and predictive analysis. It saved everyone’s time and it made the analysts’ jobs more interesting. Now Dubai Airports can create financial reports, sentiment analysis and automatically prepare input and output data for their simulation software in a matter of minutes.
Describe the business challenge or problem you needed to solve
“What we did in the past was built as many concourses as we could in the land we had, but now DXB’s land is full. So, we need to become a lot smarter in the way we manage the operations, so data has become more and more important”, said Lennart Prins, Head of Business Information at Dubai Airports.
Dubai Airports runs 1,500 passenger surveys consisting of 32 questions every quarter. The corporate office would get a hard copy of the results and compare them to the results from other airports. The reports consisted of 150 pages with bar charts, rankings, a CD-ROM with Excel sheets and source data. “It was a nightmare, nobody ever looked at it. Every time I got a question from the CEO it would take me 3 days to go through all those Excel sheets again,” Lennart said.
Their Finance team had 4 people spending one week every month updating 160 Excel tabs, creating reports and emailing them to people. Lennart himself would spend hours reconciling difference Excel sheets to put together a monthly report for the senior leadership. They were spending 4 weeks to prototype one dashboard. They wanted a better way to automate dashboards and to update them easily. From there they wanted to establish a self-service analytics platform so analysts didn’t have to depend on IT.
Another challenge, was that some analysts who have been there for 15 years were concerned that their job could be automated. Lennart explained, however, that “you will not lose your job, you will be busier. Your job will change, it will become more interesting, and it won’t disappear.” It took them 6 months to gradually move to automation. “Our finance colleagues said that now they are getting more questions and better questions”, added Lennart.
Describe your working solution
Dubai Airports first started out by building a data structure in SQL. Then, they purchased more Tableau licenses, and their first Designer license to speed up the process. At the moment they use Tableau and Splunk for visualization tools. They use Alteryx for prototyping while developing dashboards and data structures. Also, they use it for enhancing the capabilities of other BI tools, standalone applications, and workflows for advanced use cases.
More and more data became available. Their IT team set up two SQL Servers. Lennart said “whenever you mention database or SQL everybody would see that as pure IT job, but we wanted to control that process because that allows us to quickly turn around analysis and new dashboards”. Once that was completed, they opened it up to any analyst that wanted access, and the number of uses cases escalated.
The initial dashboard they created for the passenger feedback data would only show the scores that people gave and ignored written comments. Someone in that department was spending 4 days every month, reading every comment and classifying it as positive or negative. “She is a lot more valuable to our team than that. As soon as we found out we stopped that and found a better way,” Lennart said.
They used the data available in SQL, Alteryx Designer and Google APIs to build their own sentimental analysis model. They created a workflow that pulls the data from SQL, puts in the Google API, and the sentiment analysis score comes back. This is something that took 4 days every month and that they were about to cancel completely because it wasn’t worth the effort. It now takes 5 minutes.
Queue Reporting and Analysis
Dubai Airports has sensors in the ceiling of the transfer security areas to check how many passengers are in the queue, how long they are waiting, and how many passengers are being processed every 15 seconds. Lennart and his team put all that data that the sensors collected into dashboards to show what the monthly average processing time is.
With Alteryx Designer their team prototyped the data structure to get quickly to dashboards when the data becomes available. Another source of data are the forms that the Terminal Duty team fill out after every shift with comments of what went well, if there was a queue what the issue was, etc. Lennart’s goal was to combine that in the dashboard to find out the reason for the wait: is it because the lanes were opened too late? Is it because there wasn’t enough resources? Or is it because they just had so many passengers in that area that even with all the manpower in the world they wouldn’t be able to process them?
They merged the data from the sensors and the forms on SQL and it took them 3 days to write the code and one hour to run it. It wasn’t feasible. With an Alteryx workflow they did the same job: it took 30 minutes to build and it runs in 40 seconds.
Flight Schedule Converter
Dubai Airports’ analysts used to only have frequency based flight schedules that didn’t show exactly how many of which flights occurred on which days of the week. It was a difficult form of data to process. “We didn’t have any tools. I wrote some Java code to process the data we had and it took a week and a half just to create a simple application,” Lennart explained.
He created the flight schedule converter app 8 years ago, and when he got requests to update it, he realized that he had no idea where the Java code was. “Even if I have the code there was no way I was going to spend a week updating it. So I decided to build a similar application on Alteryx, and it took 20 minutes to build. That’s when I realized that I should never touch Java code anymore,” he said.
Airtop Simulation Input
The Airtop Simulation is used to simulate the capacity of the airspace and the runways. The input file is a CSV file which always uses the same columns and same type of data. However, the data can be in various formats, for example, if you want to simulate the future flight schedules that are 20 years out, if you want to simulate something in the next season, or if you want to simulate a flight schedule from last month.
To get to the same format of the input file it requires some Excel processing depending on what the original input was. The analysts that were doing that were spending 2 days to create the input file for the simulation. Then, they would run the simulation, and only then their analysis would start.
“We build a single application in Alteryx, a lot of the data needed for the simulation is available now in the SQL Server that we set up. Now it’s automated and we wait a few minutes to run. We got rid of those 2 days that are now time spent doing analysis”, Lennart explained.
Cast Simulation Output
Dubai Airports also simulates terminal capacity and the flow of passengers through the terminals. The output files came in several XML files, with a lot of VBA code that was locked and nobody had the password to update it.
Another big issue is that it wasn’t set up to run and process data around midnight. In Europe, airports are concerned with noise because they are near dense cities, so they don’t have many flights at that time. However, in Dubai midnight is the busiest time at the airport. So their team had to adjust the simulation to make it fit VBA processing. Also, it would take up to 40 minutes to load the data in Excel and hours to process and create the charts.
“So I just build an Alteryx workflow. It can read XML files, so it can process the data and spit it out in the format that we like. That gives the option for the team to store the data in SQL immediately and export in an Excel sheet. That way they can publish data straight to the Tableau Server to create charts that are updated automatically”, Lennart commented.
Passenger Demand Prediction
Dubai Airports could already measure where passengers were and where the queues were, but Lennart also wanted to be able to predict. “We would like to know that half an hour or one hour in advance so if there’s a large group of passengers coming unexpectedly we can make sure the lanes are open and nobody has to wait,” he said.
In the workflow below they took all the data available about flight arrival time, volume of passengers, information and assumptions of walking distance, disembarkation profile of passengers, and build a model to predict what would happen. In the chart below you see the comparison of what the sensors are saying and what the demand predication workflow is saying based on the data available.
“We are confident that the assumptions and logic that we applied are good. We just purchased an Alteryx Server license and this is one of the first things we want to publish. We want to test how accurate the model is 1 hour in advance compared with our most accurate data,” Lennart concluded.
Describe the benefits you have achieved
With Alteryx, Lennart and his team are able to build specific applications to niche use cases, and they don’t have to depend on IT anymore. He said that “our workflows are becoming quite advanced. Our use cases are becoming bigger and bigger. I think we will uncover uses cases in a weekly base almost.”
“Everyone in the company wants an Alteryx license now, even to do the simplest things. I get requests all the time and I was checking case by case to see if it was really necessary. So, now we have an IT team that manages these products. The entire company has evolved around it,” he said.
“With Alteryx I uncover new uses and projects every week! When I use Alteryx, I feel like Tom Cruise in Minority Report!” Lennart explained.