Ted Kowalski (Shell Oil US) and Adrian Walker (Reengineering Llc.) have been working on a natural English query language to SQL translator and its use in oil product trading. The present use case involves matching customer demand for a particular quantity of product with an optimal supply source available to the trader. Many factors affect optimum product composition including the season, the locations of available equivalent products, and the availability of suitable transportation.
SQL
A competitive supply chain plan depends on knowledge of the above factors, on business policy knowledge, and on inventory facts in SQL databases. Because the situation can change rapidly, it can be difficult to write conventional application programs and SQL queries to optimize profitability. It is possible however to express the knowledge needed to optimize fulfillment using open vocabulary business rules expressed in English. These expressions are automatically translated into the appropriate SQL queries to produce a suggested supply chain solution. Even in simple examples, the generated SQL queries are too complex for a programmer to write reliably. However, it is easy to change the business rules to specify a new policy, and the generated SQL then changes automatically. A feature of the technology is that a supply chain solution can be explained, at the business level, in hypertexted English.
An example
Say a target region needs 1000 gallons of product ‘y’ in October, 2005. We then ask what alternative routes and modes-of-transportation (truck, train, boat, pipe) do we have to get that product to the region. We are also interested in the proximity of a refinery with available capacity and we may need a delivery plan that is optimized to deliver on time, make a profit, and beat the competition. Plain English rules such as ‘estimated demand some-id in some-region is for some-quantity gallons of some-finished-product in some-month of some-year’ are drafted to describe the optimization process.
SQL complexity
Even a few straightforward English language rules combine to make SQL queries of considerable complexity. Moreover, the English language business rules reflect the underlying business process more clearly than the generated SQL and are more amenable to editing and further optimization.
The current status of this project in Shell is unknown. The full Kowalski-Walker paper is available on www.oilit.com/papers/kowalski-walker.pdf.
© Oil IT Journal - all rights reserved.