Professional SQL Server™ Analysis Services 2005 with MDX

9.1. Measure Groups and Measures

In Chapter 6, you learned about measure groups and measures within a cube. To recap, a cube can contain one or more measure groups and each measure group can contain one or more measures. You also learned about the various aggregation functions for each measure, and reviewed some MDX examples of how measure values are rolled up while browsing the cube. In this section, you'll learn how to use an MDX function to simplify querying measure groups and how to group measures within a measure group to help users navigate them more easily. You'll also learn how to use properties to control how measure values are aggregated when unrelated to dimensions in the same query or when performing currency conversions. Lastly, you'll learn how to reuse measure groups in multiple cubes.

With Analysis Services 2005 it is quite possible to end up with a cube containing several measure groups. If you open the Adventure Works cube in the Enterprise version of the Adventure Works DW sample project (located at C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Enterprise) you see 11 measure groups for the Adventure Works cube as shown in Figure 9-1.

Figure 9.1. Figure 9-1

If you expand each measure group, you can see that most measure groups contain multiple measures. For example, the Internet Sales measure group has 9 measures ...