Database Reference
In-Depth Information
Junk dimensions
At the end of the dimensional modeling process, we often end up with some attributes
that do not belong to any specific dimension. Normally these attributes have a very
limited range of values (perhaps three or four values each, sometimes more) and they
seem to be not important enough to be considered dimensions in their own right,
although obviously we couldn't just drop them from the model altogether.
We have two choices, as shown:
•
Create a very simple dimension for each of these attributes. This will lead
to rapid growth in the number of dimensions in the solution; something the
users will not like because it makes the cube harder to use.
•
Merge all these attributes in a so-called
junk dimension
. A junk dimension
is simply a dimension that merges together attributes that do not belong
anywhere else and share the characteristic of having only a few distinct
values each.
The main reasons for the use of a junk dimension are:
• If you join several small dimensions into a single junk dimension, you will
reduce the number of fields in the fact table and the number of required joins
at query time. For a fact table of several million rows, this can represent a
significant reduction in the amount of space used and the time needed for
cube processing.
•
Reducing the number of dimensions will mean Analysis Services performs
better during the aggregation design process and during querying, thereby
improving the end user experience.
•
The end user will never like a cube with 30 or more dimensions; it will be
difficult to use and to navigate. Reducing the number of dimensions will
make the cube less intimidating.
However, there is one big disadvantage in using a junk dimension; whenever you
join attributes together into a junk dimension, you are clearly stating that these
attributes will never have the rank of a fully-fledged dimension. If you ever change
your mind and need to break one of these attributes out into a dimension on its own
you will not only have to change the cube design, but also reprocess the entire cube
and run the risk that any queries and reports the users have already created will
become invalid.
Search WWH ::
Custom Search