Chapter 3: Searching for the Source—The Source of Business Intelligence 41
Snowflakes, Stars, and Analysis Services
The snowfl ake schema has all the advantages of good relational design. It does not
result in duplicate data and is, therefore, easier to maintain. It also looks more proper
to those of us who have been working with relational databases.
The disadvantage of the snowfl ake design is that it requires a number of table
joins when aggregating measures at the upper levels of the hierarchy. In larger data
marts or in data marts that experience heavy utilization, this can lead to performance
In both the snowfl ake and the star schemas, we have to calculate aggregates on
the fl y when the user wants to see data at any level above the lowest level in each
dimension. In a schema with a number of dimensions or with dimensions that have
a large number of members, this can take a signifi cant amount of time. The whole
idea of business intelligence is to make information readily available to our decision
We could calculate all the measures at every level of our hierarchy and store them
in the data mart. However, this would make the data mart much too complex and,
therefore, much harder to maintain. How do we get good performance from our
data mart at every level of the hierarchy without driving the data mart administrator
crazy? The answer is Microsoft SQL Server 2005 Analysis Services, as we see in
Chapter 4.
This page intentionally left blank

Get Delivering Business Intelligence with Microsoft SQL Server 2005 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.