Sybase Warehousing

In keeping with this month's theme of data warehousing, I would like to take a look at Sybase Inc.'s (Emeryville, Calif.) warehousing solution. In the company's data warehousing white paper ("Sybase Interactive Warehouse," www.sybase.com/products/dataware/iwwpaper.html), Sybase defines data warehousing as the process of integrating information from a variety of sources, at regular intervals, to construct an integrated view of the business, combined with query-optimized technologies for interactive analysis.

Sybase has a whole range of products as part of its data warehouse offering. They include:

• Database servers optimized for the special query requirements of the interactive warehouse: Sybase SQL Server for mixed-mode data warehouses (databases that are updated and queried at the same time), Sybase MPP for huge systems with terabytes of data and thousands of users, SQL Anywhere Professional for mobile and personal data marts, and Sybase IQ for decision support and complex data analysis.
• Middleware for access to multiple data sources on heterogeneous systems: With the Enterprise Connect family of products, you can access more than 25 data sources, including IMS, VSAM, DB2, IDMS, Oracle, Informix, and AS/400.
• Integration and management of data movement and distribution within the enterprise warehouse environment: InfoPump, Sybase's low-end data movement option, performs summaries and aggregations to the data before delivery to the warehouse. This is useful for smaller data warehouses and data marts. Sybase's Replication Server is appropriate for a large enterprise's interactive warehouse. It provides automated, high-performance, high-volume data distribution and management within highly heterogeneous and dynamic interactive warehouse environments.
• Development tools: PowerBuilder, Power++ (formerly Optima++), PowerJ, InfoMaker, and S-Designor, which was recently renamed PowerDesigner. InfoMaker is an end-user query and reporting tool designed for personal access to warehouse data for reporting. PowerDesigner (version 6, which should be released by the time you read this) has been enhanced to include Warehouse Architect. It has a Warehouse model design capability and a direct interface into Sybase IQ.

Sybase IQ

Sybase IQ was designed for interactive data warehousing. Architected around Sybase's patented Bit-Wise query-processing technology, it uses advanced performance-optimized algorithms for complex ad hoc queries. Sybase claims that this technology offers five to 500 times faster ad hoc query performance than traditional relational database management systems (RDBMSs), even with changing and ad hoc user queries. Sybase also claims that Sybase IQ requires much less resource-intensive tuning to obtain excellent performance than a traditional RDBMS requires.

Sybase IQ fits in the Sybase Open Architecture. Sybase IQ supports a collection of heterogeneous data sources (including accessing data directly from Sybase SQL Server or, through a gateway, from any of more than 20 supported data sources such as Oracle, DB2, Informix, and flat files). It can be accessed from a wide range of popular, off-the-shelf query tools (such as Business Objects and Cognos Impromptu). Sybase IQ also supports a standard relational framework. It processes standard SQL commands and supports Sybase's Open Client interfaces, DB-Library, Client-Library, and ODBC client calls. Its integrated catalog can serve both Sybase SQL Server and Sybase IQ, allowing DBAs to manage security and other administrative services consistently throughout the Sybase environment. It uses the standard SQL Server data definition language, with extensions for the Sybase IQ access methods.

This architecture can even be used to allow transactions and queries designed for execution by an operational data store to be passed through and executed transparently by an attached database. This lets you build high-performance environments that support both interactive analysis and realtime updates.

Features

Bit-Wise Indexing

Sybase IQ's Bit-Wise indexing resolves queries by efficiently combining and manipulating only the relevant data, without scanning large amounts of raw data; often it does not access any raw data at all. Because Sybase IQ's Bit-Wise indexing accesses only the minimum amount of data needed to resolve each query, Sybase IQ can rapidly process almost any query, whether preplanned or ad hoc. Accessing the data stored in a column-oriented format eliminates accessing and processing irrelevant data. Because most decision-support system (DSS) queries access a relatively small number of columns across a large number of rows, this technique eliminates a great deal of unnecessary I/O. In a worst-case scenario, Sybase IQ would scan an entire column rather than do a full table scan.

The Bit-Wise indexing technique reduces the index data structures to as little as 20 percent of the size required for the equivalent B-tree index used by a traditional RDBMS. Its data reduction and compression techniques enable the entire database to be fully indexed on each column and made available for ad hoc query in less space than it normally takes to store the raw data alone. It also does not require the ongoing maintenance tasks, as do most other relational indexing techniques.

The Bit-Wise indexing is flexible. It lets you add new data values to the data structures without needing to rebuild or reorganize these structures. This ensures fast access to current information. With the Bit-Wise indexing technology, Sybase IQ can resolve queries without tuning. The Bit-Wise technology can handle ad hoc queries with little optimization. Sybase IQ resolves queries using only information that is directly relevant to each query. Administrators need not define all of the specific queries and operations in advance to provide high performance. The access methods are data-dependent, not query-dependent, so data warehouse users can pose an unlimited number of complex ad hoc queries without needing query-specific tuning by the DBA.

Compression

Large Block I/O

Database Access Methods

The query optimizer automatically picks the best available access methods and algorithms for executing each individual query. By intelligently combining all of the capabilities I described earlier, Sybase IQ performs efficiently and consistently across the vast array of complex queries typical of decision-support analysis, without manual intervention.

Parallelism

The nonblocking parallel-processing architecture of Sybase IQ 11.1 can take full advantage of all of the available processors in a symmetric multiprocessing (SMP) environment when performing resource-intense activities, such as data loading. The parallel multithreaded loader can read multiple source files to bypass the 2GB file-size limit still found in many operating systems. This allows huge amounts of data to be loaded in a single run.

In addition, the Sybase IQ 11.1 architecture can build all of the data structures that are required to support ad hoc queries at 2GB to 4GB per hour or more on midrange SMP machines. More important, this architecture enables multiple processors to share query tasks and permits the system to execute many simultaneous queries with little or no performance degradation.

Data Loading

Prejoin Indexes

Relational and Multidimensional Data

Smaller Memory Footprint

• Reduced per-thread memory: Sybase IQ uses native operating system kernel threads to provide opportunistic parallelism, which allows it to take advantage of parallel processing to speed up compute-intensive tasks when doing so will not adversely impact other users. By restructuring how each thread uses memory, savings of between 5MB and 10MB per user have been achieved over previous releases.
• Reduced sort-merge memory: Sorting is used by many operations in complex query processing. Sybase IQ 11.1 uses a new sort-merge technique that significantly reduces the memory utilization of each sorting task.
• Sybase workspace: Sybase IQ 11.2 lets all users share a single workspace so that extensive queries can draw from a pool of memory at run time. This relieves the DBA from calculating and allocating memory per user in advance.

Join Between Private and Shared Index Spaces

The objects in the private index space are referenced using standard SQL Server db.owner.table.column(index) syntax for compatibility with a wide range of tools. Disk allocation for the private index space is preallocated and will provide excellent performance and will support private index spaces larger than the 2GB limit imposed by many operating systems.

Performance Enhancements

1. Group by: High-performance grouping is essential for many queries found in DSSs. Sybase IQ 11.1 uses new algorithms -- based on the number of groups, number of distinct elements, and other factors -- to choose among its execution strategies. This results in improved performance on a wide range of grouping queries.
2. IN list: OLAP tools make extensive use of the IN (...) SQL construct, frequently with a very large number of entries in the list. Sybase IQ 11.1 has taken a new approach to this problem by combining column-oriented processing with hashing techniques to improve the performance of IN lists containing thousands of values.
3. Subquery: Subqueries or queries containing other queries often cause performance problems in some relational databases. Sybase IQ 11.1 introduced several new approaches to subquery execution that draw strongly on its column-oriented processing and its ability to use the cache to emphasize reuse of the consistent portions of each query expression.
4. Improved costing model: Sybase IQ 11.1 has significantly improved the costing portion of the query optimizer. These changes provide the optimizer with better information on how to choose an optimal execution plan for each query. Choosing the best execution plan is critical to the performance of ad hoc join queries.
5. New join algorithm: One of the most popular data models for the data warehouse today is called the star schema. A star schema consists of a central table (called the fact table) surrounded by a series of smaller tables (called dimension tables). In a star schema, there are millions of fact records that consist of only a few numeric columns and a relatively small number of dimension table records that provide meaning to the facts. Some relational join algorithms do a poor job of joining the relatively small dimension tables with the very large fact table, causing very slow query performance. Sybase IQ 11.1 has a new hash join algorithm that provides a significant improvement in join queries over the traditional relational join methods when used on star schema queries.

Using Sybase IQ

Once the data is loaded, users can query any aspects through most of the popular query and reporting tools, including Microsoft Access (Microsoft Inc., Redmond, Wash.), Impromptu (Cognos Inc., Ottawa, Ontario, Canada), BusinessObjects (Business Objects Inc., Cupertino, Calif.), Forest & Trees (Platinum Technology Inc., Oakbrook Terrace, Ill.), and SAS Assist (SAS Institute Inc., Cary, N.C.). Access through Sybase's CT-Lib and db-Lib is also supported, along with ODBC.

After the data has been loaded and set up, there is very little administration and tuning to be done. Because the Sybase IQ warehouse is pretuned according to the nature of the data and not according to the nature of the queries, the system does not have to be retuned as the nature of the queries evolves over time.

A New Playing Field

However, no matter how a data warehouse is described, it is still a process, not an off-the-shelf product. A successful data warehouse, be it a conventional data warehouse or a Sybase IQ interactive data warehouse, requires an architecture and a plan for an end-to-end solution. This architecture is the foundation for delivering useful, reliable, accurate, and timely information to business users. However, the functionality provided by the warehouse server and the speed by which it provides information to its users can significantly improve its usefulness.