Performance Optimization - Professional SQL Server Analysis Services 2005 with MDX - page 637

achieving better performance during processing or querying. This sec-
tion discusses cube optimization design techniques.

Fact Table Measure Groups or Partitions

When you run cube wizard on a DSV containing multiple fact tables, the wizard
creates a separate measure group for each fact table identified. However, such a
UDM may or may not be the right design for your business analysis. For example,
if you have two fact tables, salesfact2005 and salesfact2006, which have the sales
information of your business for the years 2005 and 2006 (containing identical
columns), the cube wizard will be creating two measure groups. However, for your
purposes these should actually be modeled within the same measure group as two
partitions so that appropriate data can be rolled up. If you are creating the cube
using the Cube Wizard, select one of the fact tables in the cube wizard table selec-
tion page, and then the other fact table can be added as a partition to the measure
group. If you select both tables by mistake and the Cube Wizard has already cre-
ated two measure groups for the two fact tables, you can delete one of them from
the Cube Designer and add that table as a partition in the partitions tab. A way to
think about this is a Measure Group contains the union of all partitions.

You can have fact data spread across multiple tables. For business reas-
ons it might make sense to have all the fact data within the same meas-
ure group. Consider a measure group as an entity within your UDM that
represents a set of measures which are grouped logically for business
reasons; or in Analysis Services terms, share the same dimensionality
and granularity. Hence even if you have fact data spread across multiple
tables and for business reasons, you actually need to combine the data;
make sure you have them added within a single measure group. You
can join the fact tables containing measures into a single view within the
DSV and create a measure group from that, or you can add measures
from either of the fact tables within a single measure group using the
Cube Designer.

Optimizing Reference Dimensions

If your UDM contains reference dimensions, you need to be aware of
making optimizations for the reference dimensions. If you are querying
a reference dimension that is not optimized then you might not get the
best query performance. Figure 13-9 shows the relationship definition