Wednesday, February 3, 2010

Dimensional and Relational: Not Opposites

A common misconception holds that the terms dimensional and relational are opposites. They are not.

The word "dimensional" describes a design method. The word "relational" describes a data storage technology.
  • A dimensional model is a design approach that describes  a process in terms of measurements (known as facts) and their context (dimensions)
  • A star schema is a dimensional model implemented using relational storage technology—that is, in a relational database (RDBMS)
  • A cube is a dimensional model implemented using multi-dimensional storage technology—that is, in a multidimensional database (MDB)
This simple diagram illustrates these concepts:

As you can see from the diagram, a star schema is both relational and dimensional. So is a snowflake schema.

By the way, don't let this confuse you:  most modern day DBMS products accommodate both kinds of storage.