Dirk Anderson

TRANSPORTATION: TRACKING COMPETITIVE ADVANTAGE

When consolidation and competition from other transportation modes put the squeeze on freight railways, Burlington Northern Santa Fe fought back. A new data warehouse helped the company identify cost-cutting opportunities and increase revenues-a goal shared by companies in any industry.

Over the past 30 years, the North American railroad industry experienced a major restructuring. Consolidations among U.S. freight railroads have reduced their numbers significantly; soon, the industry may be dominated by only two major transcontinental railroads. In Canada, the once government-controlled Canadian National Railroad was successfully privatized. Today, Canada is home to two strongly competitive railways. Privatization is even occurring in Mexico. In some cases, American firms are using their management skills to guide Mexican railroads to independence and profitability.

These new forces brought great challenges. Shareholders continually demand productivity improvements from both the railroads' assets and employees. At the same time, railroad customers increasingly demand improved service and lower transportation costs. Competition from the trucking and shipping industries continues to grow. To thrive in the next decade, railroads will have to determine how best to leverage assets to improve market share and increase shareholder value - a common challenge of publicly held corporations in any industry.

To stay competitive, Burlington Northern Santa Fe (BNSF) Corp. needed to synthesize and consolidate expense-reporting information from disparate transaction systems. Faced with a legacy expense-reporting system that could not provide a single, accurate account of company expenditures, BNSF set out to determine how a major transportation company, with employees and operations spread out over a large geographical area, could best identify and control its expenses.

REPORTING PROBLEMS
The legacy BNSF expense-reporting system had many limitations. Expense data was kept in nine different homegrown legacy systems that didn't talk to each other, and each system used its own calculations and unique formats for reporting expenses. Therefore, analyzing individual department expenses proved a daunting task. Managers logged onto each system individually and located the correct expense reports, then consolidated the data manually to produce the departmental view.

Further complicating the process, some expenses were only available for reporting on a monthly basis. This time lag made it difficult to react quickly to changes. For example, if labor or material costs exceeded forecasts on a project, managers often were unaware of the problem until a month later. This time lag seriously impeded a manager's ability to react quickly. Even worse, managers often went to meetings with conflicting expense figures, and it wasn't possible to determine whose numbers were accurate. Furthermore, legacy databases offered limited facilities for performing ad-hoc queries. Therefore, managers had to rely on technical staff to perform analysis on the expense report data.

BNSF decided to implement a solution, called the Single Source Expense Project, to develop a single, enterprisewide expense-reporting system that would take the guesswork out of analyzing the company's billion-dollar annual expenditures. A new data warehouse was a very appealing solution: Material expenses, labor costs, accounts payable, leasing charges, and miscellaneous expenses could be consolidated into one enterprise database specifically designed and organized to enable user decision-making.

Before beginning the project, BNSF developed a list of objectives for the solution. These included:

• Ease of use. Anyone from busy corporate executives to field managers - regardless of computer skill - would be able to use the solution.
• Summaries. The system would have to provide summaries by general ledger account, activity date, vendor, business unit, and region.
• Drill down. Users would need the ability to drill down from high-level summary data to granular-level expense data.
• Forecasting. The solution would have to provide a way to compare actual expenses against budgeted expenses and use actual expenses to forecast future expenses.
• Retention of data. Because analysts need to see how material and labor expenses increase compared to previous years to identify potential areas for cost reduction, the system would have to provide several years of historical expense information for historical trend analysis.
• Views. The system would have to accommodate different views that allow data to be presented from different angles and perspectives. These perspectives included both operationally and financially oriented views and a "responsibility" view that could show expenses attributed to the manager responsible for those expenses.
• Performance. The system would have to support fast response times on even drill-through queries scanning large volumes of data.
• OLAP functionality. BNSF wanted the solution to provide typical OLAP capabilities, such as the ability to twist and turn and slice and dice data on the user's workstation.
• Scalability. The system would have to support more than 100 concurrent users without performance degradation.
• Availability. Because BNSF wanted data from the prior day to be available by 8 a.m. the next day, expense data would have to be loaded and summarized during the night.

SINGLE SOURCE REPORTING
In late 1998, BNSF initiated the Single Source Expense Project with a completion goal of 24 months. The company decided that a large, enterprise data warehouse server combined with a smaller OLAP midtier server would provide the best solution based on the list of project objectives. Because BNSF already used a Teradata data warehouse for other kinds of data, the company decided to store expense data in the warehouse, too. Seagate's Holos server was selected to provide OLAP capabilities.

Implementation followed a three-step process, including:

1. Proof-of-concept: Create a working model. In this initial step, which lasted six months, BNSF created a limited working model to validate the Seagate Holos OLAP tool product performance claims under real-life conditions. This step also included validating data warehouse performance, including the OLAP tool's ability to function effectively in drill-through queries to the data warehouse.
2. Phase 1: Build the framework. Over the next six months, the company set up the framework and structure of the system and released the first set of production expense reports and charts.
3. Phase 2: Add additional functionality. The final step, scheduled to last 12 months, involves adding additional reporting capabilities, including an expense-forecasting tool, to the system.

An existing eight-node NCR WorldMark 4800 MPP server houses BNSF's enterprisewide Teradata data warehouse (see Figure 1). The machine not only provides adequate space to hold detailed expense data structures, but also provides the necessary processing power to handle dozens of concurrent end-user queries. The system easily scans millions of detailed records to process the many queries running against the warehouse. Over the course of the project, volume in the Teradata warehouse has grown; during the current second phase, BNSF upgraded the server to 10 nodes, with a capacity of 2.2 tera-bytes of usable data storage.

Seagate's Holos midtier database, installed on a NCR WorldMark 4400 Mid-Range Server with 560GB of storage, serves as the end-user tool for querying and analyzing expense-reporting data. The OLAP tool has an easy-to-use graphical interface and performs in-depth analysis with drill-down capabilities. All high-level, expense data roll-ups are stored in multidimensional data cubes.

Holos users can easily slice and dice and twist and turn the high-level summarized data residing in the cubes to produce their own reports. A variety of charts and statistical analysis tools also are available. When users want to drill down for more granular data, Holos seamlessly passes the queries through to the Teradata data warehouse relational data structures on the 4800 machine.

OVERCOMING IMPLEMENTATION PROBLEMS
BNSF information systems staff handled most of the programming for the Single Source Expense Project. COBOL programs extracted data from the legacy databases, such as DB2. Between midnight and 7 a.m. every day, Teradata utilities load large volumes of detailed data sourced from legacy databases into the data warehouse. Next, Holos extracts and summarizes all the data from the data warehouse and stores it in multidimensional cubes on the Holos server. The cubes are then available to users for analysis.

Although hardware and software implementation went smoothly during proof of concept and Phase 1, the IS team found it more challenging to format data to leverage OLAP tool advantages. They quickly learned, though, to apply multidimensional modeling techniques to data originating from relational databases. Programmers had to identify important dimensions, recognize relationships between data elements, establish drill-down and drill-through items, and implement roll-up hierarchy structures. Sizing OLAP cubes provided another challenge; because cubes contain many dimensions, cube size could balloon with the addition of each new dimension.

RETURN ON INVESTMENT
Phase one of the Single Source Expense Project was completed and delivered to the user community on schedule. In the several months since BNSF implemented the system, the company has already started to identify opportunities to improve efficiency and help identify and reduce expenses. Managers in the field can pull up all daily expenses on their computer screens in just a few minutes. They can slice and pivot the data and drill down to increasing levels of detail to expose problem expenses. In the past, this kind of analysis took hours and involved accessing multiple systems and manual reconciliation.

BNSF is already reaping savings by preventing waste and fraud. For example, many employees are required to wear safety boots, which are purchased and paid for by BNSF. In the past, it was difficult to track employee boot purchases. An employee could purchase boots in January, transfer to another department in February, and request another pair in the new department. The new manager would have no way of knowing about the prior boot purchase and would approve the new purchase. Now, a manager can use the Single Source Expense system to quickly evaluate all purchases for an employee. Duplicate purchases can be traced, identified, and stopped.

In the second phase of the Single Source Expense project, which is currently underway, BNSF Railway is using the new expense-reporting system to pinpoint escalating business expenses before they become serious problems. Enhancements for predictive modeling of business expenses and an expense-forecasting system are also planned. And, a rules-based processing tool will be added to allow business units to apply different rules for calculating expenses. After the second phase completes, system improvements will be made as needed to handle business changes and new challenges.

THE FAST TRACK
In the competitive transportation industry, saving a few dollars on expenses can make a big difference when it comes to bidding on a competitive deal. By reducing expenses, BNSF can offer its customers lower prices. The long-term goal is not only to make the railway more profitable, but also to enable it to compete in areas where high expenses may have previously precluded the railway from even attempting to compete. BNSF plans to continue to refine the systems it has developed, and will continue exploiting its OLAP and Teradata warehouse resources to further identify and reduce expenses and improve customer satisfaction. These technologies will provide the competitive edge that will keep the railroad on the fast track to success.

Dirk Anderson is a senior data warehouse architect at BNSF Railway with 10 years' experience in decision support and data warehousing. You can reach him at dirk.anderson@bnsf.com.

 

Sidebar: BNSF Railway Vital Statistics

Burlington Northern Santa Fe Corporation (BNSF), with headquarters in Fort Worth, Texas, operates one of the largest railroad networks in North America. The company transports coal, grain, intermodal containers and trailers, chemicals, metals, minerals, forest products, automobiles, and consumer goods. Other company highlights include: 33,500 route miles cover 28 states and two Canadian provinces 5,000 locomotives and 90,000 freight cars in service 44,500 employees $9 billion in revenues in 1999

top

 
Figure 1: The BNSF data warehouse architecture.

top