The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

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.

CUBE PHYSICAL STORAGE TERMINOLOGY 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 ...