Creating a star schema does not necessarily mean that all dimensions will be linked to the fact table. There are certain specific situations in which a link between two dimensions can be established.
Prerequisites

- All the fundamental concepts

Context

An outrigger dimension is a dimension that is indicated by another dimension. Using an outrigger dimension enables two dimensions to be analyzed without consulting a fact table. It should be implemented for specific purposes:

  • Establishing a link between one dimension and the current status of an associated profile contained in a mini-dimension
  • Separating a proportion of the attributes in a voluminous dimension

Separating a proportion of the attributes in a voluminous dimension

Let’s take the example of a SALESREP dimension (see figure opposite) containing information about a company’s sales staff. This dimension contains a large number of attributes. Two sets of attributes relating to locations (reporting_location and work_location) and dates (hire and review) can be observed.

Maintaining the consistency of the information contained in this dimension is a major concern in this situation, compounded by the fact that it is also possible for information concerning certain locations and dates to be managed by other ETL processes. This creates a context in which multiple ETL processes target the same dimension.

In this context, it may be wise to eliminate the groups of attributes that are repeated and store them in outrigger dimensions. Here, this applies to DAY and LOCATION (see figure below).

This type of solution calls for caution, because it entails the application of a normalization technique whereas we are seeking to adopt a non-normalized approach. Normalization has the effect of complicating queries and reducing performance during the inquiry. However, this solution is acceptable in this specific context.

Reading Outrigger dimension for managing voluminous dimensions: Star Schema. Christopher Adamson. pages 163-169

Reading Outrigger dimension: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. pages 170-171, 169, 202

Prerequisites

- Mini-dimension

The schema below, already discussed in the mini-dimension section, covers situations in which a mini-dimension becomes the outrigger dimension for the dimension that it streamlines in order to maintain the information about the current value of the profile contained in the mini-dimension.

Reading Outrigger dimension for managing a mini-dimension: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. page 169