Warehousing Book for an Airlines

¶ … Warehousing book

For an airlines company, how can strategic information increase the number of frequent flyers? Discuss giving specific details.

The insights gained from data warehousing can deliver useful intelligence for redesigning marketing, selling, customer service and customer loyalty programs (Birkner, 2008). In addition, data warehousing is actively being used to understand the difference between customers' expectations and experiences, including which factors and touch points in service delivery contributed most and least to customer satisfaction. Services businesses are finding that the ability to mine their massive amounts of customer data and architect entirely new strategies as a result can lead to significant cost reductions and increased profits as well (Johnson, 2004).

Airlines are continually fighting a paradox of having to reduce their costs while also increasing customer loyalty. Continental Airlines was able to transform itself by using data warehousing and the business intelligence gained through the use of analytics to completely redesign its customer loyalty program and hold into customers who had been leaving in droves due to extremely low levels of satisfaction with the air carrier (Britt, 2007). Continental learned that the greater the level of systems integration across the many diverse, disparate systems in the company, many of which had never been used as part of a broader enterprise strategy in the past, the greater the Return on Investments (ROI) of their data warehousing and business intelligence systems (Dev, Buschman, Bowen, 2010).

All of these factors contributed to Continental Airlines being able to capture more customers by continually making enhancements to their Continental Airlines OnePass Frequent Flyer Program. Based on the insights gained from data warehousing strategies and the use of predictive analytics, Continental was also to determine what factors most contributed to customer churn as well (Qian, Jiang, Tsul, 2006). Knowing why customers were leaving gave Continental exceptional insight that competitors lacked and misunderstood to be pricing-related dissatisfaction (Britt, 2007). With the U.S. airline industry in pricing free-fall during this time period, Continental was generating insights and intelligence into the unmet needs of airline passengers, getting a firm grasp of what was causing churn, while the rest of the industry was reading the churn as price sensitivity (Ku, Fan, 2009). Continental drastically re-vamped their OnePass Program, creating marketing programs to show how they were working hard to get the roadblocks and impediments out of the way of travelers. The results were impressive, with customer satisfaction rankings of Continental going from among the worst in the airline industry to among the best (Britt, 2007). In the airlines industry, Continental is considered one of the most skilled at using data warehousing to increase customer loyalty while reducing churn and increasing profitability (Qian, Jiang, Tsul, 2006).

Exercise #4 -- pg. 44 -- Chapter Two Data Warehousing book

For an airlines company, identify three operational applications that would feed into the data warehouse. What would be the data load and refresh cycles for each?

The data warehouse of an airlines company is comprised of a myriad of accounting, finance, pricing, transaction, and services databases and systems. Studies indicate that the greater the number of integration points and the relative speed with which they share information, the greater the level of decision latency and accuracy in an organization's decisions (Watson, 2005). For an airline, the three operational applications that need to be integrated into a data warehouse to ensure the profitable operation of the business include its Customer Relationship Management (CRM), pricing management and optimization, and its operations systems that include on-time measures of performance and cost-per-seat analytics. These three systems are critical because for any airline, the costs of operation dictate what levels of service can be delivered at what price. The pricing system must be integrated into the data warehouse as this provides insights into the elasticity of demand in the markets the airline serves (Payton, Zahay, 2005). All three of these systems are also critically important for creating an analysis to guide fuel hedging forecasts and strategies (Birkner, 2008). Fuel hedging is the strategy Southwest uses to save up to 80% of their fuel costs over time, which leads the industry on this practice. Using customer history by flight, cost per seat, frequency of flight data and a forecast of fuel costs, the Southwest senior management teams devise strategies to buy fuel contracts using a strategy called hedging (Birkner, 2008). It is a strategy largely believed to be responsible for Southwest being the only airline in the U.S. To have never declared bankruptcy. Taking fuel hedging as an example, it can be seen that pricing and operations performance must be integrated and supported with batch updates on a daily basis to ensure the best possible decisions are made. The pricing systems of airlines have become increasingly sophisticated, using algorithms to define constraint-based pricing strategies that take into account elasticity of demand by flight (Wong, Chung, 2008). Pricing and fuel cost estimates must be real-time as they are updated with flight-by-flight analysis and also defined by the speculative price analysis, two critical real-time data needs within an airline's business model (Watson, Wixom, Hoffer, Anderson-Lehman, Reynolds, 2009). Data load and refresh rates for CRM systems can be less frequently done, yet need to be completed at a minimum on a weekly basis to ensure that the latest customer data is available for planning and implementing strategies. CRM systems need to have pricing data and customer satisfaction data from flights also integrated to ensure that the airline can index which flights are generating the highest profit per customer segment as well.

Exercise #3 -- pg. 97 -- Chapter Four Data Warehousing book

You are the data transformation specialist for the first data warehouse project in an airlines company. Prepare a project task list to include all the detailed tasks needed for data extraction and transformation.

The following is the project task list for completing a data extraction and transformation. First, the set of parameters needs to be defined for the extraction query as the source formats will vary across each databases and system that comprise a data warehouse. The syntax differences across the relational databases, virtual access points and systems must be taken into account. Next, after the extraction query is defined, a framework or taxonomy must be created for organizing the data that results from the query being completed. The focus on creating a stable enough taxonomy to support the many sources of data yet agile enough to provide support advanced queries needs to be defined. The goal of these first two phases is to get the entire data set into a single, unified data set that can be used for further analysis. Often data transformation specialists will complete initial parse-based analyses of the data to check for specific patterns and to evaluate if all sources of data have been successfully integrated during the extraction phase. In addition, data transformation specialists will complete periodic audits of data to ensure all data targeted for extraction have been actually captured as part of the initial phases. The next phase of transformation includes many steps, beginning with the loading of specific columns and the translation of their contents to coded values. This is an especially critical step if the query is going to be used for statistical analysis, where the data will need to be interval or ratio-level to be usable. The data will be next cleansed through a series of mapping, sorting and free form value analysis. Once the data has been completely analyzed to ensure missing, erroneous, or incomplete data has been either replaced or resolved, the data sets are ready to be aggregated. The aggregation process can take several iterative steps, which lead to a normalized data set ready for analysis and use for reporting. Data transformation specialists can define the surrogate key values or have automated systems to this based on analysis of the data set. Once all of these steps are completed, the data is reading for the more intensive analysis of pivoting and also defining column-based data. This is done automatically using ETL-based applications that take the surrogate values and create the structural data sets to complete additional analysis. Following this step, the data transformation specialist will often complete complex data validation that will be used for creating the rule design and exception handling. The transformations of the data sets will be completed with the rule designs and rule constraints in place, and the data set is ready for further analysis and use.

Exercise #4 -- pg. 138 -- Chapter Six Data Warehousing book

You are the manager for the data warehouse project at a retail chain with stores all across the country and users in every store. How will you ensure that all the details necessary to decide on the DBMS are gathered during the requirements phase? Write a memo to the Senior Analyst directly responsible to coordinate the requirements definition phase.

As the greatest challenge of implement a new system is overcoming resistance to change through the use of change management strategies, the most critical focus needs to be on capturing the unmet needs of users in every store quickly, thoroughly and with a high degree of transparency. The results of this effort needs to be shared electronically online to ensure everyone is kept informed about its progress.

Memo:

With the data warehouse project entering the requirements definition phase, it's important to start setting the foundation for users across the network of retail stores to begin having ownership of the new system. Getting them interested in the new data warehouse, and to take ownership of it will require getting their inputs early on during the requirements definition phase.

I'd like to suggest the following steps be taken to ensure their needs are captured as part of the requirements definition phase:

1. Create an online, protected portal and have a series of questions and applications on it to give each store user a chance to contribute to not only the content of the new data warehouse, but its usability as well. Use this data as a means to create bi-weekly conference calls to discuss the status of the data-warehousing project.

2. Enroll each store in Skype and get budget to make sure they can all participate on video conferences with regard to the user interface design and contents of the data warehouse. This will be critical for ensuring each person has an opportunity to contribute to the process as well.

3. Post the matrix of all user needs and show the date and revision fo the data warehouse when their needs were included in the design. Also be sure to recognize with praise and recognition those users in each retail store who work hard to provide feedback.

4. Give the stores an opportunity to work with early beta versions of the data warehouse software and the interfaces used for querying and using it. This will also provide a greater level of ownership as well.

5. Sponsor a contest to see which user in which store can create the best tutorial demo. Whoever wins gets to go around to all the stores and host training, at the company's expense and also gets a weekend in any city of their choice also on the company. This will drive up ownership of the data warehouse immediately and will also lead to competition to learn as much as possible.

Remember the challenge is to get the users in each store to change their behavior and support the new data warehouse initiative. Taking these steps will lead to much greater levels of ownership in the project.

Business Intelligence Text -- Cases.

Question 4-page 27 - What benefits are being derived from this initiative?

Vodaphone's many benefits of creating an enterprise data warehouse (EDW) include gaining greater accuracy and precision in the marketing, selling and customer churn reduction strategies, in addition to increasing profit margins over time. The company had also been facing a stagnating 56% market share, yet with the insights gained from the EDW and the business intelligence applications it supports, Vodaphone was able to determine the best possible message to deliver to the right customers at the right time. The formation of the customer knowledge and analysis department also served as a catalyst for more effective customer offer optimization, campaign effectiveness analysis, customer insights and the development of trigger-based marketing campaigns as well.