Measures and Measure Groups - Expert Cube Development with SSAS Multidimensional Models - page 108

When setting up a referenced relationship in the Define Relationship dialog in the
Dimension Usage tab, you're asked to first choose the dimension that you wish to
join through and then which attribute on the reference dimension joins to a certain
attribute on the intermediate dimension, as shown in the following screenshot:
When the join is made between the attributes you've chosen on the reference
dimension, once again it's the values in the columns that are defined in the
KeyColumns property of each attribute that you're in fact joining on.
The Materialize checkbox is automatically checked, and this ensures maximum
query performance by resolving the join between the dimensions at processing
time—which, as mentioned in Chapter 1 , Designing the Data Warehouse for Analysis
Services , can lead to a significant decrease in processing performance. Unchecking
this box means that no penalty is paid at processing time but query performance may
be worse.
The question you may well be asking yourself at this stage is: why bother to use
referenced relationships at all? It is in fact a good question to ask, because, in general,
it's better to include all of the attributes you need in a single Analysis Services
dimension built from multiple tables rather than use a referenced relationship.
The single dimension approach will perform better and is more user-friendly; for
example, you can't define user hierarchies that span a reference dimension and its
intermediate dimension.