Performing ETL With SQL - InformationWeek

InformationWeek is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them.Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Software // Information Management

Performing ETL With SQL

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
6 Lincoln Knoll Lane, Suite 100
Burlington, MA 01803

Minimum Requirements: For the client station: Any OS with Windows or X/Windows capability (Microsoft Windows, Linux, HP-UX, Solaris etc.) and supporting a Java Machine 1.3.1; 50MB HDD, 128MB RAM (256MB recommended). For the agent station: Any OS supporting a Java Machine 1.3.1; 50MB HDD, 128MB RAM (256MB recommended). For the Repository Explorer: Any OS supporting a Java Development Kit 1.3.1; 20MB HDD, 128MB RAM (256MB recommended). For the repository: Any database engine supporting ANSI ISO 89 syntax, such as Oracle, Microsoft SQL Server, Sybase AS Enterprise, IBM DB2 UDB, IBM DB2/400, or the built-in Hypersonic SQL (HSQL) database; Space required for the repositories: Master repository: 30MB of storage space; Work repository: 40MB of storage space.

Supported Platforms: Sunopsis is written completely in Java and runs on any system supporting Java 2 Runtime Environment 1.3.

Supported Databases: Any JDBC- or ODBC-compliant database.

Supported Message-Oriented Middleware (MOM): Sunopsis MQ, MQSeries, SwiftMQ, Microsoft Message Queuing (MSMQ), SonicMQ, Oracle Advanced Queuing, any JMS-compliant MOM.

Packages supported: SAP, Oracle Applications, PeopleSoft, Siebel, JD Edwards OneWorld, any application supporting JCA or JDBC, any EDI-compliant application.

Pricing: Workgroup Edition, typically for an ETL project: $29,950; Real-Time Enterprise Edition, including Sunopsis MQ: $49,950; Real-Time Application Integration Edition, for EAI: $79,950.

Most of the standard components of the ETL process can be developed using the prebuilt Knowledge Modules (KMs) that ship with the Sunopsis package. KMs are included for reverse engineering, change data capture, data extraction, data integration, and cleansing. Commonly used scenarios such as incremental updates and loading of slowly changing dimensions can be developed easily using the appropriate KMs. KMs also help in automatically using standard database utilities such as bulk load and database links to speed up the load. Best of all, you can view the source code for the KMs and customize them to your needs. The source code language, again, isn't proprietary: The KMs use the features of the technology components you already have in your environment. For example, the KMs associated with Oracle use Oracle-specific SQL functions.

Many ETL vendors are also adding data quality solutions to their suites in an effort to build a complete data integration platform. Sunopsis can take a step in the right direction by enhancing its KM for data cleansing into a full-blown data quality solution. Such a KM can add real value if it includes matching, consolidation, and linking capabilities beyond the normal name and address standardization provided by most vendors.

Right for You?

Sunopsis v3 is an interesting data integration solution that dares to be different. It backs the power and flexibility of the relational database over the traditional engine-based architecture. A favorable pricing structure is the icing on the cake. If you're an IT shop that's well versed in relational database and SQL skills, it makes sense to take a good hard look at Sunopsis v3.

Ganesh Variar [[email protected]] is a lead analyst with Regence BlueCross BlueShield of Oregon. He has 10 years of experience in managing and designing business intelligence solutions.


Ascential Software

Business Objects


The Data Warehousing Institute

Evolutionary Technologies International





We welcome your comments on this topic on our social media channels, or [contact us directly] with questions about the site.
2 of 2
Comment  | 
Print  | 
More Insights
10 Trends Accelerating Edge Computing
Cynthia Harvey, Freelance Journalist, InformationWeek,  10/8/2020
Is Cloud Migration a Path to Carbon Footprint Reduction?
Joao-Pierre S. Ruth, Senior Writer,  10/5/2020
IT Spending, Priorities, Projects: What's Ahead in 2021
Jessica Davis, Senior Editor, Enterprise Apps,  10/2/2020
White Papers
Register for InformationWeek Newsletters
Current Issue
[Special Report] Edge Computing: An IT Platform for the New Enterprise
Edge computing is poised to make a major splash within the next generation of corporate IT architectures. Here's what you need to know!
Flash Poll