MDX Solutions: With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase, Second Edition

Chapter 12. The Many Ways to Calculate in Microsoft Analysis Services

Microsoft Analysis Services 2000 provided many different ways of calculating a cell, and not much guidance on which one to choose for your application. Microsoft Analysis Services 2005 has added a new way of MDX programming, via MDX scripting. In this chapter, we describe the mechanics and interactions of the "traditional ways of calculating," while the description of MDX scripting is left to another chapter. An important difference between the two approaches is that, while Scripts can only be defined at the server, some of the techniques described here can be defined and/or modified within user sessions or even per query. We will provide a few simple tips to keep in mind when applying them.

The ways to calculate a cell are

1. Intrinsic aggregation for a measure

2. Rollup by custom unary rollup operator

3. Custom member formula

4. Calculated member

5. Cell calculation

We have described calculated members in some detail already because they are part of standard MDX and useful in almost every application. We haven't discussed the intrinsic aggregation for a measure very much, but then again it's simple enough almost to take for granted. (It's also something that you don't have any control over in MDX, except when creating a local cube using Microsoft's CREATE CUBE statement.) We'll discuss the custom rollups in some depth in this chapter. The custom member or custom rollup formulas are MDX expressions, whereas the unary rollup operators ...