7.3. Physical Design Considerations

Up to this point in this chapter we've been discussing the logical design process for the Analysis Services OLAP database. We've recommended that you work with a small subset of data so that you can concentrate on the structure, calculations, and other cube decorations, without worrying about the physical design.


Readers who are familiar with Analysis Services 2000 will already be familiar with most of the terminology for the physical storage and processing of the Analysis Services database. This sidebar merely defines these concepts. Recommendations and implications are discussed in detail elsewhere in the chapter.

  • Leaf data: Leaf data is the finest grain of data that's defined in the cube's measure group. Usually, the leaf data corresponds exactly to the fact table from which a cube's measure group is sourced. Occasionally you'll define a measure group at a higher grain than the underlying fact table, for example by eliminating a dimension from the measure group.

  • Aggregations: Pre-computed aggregations are analogous to summary tables in the relational database. You can think of them as a big SELECT... GROUP BY statement whose result set is stored for rapid access.

  • Data storage mode: Analysis Services supports three kinds of storage for data.

    • MOLAP: Leaf data and aggregations are stored in Analysis Services' MOLAP format.

    • HOLAP: Leaf data is stored in the relational database, and aggregations are stored in MOLAP ...

Get The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.