Annotations on Dimensional Schemas

Context

On this page, we shall be discussing the annotations proposed by the authors of the BEAM✲ approach presented in Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema. Laurence Corr and Jim Stagnitto. DecisionOne Press, 2011 (see Appendix B, pages 287 to 292). Although you will find a large number of dimensional schemas without these annotations, we advise you to take the time required to add them to your dimensional schemas in order to simplify their interpretation by experts.

All of these annotations are put in [] either to the right of the table, or to the right of each attribute.

Types of fact table

When designing your dimensional schema, it is essential to identify the type of fact table that you are creating. See the different types of fact tables.

• TF (Transaction Fact Table) for tables of transaction facts.
• AS (Accumulating Snapshot fact table) for fact tables providing an “instantaneous summary”.
• PS (Periodic Snapshot fact table) for fact tables providing an “occasional impression”.

Fact table attributes

Keys are the primary attributes of fact tables. See the different types of keys. Annotations are entered to the right of each attribute

• SK (Surrogate Key) for dimension keys.
• BK (Business Key) for business keys.
• NK (Natural Key) for natural keys.

However, it should be noted that certain attributes are degenerate dimensions, which must be entered as DD.

Next, the additivity property of all the facts in the fact table (the remaining attributes) should be shown as follows:

• FA (Fully Additive Fact)
• SA (Semi-Additive Fact)
• NA (Non-Additive Fact).

Modes of change for dimensions

Dimensions may change over time. You may encounter cases of slowly changing dimensions, very large dimensions, or rapidly changing dimensions.

You must therefore determine the manner in which each of the dimension attributes changes

• FV (Fixed Value) for attributes which should not change (except for data-entry errors), such as dates of birth.
• CV (Current Value) for a type-1 change which overwrites the value.
• HV (Historic Value) for a type-2 change which backs up each modification by adding new rows.
• PV (Previous Value) for a type-3 change which backs up the latest modification by adding a column.

If the dimension exclusively possesses attributes that do not change, enter FV to the right of the name of the table, and enter this type if all attributes share this same change mode. If there are several change modes, use:

• xx/xx (replace xx with one of the above-mentioned codes)

Special characteristics of dimensions

Dimensions may have different characteristics, which should be stated alongside the type of change for the dimension.

• RP for role-play dimensions. These dimensions are referenced by several keys in the fact table, such as an Employee dimension which also identifies a leader and his/her subordinates.
• HM for dimensions implementing a variable-depth hierarchy.
• MV for multi-valued dimensions: bridge tables or variable-depth hierarchies with multiple heritage HV/MV.