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

• The surrogate key of a dimension will be surfaced in MDX in the unique

names generated for members on the key attribute of the dimension. Since all
Analysis Services client tools save their queries using these unique names, this
means that if—for any reason—we change the surrogate key of a member in
the future (for example, because we have a "one shot" data warehouse that is
reloaded each night), the saved queries will no longer work.

• Junk dimensions : Junk dimensions, when defined at the conformed

dimension level, can cause problems when there is the need to update
data. If, for example, we need a new attribute on a junk dimension, this
can involve a change in the meaning of the existing members of the junk
dimension. This will invalidate the older facts, requiring a careful rebuilding
of the junk dimension. In our opinion, it is better to maintain the junk
dimensions as separate dimensions in the relational data mart and then
merge them into a single dimension for Analysis Services, creating a key
attribute that uses a composite key made up of the keys of all the dimension
tables that make up the junk dimension.

Therefore, the conclusion is that although surrogate keys are very useful and we are
not saying that there is something wrong with them, in some well-defined situations
it makes sense to deviate from the standard recommendations for surrogate keys and
use different forms instead.

The last consideration is that—even in cases where we deviate from the standard—
the usage of surrogate keys of the smallest integer type possible is always strongly
advised. The Analysis Services engine is optimized for the handling of integer values
and does not handle string and date values anywhere near as well.

Unknown Members, key errors, and
NULLability
When designing a data mart, questions often arise about the relationship between
the fact table and the dimensions. Should the foreign keys be NULLable or not?
Should we use the built-in foreign keys of SQL Server to handle the relationship?
What about key errors?

Since these are very interesting topics, let us discuss them in more detail:

• Can we use NULLable foreign keys columns? The answer is definitely no.

First of all, NULLable columns take space in the fact table, one bit for each
NULLable column. So, if we have two TINYINT foreign keys in the fact table,
we will end up consuming three bytes: two for the columns and one for the
two null bits. Since fact tables need to be as small as possible, NULLable
columns definitely waste space.