Optimizing ETL Processes at a Major Global Airline

ETL (extract, transform, load) is the three-part mantra of thousands of data migration experts around the world. By smoothly transferring your enterprise data from source locations to the target data warehouse, ETL is one of the most important pieces of the data puzzle.

 
Yet despite ETL being essential to data processing, far too many organizations fall prey to ETL problems that prevent them from reaching peak efficiency.

Datavail recently partnered with a Fortune 500 airline company that was experiencing a variety of ETL design and performance issues. To read all the details about how Datavail improved the client’s IT environment, check out our new case study: “Major Global Airline Lowers Costs and Maximizes Data Insights with Datavail Partnership.”

Just looking for a few pieces of helpful advice? Keep reading: we’ll give you our top tips and suggestions for optimizing ETL processes.

How to Fine-Tune ETL Processes

The airline in this case study was running ETL jobs for excessively long periods of time—up to 14 hours and even greater. As a result, they had to cut down on the number of database records they used in their ETL workflows, which prevented them from discovering valuable business insights.

With careful fine-tuning of your ETL processes, you can avoid this fate for your own organization. Datavail helped the client redesign their ETL jobs, changing their execution pattern from serial to parallel. By chunking data into multiple parallel sessions, our client was able to massively improve ETL processing times.

Parallel processing is just one way that you can optimize ETL. Other suggestions include:

• Caching the data that you use most frequently in order to speed up access times.
• Analyzing your ETL processes by their time and resource usage in order to find the biggest bottlenecks and inefficiencies.
• Loading data incrementally, i.e. loading only the records that have been created or updated since the previous load.

 
One particular improvement that Datavail made to the client’s ETL processing workflow: moving the majority of data processing to their Informatica server, thereby reducing the pressure on the client’s database. For more details about how we accomplished this feat, check out the full story in our case study.

Another suggestion for fine-tuning your ETL processes? Upgrade or change your database software. Here at Datavail, we’ve done enough database upgrades and migrations to know how dreaded and laborious they can be—but they’re a necessary evil to guarantee cutting-edge features and performance.

In our airline case study, the client was using a version of Oracle Database 12c without advanced performance tuning features. The client also lacked access to techniques such as table partitions, bitmap indexes, and columnstore indexes, which significantly hampered its ETL efficiency.

Finally, you can optimize ETL jobs by removing as much manual work from the equation as possible. Before we showed up, the client in our case study was using a convoluted method of data aggregation: 20 different company divisions needed to send Excel spreadsheets back and forth before manually entering the data into Oracle Business Intelligence Enterprise Edition (OBIEE). It goes without saying that this is far from the most efficient way to do things.

Conclusion

Optimizing your ETL processes is easier said than done, but it’s also easier than you might think. By joining forces with a knowledgeable data integration partner, you can implement best practices for ETL and database design. Find out how we helped one client in our case study “Major Global Airline Lowers Costs and Maximizes Data Insights with Datavail Partnership.”