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

A single dimension will hold attributes with different SCD types since not all the
attributes of a single dimension will need to have historical tracking. So, we will end
up with dimensions with some Type 1 attributes and some Type 2 attributes. How
do we model that in the data mart?

We basically have the following choices:

• We can build two dimensions; one containing the historical values and

one containing the current values. The historical dimension would contain
only the historical values of attributes, while the actual dimension would
contain all the current attribute values. Obviously, we will need two different
dimensional tables in the data mart to hold the two dimensions.
This solution is very popular and is easy to design but has some
serious drawbacks:
° The number of dimensions in the cube is much larger. If we have
several Type 2 dimensions, the number of dimensions might reach
the point where we have usability problems.
° In order to query for the actual value and the historical value of an
attribute, Analysis Services needs to resolve the relationship between
the two dimensions via the fact table, and for very big fact tables, this
might be very time-consuming. This issue is not marginal because,
if we give users the actual and historical values of an attribute, they
will always want to mix the two attributes in a single query to check
how the actual value has been changing over time.

• We can build a complex dimension holding both the actual and historical

values in a single dimensional table. This solution will lead to a much more
complex ETL to build the dimensional table, but solves the drawbacks of
the previous solution. For example, having both the current and historical
attribute values in a single dimension can lead to better query performance
when comparing these two values, because the query can be resolved at
the dimension level and does not need to cross the fact table. Also, as we've
stressed several times already, having fewer dimensions in the cube makes it
much more user-friendly.

Bridge tables or factless fact tables
We can use the terms bridge table and factless fact table interchangeably—they
both refer to the same thing; a table that is used to model a many-to-many relationship
between two dimension tables. Since the name factless fact table can be misleading,
and even if the literature often refers to these tables as such, we prefer the term bridge
table instead.