Microsoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 7
This chapter, from some viewpoints, covers the heart of what people want from Analysis Services: measures. As the chapter mentions, the measure is the simplest dimension with only one attribute, Measure. This attribute, by design, is not aggregatable (a property typical of other types of dimensions). In statistical terms, a measure is the dependent variable, and the attributes within other types of dimensions are the independent variables.
Within Analysis Services, measures have certain properties which are optimized for calculation performance:
- DISTINCT COUNT
In many, many discussions on performance, I have talked with people who want to do more than just these functions (with optimal performance), such as standard deviation (or variance) and logarithm. Adding more functions can affect overall cube performance, and as the chapter mentions, even with the current list, the DISTINCT COUNT option takes a lot of resources (pages 80 and 81).
Figure 7.3 on page 82 covers an important concept: not all dimensions need to be connected with every measure. As I mentioned in an earlier chapter, these linkages also need not be the complete set of all possible linkages from a source relational system. The goal of strong Analysis Services design starts with what the business questions are and proceeding from that point. The cube design is intentionally optimized to be a subset of all possible designs. Choosing what dimensions to link to measures should be logically possible, and on top of the core logic, optimized for best performance. Thus, a designer might not include linkages from certain dimensions to certain measures to gain better cube performance.
The term granularity (page 84) defines how deep someone could drill through to source data. Again, granularity might, and maybe in most cases, should match the source data (perhaps from a relational system). However, Analysis Services optimizes data for analysis, and the original granularity need not match the designed granularity inside a cube. I have built analysis systems in SAS where the granularity did NOT match the source data, with the designed intention that people wanting that source data should go to the source rather than to the analysis structure. The chapter talks about the tradeoffs of determining where the granularity should be.
Page 87 talks about what happens when someone changes a dimension’s granularity (and those words would be a better title for this section). The effects depend on the hierarchy. Because multiple hierarchies can be included, Analysis Services looks for the shortest path to an attribute connected to a measure. Thus, the authors include this advice: “it’s a best practice to avoid alternative paths in the attribute tree altogether” (Page 89). Alternative paths can happen when design happens over time, or among a team of designers. Anything put into a cube, whether measures or dimensions or hierarchies, comes with a performance price, and perhaps might introduce ambiguity too.
Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.