Cube Metadata

Suppose you decide to design a cube before you create the data warehouse to support it—
which, incidentally, you can do in Analysis Services 2005. First, you select a measure—say,
Sales Amount. Next, decide what dimensions you would like for that measure, and at what
level of detail—say, Product by Customer, by Date. This defines the grain for the measure.
Finally, decide if there are any other measures that have the same grain—perhaps Sales Units.
You would then create a measure group that contains all the measures that have the same
dimensions at the same grain.
Suppose you select a new measure requiring a different grain. For example, suppose you want
Sales Target to have product categories by calendar quarter by scenario. This measure does
not have the same grain as Sales Amount and Sales Quantity, so you create a new measure
group. If there are any other measures that require the same grain as Sales Target, you can add
them to the same measure group.

 

A measure group is simply the group of measures that share the same grain. When you go to
build your data warehouse, you would create a separate fact table for each measure group.
Conversely, if you already have a data warehouse with several fact tables, you simply create a
measure group for each fact table.
A cube is then the combination of all the measure groups. This means that a single cube can
contain measures with different grains. This pushes the meaning of cube even further from its
geometrical origins. Perhaps you can visualize a cube as a cluster of crystals of varying sizes
and shapes, many of which share common sides. In this new way of thinking, a single cube
can contain all the metadata for all the data in your data warehouse. Because of this, a cube is
now sometimes called a Unified Dimensional Model, or UDM. Sometimes a cube has more
information than is manageable by a single person. For example, a procurement manager may
not care about how sales discounts are applied. Analysis Services allows you to create a perspective
that is like a cube that contains only a subset of the measures and dimensions of the
whole cube. You can create as many perspectives as you want within a cube.
A cube is a logical structure, not a physical one. The same is true for a measure group. It
defines the metadata so that client tools can access the data. You define measures and dimensions,
and specify how measures should be aggregated across the dimensions.
Conceptually, each measure group contains all the detail values stored in the fact table, but
that doesn’t mean that the measure group must physically copy all the detail values from the
fact table. If you choose, you can make the measure group dynamically retrieve values as
needed from the fact table. In this case, you’re using the measure group only to define metadata.
This is called relational OLAP, or ROLAP. For faster query performance, you can tell the
measure group to copy the detail values into a proprietary structure that allows for extremely
fast retrieval. This is called multidimensional OLAP, or MOLAP. Analysis Services allows you,
as the cube designer, to decide whether to store the values as MOLAP or ROLAP. Aside from
performance differences, where the detail values are physically stored is completely invisible
to a user of a cube. Whether you use MOLAP or ROLAP, values are stored in a memory cache—
on a space-available basis—to make subsequent queries faster. You can think of MOLAP storage
as a disk-based cache that allows the Analysis Server to load the memory cache much
faster than if it had to go to the relational database.(p25)