Going in Production - Expert Cube Development with SSAS Multidimensional Models - page 304

Processing reference dimensions
As we saw in Chapter 1 , Designing the Data Warehouse for Analysis Services and
Chapter 4 , Measures and Measure Groups , materialized reference relationships result
in a join being made between the fact table and the intermediate dimension table in
the SQL generated for partition processing. This highlights the important point that
materialized reference relationships are resolved during partition processing and not
dimension processing.
Apart from the negative impact that materialized relationships have on partition
processing performance, using them also carries a less obvious but more serious
penalty: if the intermediate dimension in the relationship ever changes its structure,
we have to run a Process Full on all of our partitions.
Consider the scenario we looked at in Chapter 4 , Measures and Measure Groups ,
where a Region dimension joins to a measure group through a Country attribute
on a Customer dimension using a referenced relationship. If that relationship is
materialized, during partition processing, the Customer dimension table will be
joined to the fact table and the key of the Country that each Customer lives in
will be stored in the partition. But what happens if we run a Process Update on
the Customer dimension and Customers change the Country they live in? The
materialized referenced relationship data on existing partitions will not be refreshed,
so Region values for these partitions will be incorrect. Even worse, if we then create
new partitions and run a Process Full on them, these new partitions will contain
the correct materialized relationship data, so Region values will be completely
inconsistent. Only a Process Full on all partitions can ensure that queries using the
Region dimension will return correct data.
This problem does not occur if the referenced relationship is not materialized. As a
result, this is one more good reason not to use materialized referenced relationships.
Handling processing errors
In an ideal world, Analysis Services processing would never fail. Certainly, as we
argued in Chapter 1 , Designing the Data Warehouse for Analysis Services , we should
always try to ensure that all data integrity issues are handled during the ETL for
our relational data warehouse rather than in Analysis Services. However, in the real
world, there will always be some problems with the data in our data warehouse, and
we need to configure Analysis Services processing to handle them.