Case study: Script strips Oracle data, ships it to E.piphany

Reflect.com's production and other business processes use an on-site Oracle database that is continuously updated by customers and various business processes. The company's marketing department extracts information from the production database every day to use in its analysis and customer-focus efforts. The information in this data mart (part of the E.piphany system hosted by Interelate) is historical and static, and contains numerous summaries built to handle analyses, including elaborate queries on large amounts of data that can require extensive searching. "To do these types of analyses on the production database would be inefficient, lack historical perspective, and adversely affect the customer experience on the Web site," says Matt Doyel, Reflect.com's director of customer focus.

To keep the data mart current, an automated Unix shell script captures all the relevant data and transmits it to Interelate via SCP, a secure communications utility that uses Secure Shell (SSH) as its underlying protocol. SCP and SSH work like Unix "cp" and "sh" commands, but use public key cryptography to ensure that data is transferred securely over unsecured communication channels. All the changes to the database in the previous 24 hours are captured and transmitted to Interelate early every morning.

"It typically takes about 10 minutes to extract, compress, and transmit the data," Doyel says. "The key to minimizing this time was designing the system for daily, incremental updates instead of complete copies of the database. If we were to transmit a complete copy of the database it would take a full day at top bandwidth."

Lisa Napell Dicksteen is president of LMN Editorial, which handles writing and editing as well as marketing and public relations consulting.