Performing ETL With SQL 2

Who needs an engine? Not Sunopsis V3, which employs SQL to bring a fresh approach to ETL.

One of the major hurdles in designing a successful ETL solution is in identifying what changed in the source system since your last extract. Sunopsis makes this task easier for you by providing a change-data capture facility. This feature along with Sunopsis MQ makes the suite a good fit for organizations that need a near real-time update facility for their operational data stores.

Ease of Use

I tried using Sunopsis to create a new ETL program. I found the first step of setting up the connections to my databases (Oracle 9i) to be fairly easy. Sunopsis does a great job of reverse engineering the data model from the database. The graphical interface is rich, yet intuitive and easy to learn. With a little help from the excellent user manual, I was able to develop an ETL process quickly and execute it successfully. Sunopsis includes a built-in scheduler and also interfaces with external schedulers.

The Sunopsis product has four graphical user interfaces. You define the physical and logical architecture of the interfacing systems with the Sunopsis Topology module. The Designer (see Figure 1) is the interface you use for reverse engineering the data model, creating the ETL data flow, and generating the code. You manage user access to various functions and components of the system with the Security Manager. The Operator module can be used to manage the production interface for monitoring the ETL programs.

FIGURE 1 The Sunopsis Designer is the tool's primary developer interface.

Value-Added Features

Ease of metadata management is an important requirement of a good ETL tool. Sunopsis stores the metadata associated with the ETL programs in a database repository. Unlike some other ETL products that store the metadata in a proprietary format, the Sunopsis repository can be stored on any relational database, thus making its contents available for inspection using SQL. This feature can be particularly useful while trying to find out how a change to one part of the system might affect the other parts. Of course, Sunopsis provides its own built-in option for impact analysis, too.


Sunopsis v3

Editor's Choice
Brian T. Horowitz, Contributing Reporter
Samuel Greengard, Contributing Reporter
Nathan Eddy, Freelance Writer
Brandon Taylor, Digital Editorial Program Manager
Jessica Davis, Senior Editor
Cynthia Harvey, Freelance Journalist, InformationWeek
Sara Peters, Editor-in-Chief, InformationWeek / Network Computing