When it comes to data warehouse query performance, speed is everything. Faster performance means needed data and analysis is available to act upon ­ quickly. In order to achieve this goal, data warehouse experts agree that data aggregates can be a very powerful tool. According to Ralph Kimball, expert and author of The Data Warehouse Lifecycle Toolkit, data warehouse administrators can "expect anywhere from a tenfold to a thousandfold improvement in runtime performance by having the right aggregates available."

Aggregates are pre-stored summary records held in a data warehouse. They are usually built from the base layer upward, culminating in high-level totals. For example, a data warehouse administrator may decide to build aggregates for sales based on product, store and time-related totals such as month and year. Tables of aggregates eliminate the time and resources required to summarize data each time it is needed to respond to a query, allowing the query to be answered very efficiently in just a few seconds.

We have benefited from the use of data aggregates at Kraft Food's New Jersey-based business (including the Biscuit, Confections and Snacks Divisions) which generates an enormous amount of customer, manufacturing and sales data. These Kraft businesses include such icon brands as Oreo cookies, Ritz crackers, Life Savers candies, Altoids mints and Planters nuts.

The data aggregates are used by our sales, finance and marketing staffs to analyze both customer SKU and manufacturing data for these divisions. They consist of current year-to- date as well as the previous two years' sales information. To create these aggregates, we used a combination of UNIX joins and SyncSort for UNIX running on an IBM RISC 6000 Silver Node with four gigabytes of memory and four processors. The computer system stored our extensive facts.

Although this process was providing the results we needed, we realized that there was a potential problem. We were limited in the amount of time we had to create the aggregates but were receiving a growing number of requests. With a batch window of only five to six hours a night, we needed a faster methodology to build the multiple new aggregates that are continually required by our users. After an extensive review of the products available, we decided that Syncsort's Sigma offered the best solution. This high-performance utility allowed us to create and manage data aggregates rapidly, easily and accurately.

We chose Sigma because of its demonstrated capabilities and because of Syncsort's reputation for high-quality products and service, which we've experienced firsthand during years of using their data management products. We run Sigma on an IBM RISC 6000. The computer system utilizes AIX 4.4.3, and the warehouse database features Informix Red Brick Release 6.0.2.

Sigma's GUI front end makes it fast and easy to create new aggregates and review or modify existing ones. We use Sigma to build a total of 13 aggregates that consist of current and two prior years of sales history.

Since its implementation, Sigma has helped us to reduce the amount of time it takes to define and maintain aggregates by up to 40 percent and has dramatically cut response time for database queries. With this approach to creating new aggregates, we are confident that we have the ability to provide the queries that we need in the fastest and most efficient way.

Sigma is Syncsort's high-performance utility for creating and managing data aggregates rapidly, easily and accurately. Because Sigma is a specialized tool, it summarizes data much faster than other aggregation methods such as C programs, SQL statements or third-party multipurpose warehouse packages. Performance improvements of 25 percent or more are possible when Sigma creates aggregates.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access