Snowflakes, Stars, and Analysis Services

The snowflake 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 snowflake 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 snowflake and the star schemas, we have to calculate aggregates on the fly 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 significant 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.