Designing More Complex Dimensions - Expert Cube Development with SSAS Multidimensional Models - page 64

If we modeled our new dimension using meaningless surrogate keys, we would
have to perform a lookup during our ETL to find out which band each row in the
fact table fell into and assign it the appropriate surrogate key, as shown in the
following diagram:

ID Order Value OR OR RANGE

9,815

1 2 1 LOW

2 890 1 2 MEDIUM

25,000

3 3 3 HIGH

But what would happen if the user changed the bandings? If a user does this, then
we would have to reload our entire fact table, because potentially any order might
now fall into a new banding. A more flexible approach is to hardcode only the
granularity of the bandings into the fact table: for example, we could say that our
bandings could only have boundaries divisible by €1,000. This would then allow us
to use an expression in our fact table ETL such as Floor(OrderValue/100) to create
a meaningful key; in our dimension, we would then create one row per €100 up to
what we think the maximum value of an order might be, and then group these €100
ranges into the bandings our users wanted, as follows:

OR RANGE

1 LOW

ID Order Value OR 2 LOW

... ... ...

9,815 98

1

2 890 8 8 LOW

... ... ...

3 25,000 250

98 MEDIUM

... ... ...

250 HIGH

The advantage of this is that so long as the granularity of the bandings doesn't
change, we will never need to reload our fact table. In Analysis Services terms,
this dimension would have two attributes: one built from the meaningful key,
and one to hold the name of the band; a Process Update would be all that was
necessary when the banding boundaries changed because only the dimension
table would have been changed.