Designing the Data Warehouse for Analysis Services - Expert Cube Development with SSAS Multidimensional Models - page 25

As long as all the joins are made on the primary keys, this will not lead to any
problems, but in cases where the JOIN is not made on the primary key, bad
performance might result. As we said before, if we succeed in the goal of exposing a
simple star schema to Analysis Services, we will never have to handle these JOIN s.
As we argue next, if a snowflake is really needed, we can still hide it from Analysis
Services using views, and in these views we will have full control over and knowledge
of the complexity of the query used.
Reference dimensions
Reference dimensions, when present in the cube definition, will lead to one of the
most hidden and most dangerous types of JOIN . When we define the relationship
between a dimension and a fact table, we can use the Referenced relationship
type and use an intermediate dimension to relate the dimension to the fact table.
Reference dimensions often appear in the design due to snowflakes or due to the
need to reduce fact table size.
A referenced dimension may be materialized or not. If we decide to materialize a
reference dimension (as SQL Server Data Tools will suggest) the result is that the
fact table query will contain a JOIN to the intermediate dimension, to allow Analysis
Services to get the value of the key for the reference dimension.
If JOIN s are a problem with dimension queries, they are a serious problem with
fact queries. It might be the case that SQL Server needs to write a large amount of
data to its temporary database before returning information to Analysis Services.
It all depends on the size of the intermediate table and the number of reference
dimensions that appear in the cube design.
We are not going to say that referenced dimensions should not be used at all, as there
are a few cases where reference dimensions are useful, and in the following chapters
we will discuss them in detail. Nevertheless, we need to be aware that reference
dimensions might create complex queries sent to SQL server and this can cause
severe performance problems during cube processing.
Fact dimensions
The processing of dimensions related to measure group with a fact relationship
type, usually created to hold degenerate dimensions, is performed in the same way
as any other dimension. This means that SELECT DISTINCT will be issued on all the
degenerate dimension's attributes.