What is dimensional modeling with examples

Business Intelligence (Part 3): Data Modeling - Relational and Multidimensional Models

The star scheme

The star schema is a special form of the multidimensional data model. The scheme has become the standard for mapping multidimensional data structures in relational databases and is mainly used in data warehouses and OLAP applications.

The star schema tries to minimize the large number of tables that are typical in the relational model. The term star schema comes from the fact that the tables are arranged in a star shape.

A distinction must be made between two different types of tables. At the center is the fact table, around which several dimension tables are grouped. Both are symbolized by rectangles. The fact table is used to store the numbers or derived quantities, such as sales or costs. From the perspective of the cube, it contains the core of the cube. The dimension tables contain the qualitative data for visualizing the dimensions and dimension hierarchies.

The individual rows of a dimension table are identified by a minimal combination of attributes, the primary key. To establish the relationship between the dimension tables and the associated fact tables, the primary keys of the dimension tables are included in the fact table as foreign keys and there in turn form the primary key of the fact table. The following figure illustrates this.

The following figure shows a concrete example of the star scheme.

The aim of the star scheme is not normalization, but rather an optimization for efficient read operations. In the star scheme, the dimension tables are therefore denormalized: there are functional dependencies between non-key attributes, so that the third normal form is violated. This violation is accepted, however, because the data structure enables a better processing speed at the expense of data integrity and storage space.