Union Pacific: On the fast track to efficiency

North America's largest railroad company is moving
full-steam ahead with Teradata.

by Karen D. Schwartz

After using Teradata for the past 12 years, Roger Bresnahan knows a thing or two about data warehousing. But the principal engineer of the Enterprise Data Warehousing Group at Union Pacific Railroad keeps on learning and finding more uses for this versatile technology.

With 39 years of service to the Omaha, Neb.-based company - the last 36 in IT roles - Bresnahan is well-positioned to reflect on the evolution of the company's data warehousing strategy and consider its continued growth.

It all started more than a decade ago, when a small group within the company initiated a pilot project to determine whether Teradata could handle multiple subject areas simultaneously. The answer, of course, was a resounding "yes."

Since 1992, Union Pacific has used Teradata to stay ahead of the competition. Here's the story behind the continuing success of North America's largest railroad company.

Data rides the rails
With 49,000 employees coordinating the movement of more than 7,000 locomotives along 33,000 miles of track in 23 states, Union Pacific needed a robust business intelligence solution to ensure operational efficiency and to streamline costs.

The goal, according to Bresnahan, was to find a way to integrate and manage data from various sources within the business in order to improve decision-making. At the time, it was virtually impossible to meet that goal because Union Pacific's IT environment consisted of a host of stovepiped systems spread across various areas of the company.

"By the time each group got its data, there was a real loss of connectivity between the different areas of the railroad," he explains. "You couldn't tie together important things like revenue cost, car movement, waybill information, accounts receivable and accounts payable-which is what senior management wanted to do so they could have an enterprise-wide look at the situation."

To address a range of business goals-faster data accessibility, decreased query response times and the ability to sort large volumes of data on demand-Union Pacific executives organized a team of outside consultants and internal experts to coordinate information from 26 different departments.

In 1992, the team designed a foundation for the proposed Teradata Warehouse. They then developed a pilot system consisting of seven different subject areas. By 1994, the data warehouse was nearly complete, running on a Teradata DBC/1012 with up to 100GB of storage and a dozen users.

As the system continued to grow, so did its hardware and capacity. Today, the 12-node Teradata Warehouse handles 400,000 queries per day accessing 3.5TB worth of information for 4,000 users per month.

The system gathers data from about 100 source systems, including several legacy stovepipe systems running on various IBM mainframes using IMS, DB/2 or FOCUS; EDI gateway platforms; non-EDI data feeds from outside companies; dozens of UNIX and Oracle systems; and SQL Server and Lotus Notes sources. Additionally, an in-house system called the Transportation Control System, which runs the railroad's daily operations and manages crew, routing, waybill and locomotive movement, feeds into the data warehouse.

The data from these source systems is transmitted to a centralized location and then transferred to the Teradata enterprise data warehouse. From there, users can access the data either in its raw form or processed through data marts. To simplify matters, the design team allows users to pull data into department-specific systems through any graphical user interface, database port or other frontend application.

"One of the goals was to make it as easy to use by as many people as possible. We realized that our customers weren't going to wait for the IT department to take care of it," Bresnahan says.

Today, Union Pacific's Teradata system consists of more than 2,600 tables. The largest single table is more than 330GB, and several tables contain more than 1 billion rows. Five thousand views of integrated business data cover more than 35 subject areas and support every major department in the company.

Each night, more than 200GB of fresh data are loaded into the data warehouse. And still the system currently has a 99.97% availability rate during prime-time hours.

Information destination
The result of all of this planning, integration and system development during the past 12 years has been the ability to run sophisticated analyses of various business operations-analyses that have resulted in millions of dollars in cost savings and revenue generation.

"For the first time, we can do so many types of analysis," Bresnahan says. "Take customer car orders, for example. We can use this system to analyze what will be a peak season for which type of commodities and where demand will occur. We can also analyze locomotive utilization, locomotive dwell time, train delays and payroll analysis.

"In addition, we can determine the best routes for the cars, analyze by market segments the best routes to run for certain types of traffic and decide how to split routes up by customer and car types. We couldn't begin to do these types of things before (Teradata)."

Other major analysis applications include:

Business analysis database-This application provides a seamless view of data from at least nine different systems: cost, customer, price, revenue, car movements, refunds, equipment, cash collected and commodities. A global view ensures accuracy and consistency.

Price management-Analyzing data across multiple subject areas helps determine new competitive pricing strategies and simplify complex pricing processes. This application results in fewer billing errors, better product definition, more consistent rates, an increase in revenue and the reallocation of marketing and sales resources.

Freight bill payment analysis-Finance, accounting, marketing and sales departments can view open and paid accounts to correct billing problems more easily and shorten the company's revenue cycle.

Cash stops-An automated application determines customer status by reviewing payment history, business volume and profitability. "Cash only" shipment flags significantly reduce bad debt write-offs.

Timekeeping operations-To ensure that employees are paid accurately and on time, Union Pacific performs audits before and after paychecks are issued.

Crew efficiency-Combined with the daily payroll-projection reports, these reports enable management to measure and review operations as well as correct inefficiencies to reduce expenses.

With applications like these, concrete savings aren't difficult to measure. For example, the system saves 48 clerical days per year in timekeeping operations alone, as well as generates an annual return of $7 million to the bottom line through payroll audits.

Crew efficiency increases also have been documented. The system reduced the re-crewing rate (when a train crew reaches its federally mandated service limit and must be relieved by another crew) from as high as 25% to less than 10%, saving millions of dollars per year.

EDW-powered analysis of profit margins also has resulted in changes to Union Pacific's pricing structure, which has generated millions of dollars in additional revenues since the system's inception.

"ROI overcomes all," acknowledges Bresnahan. In fact, return on investment has always been quite positive. The only way the team could justify the system in the first place was by proving that it would yield a 35% ROI, and it has-every year since 1992.

The track ahead
The sophisticated concepts that led Union Pacific to adopt efficient companywide IT operations-bringing data together with early EDW implementation and consistently measuring ROI along the way-solidify the company's reputation as a visionary.

Although Union Pacific's TDWI 2004 Best Practices Award-winning Teradata Warehouse is very mature, the EDW team isn't finished yet. In fact, it just completed a server upgrade in concert with a move of the central data facility from St. Louis, Mo., to the company's headquarters in Omaha. Bresnahan says the upgrade is a significant improvement of UP's production capacity and nearly doubles the capacity for development and disaster recovery. Additionally, the team is planning to upgrade to Teradata Database V2R5, which will provide better performance, more efficient reporting and enhanced processing and data loading methodologies.

Bresnahan, who is enjoying the benefits of the upgraded Teradata system, has no intention of stopping now.

"We have just started to explore the data mining aspect of the warehouse and are looking forward to exploring other untapped areas as well. One thing leads to the next," he says. "There are so many things you can do with an enterprise data warehouse. It will be a never-ending process." T

back to top