DB2 helps Canadian National Railway Corporation improve customer service

"Using the DB2-based data warehouse, we can quickly and effectively meet the information requirements of end users. By granting analysts direct access to all the CN operation data stored in the data warehouse, the response speed is significantly improved, and the IT department is reduced as the main report builder and query manager. "-- RegSimpson, Information Department Manager, Canada National Railway Corporation

Application: Marketing and Operation Analysis Data Warehouse

Commercial benefits: improve customer service, reduce operation costs, and predict market demands and trends

Software: DB2 * fo rOS/390 * DDCS * (currently DB2Connect *) Hardware: S/390 *, RS/6000 *

For more than 75 years, the Canadian National Railway Corporation (CN) has been pulling this variety of products-cereals, automobiles, wood, and coal-from Canada to the United States. The CN railway has more than 1,800 locomotives, 60,800 trucks, and serves the whole of Canada and the central and western United States, including Vancouver, Montana and Halifax, And the 15,000-mile railway in major cities such as Toronto, Chicago, Detroit and buffalo.

Even with such a large network, in 1990s, CN still felt the competition pressure from other land transportation methods. To preserve the market share and increase profits, CN needs detailed information, such as asset utilization rate, profit of transportation lines, and the type of services the customer wants.

Today, Data Warehouses Based on DB2forOS/390 are the source of these important information. It is frequently visited by nearly 1,800 marketing and operation users. Through complex analysis of collected data, users can now track transportation demands rather than forecast, calculate transportation time, identify the frequency and cause of transportation delay, and monitor the utilization rate of vehicles in the transportation field. This knowledge helps CN companies improve service quality by measuring and improving service reliability and meeting customer shipping requirements.

Merge data to accelerate access

Before implementing a data warehouse, you need to collect information from scattered systems-transportation, engineering, accounting, and marketing-and then generate useful reports. For more complex analysis, you need to seek help from IT experts to combine data and generate reports. Considering the time and effort invested in this process, the results are limited. Multiple backend systems also impede routine operations. Therefore, a few years ago, CN company integrated its transportation system into a platform called a service reliability policy system, in which the Query Process was separated into an independent DB2 data warehouse. SRS is set up for efficient online transaction processing, "explains RegSimpson, manager of the Information Department of CN.

SRS integrates the daily core operations of all railways, such as transportation ticket preparation, Workshop operations and service scheduling. All the data generated by SRS is stored in the DB2forMVS database. A wide range of data warehouses are extracted at night. The data repository is DB2forOS/390 running on the S/390 server.

The data that users access to the data warehouse mainly uses the GQL language and front-end query and analysis tools of IBM Business Partner Hummingbird. GQL-guided graphical interface allows users to query data warehouses and generate detailed reports on vehicle traffic, power, transportation, and employee events-"Almost all railway operations are covered, "RogerCourtois, a database architecture designer in CN, said. GQL is selected as the standard query tool because it is easy to use and compatible with DB2/MVS hosts-it is now DB2Connect through DDCS on RS/6000 servers) and DRDA * gateway generate SQL statements that are transparent to the OS/390 platform.

"Using the DB2-based data warehouse, we can quickly and effectively meet the information requirements of end users," says Simpson. "By granting analysts direct access to all the CN operation data stored in the data warehouse, the response speed is significantly improved, and the IT department is reduced as the main report builder and query manager. "On average, the query time is reduced from a few days to several seconds. Most of the queries are completed within 15 seconds.

Performance and price

To support the promise of fast data access, CN is very cautious when choosing a database as its data warehouse. After visiting several major railway departments and their long-term needs, the Courtois team confirmed that the most important criterion was to accommodate a large number of concurrent users and quickly process any type of queries, from simple response queries to complex data mining problems. The cost is also a problem, because the railway funds are limited and will continue to decrease as other IT needs emerge.

In terms of performance, Simpson reported that DB2 Data Warehouses support 90 concurrent queries without compromising performance. "OS/390 server load manager is very effective in priority adjustment-balancing simple and complex queries based on their respective CPU clock," he said.

"To be honest, we initially suspected the scalability of the platform, but IBM proved its capabilities," continued Simpson. "Our Data Warehouse has doubled, from 1 GB to 2 TB by the end of July. This is a very challenging arrangement, but we rarely encounter problems that cannot be solved. "The final expectation of Data Warehouse capacity is 2.4 TB, and the number of users will reach nearly 2,000 by the end of the year.

In terms of cost, Courtois points out that "other competing products with the same performance and functionality are five times higher than IBM's and are expensive to maintain. "

Currently, all basic data has been loaded into the data warehouse. Once this step is completed, the focus is on how to maximize its effectiveness. The railway company will establish strategic decision support software, which may include data mining in the data warehouse. CN will continue to work with IBM in this and other projects. "IBM is an outstanding business partner who works closely with us in hardware debugging and expansion," said Simpson. "DB2 is a database with Industry energy and will constantly prove itself. It is already working for us. "