Data Warehouse Refines Crude Data

One of the greatest challenges that global enterprises face is managing data across multiple sites, separated by land and sea - not to mention time zones, language barriers, and business rules. Oftentimes, this dilemma of data residing among disparate databases is solved with a data warehouse. A data warehouse copies data from various production databases, converts it into one format, and dumps it into one database. From there, business intelligence tools can be used to perform data mining functions and computations within the new data set.

Take Shell Expro (Aberdeen, Scotland), for instance, a division within the conglomerate Royal Dutch/Shell Group (The Netherlands) and Exxon Mobil Corp. (Irving, TX). Shell Expro has 2,400 employees and interests in 57 gas/oil fields throughout the United Kingdom. In January 1997, as Shell Expro experienced growth, it decided to install an SAP ERP (enterprise resource planning) system. The ERP system was implemented with the hope of enabling the company to standardize its data capture and reporting functions. At the same time, Shell Expro's parent company raised the bar on the type and depth of reports Shell Expro had to submit to qualify for continued funding. "Shell Expro has to submit a five-year forecast to corporate detailing how much oil it could produce, including return on investment stats, economic ratios among UK countries, and political environment considerations," says Steve Mutch, data warehouse team leader. Without these figures we wouldn't receive funding to keep operating."

ERP Lacks All The Resources
Shell Expro found that the reporting tools built into its planning applications and its SAP were inadequate at meeting internal management's stringent reporting and were unable to satisfy Shell Group's increasingly demanding planning requirements. Besides being inflexible and requiring IT resources to make changes, running reports with the SAP system created other problems as well. "Because the SAP system is used primarily for transaction processing such as invoice processing and material requests, reporting requests cannot be optimized like they can be in a data warehouse," says Mutch. "In fact, there were times - usually at the month's end - when performing reporting and transaction processing simultaneously brought the SAP system to a halt." Shell Expro looked into a data warehouse with a data management system and a report and analysis tool that would solve this problem.

Data Warehouse Fixes Bottleneck
Shell Expro chose Kalido's (London) Dynamic Information Warehouse and Business Objects' (San Jose, CA) business intelligence tool for its information access, analysis, and data sharing needs. The installation ran into no integration issues and was up and running within two weeks. "Kalido is one of the few companies to offer an out-of-the-box data warehouse solution," says Mutch. "Most data warehousing solutions require months of upfront planning and programming before going live. The only upfront planning our solution required was to determine the business rules for the solution." Shell Expro set up its data warehouse to migrate new data from its ERP system to the data warehouse on a monthly basis, after business hours, and from its planning tools on a daily basis during the planning cycle. The Business Objects' business intelligence tool mines the data within the warehouse and outputs the results to reporting tools such as Microsoft Excel pivot tables, HTML files, PDF files, or Business Objects reports. "We use the Kalido data warehouse in conjunction with Business Objects to generate all our management information and planning reports," says Mutch. "This enables the planning and business analysts to analyze the data from nicely formatted online suites of information, complete with dashboard dials and tables that can be drilled down."

The benefit of Shell Expro's solution is that it produces accurate reports within minutes on such subjects as total oil production, how many reserves are left, how much money will be spent on a project, and the production target. Kalido uses a generic database structure, which enables it to quickly merge data from disparate sources, such as SAP-created data with data from other Oracle and Microsoft Access databases. "One other benefit to the Kalido data warehouse structure is that it cleanses data within the warehouse, as opposed to forcing the user to cleanse it at the source," says Mutch. "Because we have 30 years worth of data in our databases, it would be too huge of a task to coordinate with every site to enter data in a uniform format." For instance, one system might use "Bill" and another says "William." Instead of requiring both sites to use the same name, Kalido can automatically and transparently map and convert the data within the warehouse. This feature enables Shell Expro to perform data analysis on hundreds of thousands of fields of data and produce useful reports within minutes.