Dimensions That Change

After you've selected the type of dimension to be used for your particular business scenario, another important concept is whether and how your model will support update behavior for the dimensional attribute values. To understand how to model for updates, consider that updates in SSAS need to be divided into two types: new values (or inserts) and changes (or updates or deletes of existing attribute values). Allowing inserts of new values for the dimension or fact tables does not require you to use any special modeling techniques. However, the business rules required for changes to dimensional attributes will affect the way you model those attributes.

In Chapter 2, we introduced the topic of modeling slowly (or rapidly) changing dimensions. At this point in your BI solution, you now have six considerations. These considerations include modeling for the rate of change (that is, slow or rapid), and configuring error definitions (that is, “Are changes allowed at all?” or “Are nulls allowed?”). The options and support for updating the various types of dimensions via the SSIS slowly changing dimension transformation are listed next. The reason for this last point is that you will probably want to automate the process of dimensional attribute updates via SSIS packages.