Delivering Business Intelligence with Microsoft SQL Server 2005

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

problems.

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

makers.

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