In-Depth

Three-tier warehouse manages all right for Shell

• By Lana Gates
• April 1, 2002

Application Development Trends'
2002 Innovator Awards
Data Warehousing
Winner

Faced with an increasing need for fast, accurate and up-to-date operational information, Shell Oil Products set out to develop a cost-efficient management information solution that could be rolled out worldwide, accommodate changes quickly and be capable of providing information in a simple, accessible format worldwide. Shell needed a common data warehouse across its Oil Products that could deliver harmonized, segmented management information appropriate for each country and enable the aggregation of data for regional and global reporting.

A lack of flexible data warehousing solutions on the market forced the organization to develop its own data warehouse solution. So it established a spin-off of the Royal/Dutch Shell Group, Kalido Ltd., London, and invested $20 million in research and development over a five-year period to build the Kalido Dynamic Information Warehouse.

Prior to implementing the warehouse, Shell developed tailored warehouse implementations such as extraction, load and reporting programs from scratch, all based around a specific business model implemented in a traditional relational database. "These solutions are designed to meet a specific need at a point in time and all depend on that relational structure," noted Charles Manly, Kalido Ltd. director of operations.

If the structure needs to change at any point, the relational model needs to change and all the associated programs need to be rewritten and tested, which is often the reason these projects fail or have huge, ongoing maintenance costs.

The greatest challenge in implementing the Kalido Warehouse was to develop a generic software application to solve a specific business problem. This created heightened risks in overall costs and performance, as well as the possibility that some detailed requirements of specific business problems might be difficult to accommodate in a generic solution.

Kalido came up with a common data warehouse that Shell has come to rely upon as the heart of its MIS system for its entire Oil Products business. "We have a federated warehouse of 80-plus Kalido instances around the world loading data locally, feeding into zonal and finally a global Kalido Warehouse," Manly explained. Shell also uses the software for category management in its gas stations.

The Kalido Dynamic Information Warehouse uses pre-built structures that only require customization for specific market uses. It has the unique ability to handle business information in its original form, including both incomplete and incompatible information. By decoupling the business rules and the database schema, the warehouse provides significantly lower maintenance costs.

So far, annual maintenance costs with Kalido Dynamic Information Warehouse are typically at least 50% below those for a comparable, conventional data warehouse because the app does not require sources to use precisely the same data models and reference data.

Once installed, Kalido Dynamic Information Warehouse can automatically adjust to a company's structural changes, giving users consistent access to current data. The app learns the rules and dynamics of businesses and allows firms to isolate trends and patterns. It uses industry-standard technologies such as Oracle for data storage, and is compatible with decision support and data query tools such as Microsoft Excel and Business Objects' BusinessObjects. In addition, Kalido Dynamic Information Warehouse complements the capabilities of major ERP systems.

Because Kalido Dynamic Information Warehouse integrates and manages a business' reference, or meta, data, it can manage data stored in disparate locations and inconsistent structures, allowing multiple views of business information to be generated dynamically.

Kalido Dynamic Information Warehouse's ability to compare, integrate and store data from multiple sources, regardless of the data structure, allows both business and IT users to create data marts (or reports) for specific tasks, such as evaluating costs, examining corporate revenues and measuring profitability. Because of this ability, the warehouse is already in use in the areas of finance, human resources, retail, oil, marine and aviation.

The project required buy-in from Shell's independent operating companies in various countries. Kalido Ltd. realized that buy-in as a result of a successful pilot project in Australia. Users were involved throughout the project in overall requirements, setting and ensuring the flexibility and usability of the final product.

The majority of the development team brought expertise in C++ and Java, while others contributed expertise in Oracle, DB2, SQL Server and Visual Basic.

Kalido Ltd. developed Kalido Dynamic Information Warehouse specifically to manage reference data on a global scale for one of the largest and most complex global organizations in the world, Shell Oil Products. The success of this project has opened other doors for Kalido Ltd., which has signed license deals with Unilever, Philips, Cadbury/Schweppes and the Halifax Group Plc.

Above: Coman Wakefield and Chris Angus

Project: Kalido Dynamic Information Warehouse

Purpose: To provide a common data warehouse across Shell Oil products; to deliver harmonized, segmented management information that supports the business at a country level; and to enable aggregation of data for regional and global reporting.

Benefits: Global connections, lower costs, agility, flexibility and integration.

Platforms: Client: Windows 95/98/2000/NT/XP; App server: Windows 2000, NT4; Database server: Windows 2000, NT4, HP-UX, Solaris, Compaq True 64, IBM AIX

Keane Report: