Intelligent Enterprise

Better Insight for Business Decisions

RSS
Webcasts
Whitepapers
Subscribe
Home

January 1, 2002

Lost, Shrunken, And Collapsed

Why and how to create the three types of aggregates in a dimensional data warehouse

By Lawrence Corr

Continued from Page 1

For a snowflake schema, you can often create a shrunken aggregate by replacing the surrogate key of a snowflaked dimension with the key to one of the dimensions' outriggers. Figure 3 shows how this shrunken aggregate might look. Notice how an outrigger of the fact table becomes a directly attached shrunken dimension of the aggregate. The query to populate this type of aggregate from a snowflake would be the same as from a star schema.

Shrunken dimension aggregates, whether from stars or snowflakes, have the advantage of helping you balance performance and usability. Their main disadvantage is the additional maintenance demands of the shrunken dimensions and surrogate keys, although these efforts are often necessary anyway to match the grain of other stars such as planning or budget facts.

COLLAPSED DIMENSION AGGREGATES

Collapsed dimension aggregates are created when dimensional keys have been replaced with high-level dimensional attributes, resulting in a single, fully denormalized summary table. Figure 4 shows a collapsed aggregate with a small number of surviving dimensional attributes from two dimensions. This example could be a quarterly product category sales summary.

Collapsed dimension aggregates have the performance and usability advantages of shrunken dimension aggregates, without requiring you to maintain shrunken physical dimensions and keys. In addition, they can offer further query acceleration because they cut out join processing for rewritten queries. However, you can consider them for high-level summaries only where few dimensional attributes remain and those attributes are relatively short. Otherwise, the increased record length may contribute to the collapsed table being too large, especially if many attributes are included. A collapsed dimension aggregate might well have 10 times fewer records than the fact table but its record length could easily be three to five times longer, leaving the overall performance gain at only two or three times.

Collapsed dimension aggregates have the same build complexity as shrunken dimensions - including the need to be rebuilt, rather than refreshed, to avoid problems with dimensional attribute maintenance. Collapsed dimension aggregates would be tenable only for high-level summaries built from already moderately sized aggregates. It makes most sense to use these completely collapsed fact tables as independent bases for high-level reporting applications, rather than expect them to be woven seamlessly by applications into an aggregate navigation hierarchy.

Rate This Article

Comments:

Optional e-mail address:

BUILDING AGGREGATES

For each of the aggregation techniques, I described how they might be populated via a SQL query. In databases that offer built-in summary table facilities, there are associated facilities for defining the aggregates and registering them for aggregate navigation. Also, using features such as Oracle 8i materialized views, if available, to build and maintain your aggregates is straightforward as long as batch performance is not an issue for you.

If you have a large number of aggregates to refresh each night and are squeezed for time, you may find it more efficient to perform the aggregation outside your database and process the updates explicitly using an ETL tool. If you are in this situation, consider tools such as Cognos Inc.'s DecisionStream that specialize in loading fact tables and aggregates in parallel in one pass of the source data, or SyncSort Inc.'s Sigma, which adds SQL connectivity and summary maintenance to SyncSort's breathtaking sorting and aggregation performance.

Guest columnist Lawrence Corr [lcorr@decisionone.co.uk] designs large data warehouses through DecisionOne Consulting, based in the United Kingdom.