Case Study: Customer Tracking System (CTS)

Our company has a sales force scattered all over the United States. Whenever they visited a customer's site, they used to fill out a simple paper form and fax it back to the corporate office where it was placed in a filing cabinet. There was absolutely no call tracking of any kind.

We have two primary information stores. The first is an Oracle database that stores companies and people who have gone through our reseller certification course. The second is our ERP system that contains all of our customers and their sales information. The ERP system runs on AS/400.

We had 4 goals for phase 1 of CTS.

1. Get the sales people a customer list and details about the customers' order history.
2. Include last order date, last order amount, prior year sales, etc., so the sales people would have more information about the customers' size and order volume.
3. Have an online version of the call tracking reports, so the data would be automatically entered in the database and searchable.
4. Allow the sales people to go offline with their laptops and have the same information available to them as if they were online.

We decided on a master database schema in Oracle. We wrote a large batch of SQL code in order to pull information from both sources and merge it together into the CTS system. These scripts are run every night and they fully re-populate the data from both external sources. The data is then compared to the one retrieved the night before and time-stamps are updated to allow for changed-data queries. This accomplishes goals 1 and 2.

Since this application was written before the announcement of Data Abstract, it only makes use of the RemObjects SDK. As such,we wrote some classes designed to stream a dataset down to the client inside of a TClientDataSet. Since the TClientDataSet already supported streams, this was a trivial task. The client can request either a specific service from the middle tier or execute a custom SQL statement for more specific queries. All of this is handled through an Indy TCP/IP socket connection and RO. Everything runs live against Oracle.

As a final piece, we needed some online reports to view sales-person activity for a particular period. We set up an internal PHP web page that allows authenticated users to execute reports against live data in Oracle. They can choose the format of the report (HTML in the browser, or CSV for download) and add criteria to the search (date ranges, sales person name, etc.) for more localized reports. Goal number 3 is met.

The client application presents the user with many queries and reports, and provides a simple interface to add new data into the system or submit changes to existing data. Now the major goal (number 4) was to move CTS offline and replicate the data.

We chose DBISAM as the embedded database engine because of its strong SQL support. We created several helper applications to allow the database tables and indexes to be created by a script. Finally we created a loading application that would load a table from Oracle into a DBISAM table. These helper applications are wrapped into an RO service that the client can call to request a table. The RO service builds the table, compresses it, and sends it to the client, where it is then decompressed. This makes the most effective use of bandwidth since most of the sales people are on the road with nothing better than dialup.

Clients connect to the RemObjects SDK server, upload the changes they've made since the last replication, then start requesting updates. The changes are packaged into a compressed file (.RAR), then downloaded in chunks using RemObjects SDK. The size of the chunks is dynamically calculated based on the message round-trip time. Longer times indicate slower connections, and subsequently smaller chunks. This makes for optimal use of the available bandwidth.

This project has taken several months to complete, but it has delivered an up-to-date collection of information to our sales force. We now have a much more streamlined path of information flow, and it is much more current than the Excel spreadsheets being emailed around. Furthermore, it's secure in that the database on the client self-terminates if is not replicated for more than one month. RemObjects SDK has eliminated the need for any custom transport development, and has let us focus on what matters most, the application.

© Eric Hill — reprinted with kind permission.