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

We'll discuss many-to-many relationships in the next chapter because they are an
important topic on their own, but before that let's talk briefly about the relationship
types we've not seen so far.

Fact relationships
Fact or degenerate dimensions are, as we saw in Chapter 1 , Designing the Data
Warehouse for Analysis Services , dimensions that are built directly from columns in
a fact table, and not from a separate dimension table. From an Analysis Services
dimension point of view, they are no different from any other kind of dimension,
except that there is a special fact relationship type that a dimension can have with a
measure group. There are in fact very few differences between a fact relationship and
a regular relationship, and they are shown as follows:

• A fact relationship will result in marginally more efficient SQL being
generated when the fact dimension is used in ROLAP drillthrough;
a subject we'll discuss in much more detail in the next chapter.
• Fact relationships are visible to client tools in the cube's metadata, so client
tools may choose to display fact dimensions differently.
• A fact relationship can only be defined on dimensions and measure groups
that are based on the same table in the DSV.

• A measure group can only have a fact relationship with one database

dimension. It can have more than one fact relationship, but all of them
have to be with cube dimensions based on the same database dimension.

It still makes sense though to define relationships as fact relationships when you can.
Apart from the reasons given previously, the functionality might change in future
versions of Analysis Services and fact relationship types might be further optimized
in some way.

Referenced relationships
A referenced relationship is where a dimension joins to a measure group through
another dimension. For example, you might have a Customer dimension that
includes geographic attributes up to and including a customer's country; also, your
organization might divide the world up into international regions, such as North
America, Europe, Middle East and Africa, Latin America, and Asia-Pacific, for
financial reporting, and you might build a dimension for this too. If your sales fact
table only contained a foreign key for the Customer dimension, but you wanted
to analyze sales by international region, you would be able to create a referenced
relationship from the Region dimension through the Customer dimension to the
Sales measure group.