Enterprise-wide Business Intelligence

Enterprise-wide Business Intelligence

TUI-Jetair, embarking on a Performance Management journey

TUI-Jetair has started an exciting journey in maturing their Performance Management environment. In collaboration with element61, a strategic roadmap was defined to allow TUI-Jetair to enable enterprise wide Business Intelligence & Performance Management. This multi-year program includes building an enterprise data warehouse solution that will leverage reporting efforts throughout the business. By integrating multiple types of source systems such as ERP, accounting, complaints and marketing data, a unified view on the customer can be created. This will help the organization to proactively improve the total customer experience.

The Customer

Jetair was founded in 1971 but actually can be traced back to 1956 when founder Gerd Brackx started the first coach tours to Benidorm and became one the first pioneers of the travel sector in Belgium. In 1958 he founded his own company Royal Tours that used coaches to transport travelers to popular destinations in Spain. In the eighties, Jetair diversified its activities and became an all round tour operator. In 1994, Jetair became the largest flight holiday provider in Belgium.

TUI-Jetair activities are divided into a number of brands:

  • Jetair: the well known tour operator brand
  • Jetaircenter: a retail network of 180 travel agencies
  • Jetairfly: the second largest airline in Belgium
  • Jetairfly.com: low-cost tickets

Enterprise-wide Business Intelligence

In 2011, Jetair employed over 1800 people and had revenue in excess of 948 million EUR. More than 2.21 million passengers traveled using Jetairfly that has a fleet of 20 aircraft flying to 87 destinations. Currently Jetair is part of the TUI AG-group, who isquoted on the Frankfurt stock exchange.

The Challenges

Enterprise-wide Business Intelligence

Like most companies, the Business Intelligence and data warehousing environment at TUI-Jetair grew organically. Throughout the years, multiple components were added, forming an incoherent architecture after a few years.

Back in the nineties, the first iteration of a data warehouse was built to accommodate the Tour Operating operational activities. This data warehouse however had several data quality issues, lacked accounting information, was not fully dimensionally modeled and lacked sufficient documentation. As no more updates were implemented, further subject areas could no longer be integrated and the long term sustainability of this data warehouse became questionable. It was therefore clear that the existing Business Intelligence architecture could no longer support all of the growing management information requirements of TUI.

When in 2010 a new operational retail system for Jetaircenter was implemented, the reporting was originally a bit forgotten. There was however a high need for KPIs, analysis and reports for the retail activity. As an initial, short term solution, a Cognos framework on the operational system was built. The framework however was limited in scope and had limited functionality due to the underlying operational data model (Third Normal Form). Reporting directly on top of a production environment had several drawbacks. System resources were drained by running the reports, leaving data entry users waiting to commit their data. The reports had performance issues as well, as Third Normal Form data models are optimized for data entry and not for reporting. Integrating other data in the reports, such as planning or budget information presented insurmountable issues.

On top of other operational systems, such has accounting and HR, cubes were built. As these cubes did not leverage a common data model, the total cost of ownership was increased considerably. Repeating the same logic in several cubes allowed room for discrepancies and jeopardized the single version of the truth paradigm. Running all those cubes overnight, in combination with technical limits imposed on cubes, created a bottleneck.

IBM Cognos Planning was already used for the budgeting and forecasting process. Due to the lack of an enterprise data warehouse, Access was used as an intermediate to accommodate the input of the planning process. As no enterprise data warehouse was available, the output of the planning and budgeting information from IBM Cognos Planning could not easily be leveraged either.

The reporting, mostly based on cubes, was considered to be effort consuming due to a lot of manual processing. The reports were not at the desired grain as cube size & performance limitations prevented in loading the lowest level grain. Reports did not integrate the budget information needed as conforming data was a daunting task.

Having a fragmented solution presented another major drawback: an integrated view on a customer was not possible. Tracking the behavior and preferences of a customer, requires a conformed view on customer data throughout the organization.

Faced with all of the above-mentioned problems, TUI Jetair asked element61 to execute a Performance Management Roadmap in order to come up with a clear and realistic long-term plan.

Solution

The goal of this roadmap study was to review the existing TUI-Jetair architecture, analyze the information gap that Jetaircenter experienced and design a to-be architecture. Following the acceptance of the roadmap, element61 was also selected as partner of choice in implementing the different iterations of the roadmap. The following picture presents the long-term to-be architecture.

Enterprise-wide Business Intelligence

At the heart of the new architecture is the Enterprise Data Warehouse. This data warehouse functions as a hub to consolidate data from all source systems involved. By reusing the results of the budgeting process in the IBM Cognos Planning application, a closed loop is created, allowing for easy reporting on actuals versus budget and/or forecasts. The enterprise data warehouse contains conformed, atomic and auditable data. Where appropriate, history tracking is enabled. As multiple sources contain customer data, a data quality process is necessary in order to create a global, "golden customer record. This will allow advanced customer intelligence reporting and behavioral analysis of a customer throughout his interactions with the different departments of the organization. On top of the enterprise data warehouse, data marts are defined per relevant subject area.

Having an enterprise data warehouse offers more advantages:

  • Corporate management information over entire business
  • Different entities can benefit from each others information
  • Cost effective maintenance of central architecture versus disparate silos
  • Common data source can be integrated cost effectively
  • Better leverage of software investments
  • Shared cost effective hardware platform

One of the main drivers for building this centralized architecture is TUIs desire to offer a better and integrated "-customer experience to its customers. While in the past, the tour-operator business was mainly product-oriented, a shift is made to a corporate culture and commercial approach that is much more customer focused. The centralized BI solution plays a key role in defining and enabling that strategy, bringing together all customer information and all customer transactions from the different brands and channels into one single management information environment delivering true customer insight.

Project approach

It is never wise to approach such a considerable BI program as a single project. TUI Jetair therefore decided to split the entire BI program into a number of iterations, each iteration being based on one of the source data applications.

The first iteration was focused on the Jetaircenter operational needs, involving financial, operational data and basic customer reporting. The goal of this iteration is to replace the existing operational Cognos framework, freeing up system resources for the operational system. This phase also delivers the foundation for additional subject areas to be added in later iterations.

The second iteration focuses on the commercial activities in the retail outlets and will allow for reporting and analysis on the commercial effectiveness and efficiency of Jetaircenter staff.

The third iteration involves the creation of the single view of the customer. By integrating customer data from all available sources within TUI and by solving any data quality issues along the way Jetair is able to have a 360 view of their customers and their transactions.

Subsequent iterations involve integration of disparate data sources such as HR information, Customer Satisfaction Questionnaires, Complaints, This program will ultimately deliver an environment where all management information questions and queries can be solved by the new enterprise-wide BI solution.

Business Intelligence front-end

The user access to all this centralized management information happens through IBM Cognos BI 10. Depending on the requirements and skills, users will have different modules available, but most of the standard information will be made available in reports, built in IBM Cognos Report Studio. These reports do not require any technical knowledge on behalf of the end user. All the user needs to do is select the correct report, decide on the report parameters (time, region ) and the report will be generated automatically. An example of such a preformatted report is shown below.

Enterprise-wide Business Intelligence

Other, more demanding and experienced business users, will also be given access to an analysis environment, where they can perform in-depth analyses (drill-down) and solve ad-hoc queries, without requiring any technical knowledge. This is also realized in Cognos 10 Business Intelligence.

A third possible form of interacting with the data is through dashboards. These are highly visual and interactive representations of data, usually at a high level of aggregation. Their purpose is to give a quick overview and summary of different key figures.

Technology used

The architecture of the project is based on a best of breed software selection:

  • Informatica Powercenter 9.1: ETL-tool. Informatica has been in de Gartner Magic Quadrants for years and is considered to be a leading data integration platform. For the first phase of the project, the Informatica Powercenter suite was used to stage source data from different sources (MySQL, text, Oracle) into an Oracle 11g release 2 database.
  • Oracle 11g release 2: enterprise data warehouse database platform. Oracle offers a world class database platform that offers a lot of specialized features for data warehouses like bitmap indexes, partitioning,
  • IBM Cognos 10.1 Business Intelligence is used as a BI reporting platform. Cognos offers advanced reporting / analysis capabilities and leverages metadata consistently by using Framework models.

element61 responsibilities

element61 played a key role in each of the phases of this program. element61 has been responsible for the following roles & activities:

  • Definition of a Performance Management roadmap
  • Project management
  • Definition of the architecture
  • Installation of all components
  • Selection of software tools
  • Design & build in Informatica Powercenter 9 and IBM Cognos 10
  • Rollout, training & coaching of the customer both on the concepts & technology deployed

Conclusion

Using the elementary project methodology, the first iteration of the project was completed successfully and within budget. This iteration created a solid foundation to add future subject areas. Multiple data quality issues have been addressed in the source systems and more awareness was created with data entry users as they were confronted with less than accurate data.

Jetaircenter is able to gain further insight in their revenue figures without extensive manual effort to combine and compose all the data needed. Travel agencies automatically receive scheduled reports, containing detailed figures allowing them to better steer their business performance. The automation in conforming and distributing figures allows the Jetaircenter controllers to spend more time on their core activities.

TUI Jetair has not completed their Performance Management journey yet, if companies ever have. Some of the iterations described in this case will be executed over the quarters to come, as an iterative approach to investments in BI & data warehousing is a best practice. Nevertheless, by being able to better manage the retail activities, Jetaircenter is already reaping the benefits of the BI investments and vision of its management. This Return-on-Investment will only increase as more information is added to the BI solution and more users are experiencing the added value.