With approximately 3,200 employees in 16 countries, Peoria, IL-based Caterpillar Logistics Services, Inc. ("Cat Logistics"), a subsidiary of Caterpillar Inc., provides integrated logistics services to leading manufacturers and distributors around the globe. By helping clients deliver products to customers more efficiently, Cat Logistics helps reduce distribution costs and enhance customer service.

In February 1999, Cat Logistics added Web-enabled, AS/400-based business intelligence (BI) functionality to its logistics application. In this article, I describe Cat Logistics' BI application and the benefits it brings to users.

The Application

Cat Logistics' BI application runs on a single AS/400 S20 server and consists of a data warehouse and six relational online analytical processing (ROLAP) data marts. (For an overview of Cat Logistics' system, see Figure 1.) The data warehouse maintains detailed information, and the star schema-based data marts store summary data for five key business areas: sales, backlog, inventory, purchasing, and service levels.

Cat Logistics uses Coglin Mill's data warehouse management application, Rodin, to create physical and logical files in the data warehouse. A graphical client/server application, Rodin provides comprehensive support for AS/400-based data transformation, data management, and data propagation. Because it generates RPG code for these tasks, Rodin supports functions that SQL-based transformation products can't perform directly. Designed for mid- to large-scale data warehouses that can process millions of records on a daily (or even hourly) basis, Rodin also supports incremental and full-refresh propagation routines.

To create the tables and metadata for the data marts, Cat Logistics uses DBGenerate, a module of Silvon Software's data warehouse solution DataTracker. A native AS/400 application, DBGenerate uses configuration parameters entered in DataTracker's DBAdmin module to create DB2/400-based fact and dimension tables. The module also generates requisite join logical files to improve query performance.

Data is moved from Cat Logistics' IBM S/390 mainframe to its AS/400 via File Transfer Protocol (FTP) daily. When the data arrives, Rodin automatically initiates processes that transform the data and move it into the data warehouse. Figure 2 illustrates the maintenance of a data set in Rodin. For organizations that use IBM's DataPropagator to replicate data across members of the DB2 product family or IBM's DataJoiner to replicate data across databases (e.g., from Oracle to DB2), Rodin integrates fully with these products as part of Coglin Mill's support of IBM's Visual Warehouse and DB2 Metadata program.

After the data is loaded into the warehouse, DataTracker's replication module, DBLoad, moves data to the data marts. To do this, DBLoad first accesses DataTracker's metadata repository to obtain details about the target data marts' schemas. Then it aggregates data to match drill-down specifications that are predefined by Cat Logistics' IS staff. Once the aggregation processes are completed, DBLoad executes insert and update operations to propagate data to the data marts.

User Access

Cat Logistics' customers use DataTracker's Windows-based DBView module to interactively query and analyze the data marts and to drill up and down through various levels of aggregated information. (Sample views from DBView appear in Figure 3.) DBView also features spreadsheet, graphing, and online analytical processing (OLAP) functionality. In addition to DBView, Cat Logistics uses Seagate Software's Crystal Reports to create detailed management reports directly from the data warehouse.

To facilitate fast remote access and reduce communications costs, Cat Logistics lets users query the data warehouse and data marts using Web browsers via an intranet and an extranet. (Providing access via the Internet is the next step in Cat Logistics' BI rollout.) Adopting a "thin-client" approach, the company uses Citrix Systems' MetaFrame software to administer communications between DataTracker clients and the AS/400 server.

DataTracker client software runs on the Citrix server itself and communicates with the AS/400 data warehouse server via TCP/IP. Only query results are passed back to users, significantly reducing network traffic and improving overall system performance. Data presentation and analysis are processed on the client. Thanks to this design, Cat Logistics' European users have nearly the same response time as their counterparts in the United States. As an added benefit, Cat Logistics' IS staff can perform upgrades to both DataTracker's PC-based software and IBM's Client Access software on the Citrix server instead of on each user's PC.

Satisfied Users

Feedback from Cat Logistics' customers has been very positive. With the new application, users regularly perform analyses that couldn't be done prior to the data warehouse. Analysis of historical information, in particular, had been a tedious task, and viewing information across different subject areas was nearly impossible.

With the data warehouse, however, users have a consistent view of the business. Because the data warehouse contains information from a variety of operational systems, users spend less time collecting data and more time analyzing it. Information that used to take weeks to collect and analyze is now available immediately.

The success of Cat Logistics' BI application highlights the AS/400's ability to function as a data warehouse server in a heterogeneous environment. "We chose the AS/400 because we want to solve business issues and deliver solutions, not spend time supporting technology," explains Mark Vetter, manager of information systems. "We realize that Coglin Mill and Silvon Software are strong players in the data warehouse world, and MetaFrame from Citrix Systems made it possible to provide our information over the Web. For us, it was a natural fit."

Scott Steinacher, an independent consultant based in New York with more than 10 years of hands-on AS/400 programming and IT consulting experience, is a NEWS/400 technical editor and the author of Data Warehousing and the AS/400 (29th Street Press, 1998). You can reach Scott at ssteinach@aol.com.

Vendor Information

Citrix Systems, Inc.
(800) 437-7503 or (954) 267-3000
Fax (954) 267-9319
http://www.citrix.com
MetaFrame

Seagate Software
(800) 877-2340 or (604) 893-6389
Fax (604) 681-2934
http://www.seagatesoftware.com
Crystal Reports

Silvon Software, Inc.
(800) 874-5866 or (630) 655-3313
Fax (630) 655-3377
http://www.silvon.com
DataTracker