What is BIDW

What is Data Warehouse & Business Intelligence (BI/DW)
A data warehouse is a structured collection of data from disparate systems in a central data store in a uniform format. Three characteristics make the data warehouse extremely valuable for decision support applications:
• The data is historical: History is maintained for as long as the data might be relevant. This gives managers the ability to analyze long-term trends and patterns that influence business. Airline data warehouses typically maintain inventory and booking data, enabling analysis of booking curves and customer buying behavior.
• The data is integrated: Airline operational systems are highly disjointed. The reservations system, the GDS, revenue accounting, departure control, frequent flyer and other systems all hold fragments of information about the airline value chain: joining these fragments together is vital in understanding customer behavior, and identifying revenue leakage and fraud. Joining together operations and financial data is required to truly understand the effectiveness of business decisions and processes. Gaining a 360 degree view of customer interactions, across various channels, is essential in developing marketing insights.
• The platform is purpose-built for analysis: the data warehouse is designed for easy navigation by business users; the server is generally a high-performance, parallel processor or cluster; and the database software is specifically designed for complex analytical workloads. These factors result in responsiveness for analytical workloads that operational systems cannot achieve.
Business Intelligence is a set of technologies and practices used to enable the collection, transformation and presentation of relevant information, generally from a data warehouse, to improve business decisions. Business Intelligence may include:


• Reporting
• Ad-hoc querying capabilities
• On-line Analytical Processing (OLAP)
• Data Mining
• Management Scorecards/Dashboards
The data warehouse may also support specialized BI applications including campaign management, financial allocations/consolidation, and fraud detection.

BIDW for Airlines

Why should airlines have BI/DW systems?
• Which customers are at risk of churning?
• Which markets should be targeted with sales promotions?
• How can spoilage be reduced?
• What is our future cash flow?
• How many passengers are likely to use a self-service kiosk?
• How should we change the flight schedules based on demand?
• How can we analyze fraudulent transactions?
• What is the actual cost of processing a check-in?
• How does crew absenteeism affect on-time performance?
• How many of each type of meal should be uplifted?
• Where should ULDs be positioned to ensure adequate supply?
• How has market share been affected by new competitor schedules?
Answering these questions requires information from many of the airline’s systems, as well as external data. The sources of data for the data warehouse might include the following:
• Reservations
• Departure Control
• Revenue Accounting
• Financial Accounting Systems
• Crew Management
• Loyalty
• Cargo
• Tours/Holiday
• MIDT
• BIDT
• ATPCO
• BSP
• Flight Operations Control
• Human Resources
• MRO
• Budget and Planning systems

This data is usually delivered in different application specific formats from each of these applications. The data warehouse must have the ability to transform this data into its own format and structure (its data model) which is optimized to enable analysis and reporting by managerial users.
Consider these real-life examples:
• The Loyalty Program Manager wants to know the travel patterns of his top customers and analyze how well incented demand translates into revenue earned by the airline. For this, he requires data from the Loyalty Program application, Reservation system, and Revenue Accounting system. Tickets, which contain the revenue data, can only be linked to a loyalty program member via the PNR, which contains the loyalty membership number as well as the ticket number. The data warehouse with its ability to link the different data items together enables such analysis to be performed.
• Flight Operations wants an estimate of revenue by flight departure, in order to plan some necessary cancellations. Any top-tier loyalty members and VIPs who may be affected are also taken into account. The data warehouse is used to generate a single report, combining passenger bookings, actual and expected revenue, forecasts of no-shows and cancellations, and CIP/VIP passengers.
• The Sales Manager is facing a decline in bookings on a specific route, but his traditional reports are unable to show the source of the problem. By analyzing PNR data, he can see which Point-of-Sale, markets and channels contribute to the decline.
• The Finance Manager has always relied on manual audits and sampling to detect cases of agency fraud and revenue leakage. Using the data warehouse, every booking and ticket can be reconciled, validated against fare rules, and compared with corresponding data from the GDS. This analysis reveals numerous cases of booking class mismatch, coupons used out of sequence, fare rules not adhered to, cross-border sales, illegal name changes and many forms of abuse: the airline is able to recover lost revenue from passengers and agents, and deter future cases by demonstrating its ability to perform these checks.
• Customer Relations wishes to proactively contact passengers who have been disrupted, with an apology or offer. Owing to various constraints, they can only do this for elite members of the loyalty program, who have suffered a severe disruption or multiple incidents in a short space of time. The data warehouse is able to combine service information (such as flight cancellations, denied boarding and mishandled baggage) with customer profiles, to form a picture of each member’s service experience. Recovery efforts can then be targeted to those customers who are most deserving.

A Breakthrough in BI/DW Implementation
Designing and implementing a data warehouse has always been labor-intensive, expensive and risky. Three separate skill sets are required:
• Methodology skills – using the methods, approaches and techniques for capturing requirements, designing databases, performing testing, managing data quality etc – all of which are highly specific to business intelligence
• Technology Skills – experience in configuring and tuning the specific DW/BI tools, such as Extract-Transform-Load tools, parallel database servers, and BI analysis and reporting tools
• Airline Industry Skills – the ability to align the technical solution with real-world business requirements, and effectively build a bridge between business processes and the IT solution
In the past, airlines have faced one of the three approaches:
• Use a large, global systems integrator: these firms are usually able to bring the right mix of skills, but at a high cost.
• Use a “local” systems integrator: while offering lower costs, these companies lack airline industry knowledge.
• Use in-house resources: the lowest cost option, but highly constrained by lack of capacity and alternate demands on time. Few airlines have been able to muster sufficient resources and skills to build a true enterprise-class solution, this way.
Some vendors offer off-the-shelf data models, but these form only one component of the solution. Each airline is left to define its own Key Performance Indicators and reports – even though this Business Intelligence layer is applicable to airlines with similar operating models.

Introducing aiCUBE

IBS, with aiCUBE, offers two key breakthroughs:
Being focused on the airline domain, IBS brings industry expertise in various areas including reservations, departure control, crew management, loyalty, cargo, flight operations, airport operations and tours. Combined with a dedicated Data Warehouse professional services practice, IBS can deploy a skilled team to a project anywhere in the world.
Secondly, with aiCUBE, IBS offers pre-built assets to accelerate the implementation of an airline BI/DW solution. aiCUBE comes with a ready data model, together with numerous pre-built reports and KPIs. These have been developed with expertise gained from the thousands of man years IBS has invested in product development for the airline industry.
aiCUBE provides a jump start for the BI project, enabling clients to go live with the solution in much less time compared to traditional “hand-crafted” solutions.
The data model defines facts and dimensions across different data subject areas, including reservations, departure control and loyalty. These are implemented in a consistent way, easing the learning curve for both developers and users. All key dimensions (such as flights, airports, points-of-sale, etc) are shared.
The reporting layer consists of a number of pre-built analysis and reporting templates.

aiCUBE Overview – Current version
The current version of aiCUBE (Version 1.0) focuses on Passenger, Loyalty and Cargo.

Passenger
Data for the passenger area will come from either a single integrated PSS system or through multiple systems covering reservations, departure control and revenue accounting. The subject areas covered under aiCUBE are:
• Schedules
• Inventory
• Bookings
• Departure Control
• Passenger Revenue
• Origin and Destination
Some of the key reports and KPIs available from the system include the following:
• Passenger Revenue and Yield Analysis
• Production and Seat Factor Analysis (ASKs, RPKs)
• Aircraft Utilization
• Advanced Load Factor
• Spoilage Analysis
• Revenue by Origin and Destination

Loyalty
aiCUBE links passenger transactions (bookings, check-in and tickets) to the loyalty member profile to provide the loyalty manager with a 360o view of customer behaviour. aiCUBE may also be used to measure key performance indicators for the loyalty programme itself. The preconfigured reports and dashboards related to Loyalty include:
• Membership: growth, acquisition rate, churn rate, activity rate
• Airline Contribution: share of airline traffic & revenue from FFP
• Partner Contribution: enrolment, activity, revenue
• Accrual: air and non-air
• Redemption
• Liability
• Points Expiry and Breakage
• Database Quality/Member Contactability
• Cost

Cargo
Most airlines carry both passenger and cargo. Some airlines have separate freighter carriers that are tracked separately. The different parts of the cargo subject area can be broadly classified based on their functionality as follows:
• Capacity Management
• Operations
• Revenue Management
• ULD Management
• Mail Management
• Claims
• Accounts

aiCUBE Roadmap
In future versions, the following subject areas will also be covered in the offering:
• GDS data: booking information (MIDT, BIDT) and sales (BSP and ATPCO)
• Crew
• Flight Operations
• Maintenance Repair and Overhaul
• Tours and Holidays
• Finance
• HR