Definition of Dimensional Modeling
Dimensional Modeling is a logical design technique that seeks to present the data in a standard, intuitive framework that allows for high-performance access. It is inherently dimensional, and it adheres to a discipline that uses the relational model with some important restrictions. Every dimensional model is composed of one table with a multipart key, called the fact table, and a set of smaller tables called dimension tables. Each dimension table has a single-part primary key that corresponds exactly to one of the components of the multipart key in the fact table.
The graphic indicates a simplified example of a fact table (Product) and its associated dimension tables (Division, Department, Class, Item, UPC, and Subclass).
Fact Tables and Dimension Tables
The structure of a dimension model somewhat resembles that of a crude drawing of a star or snowflake (see the following graphics ).
In a dimensional model, fact tables always represent M:M relationships (see Many-to-Many Relationships). According to the model, a fact table should contain one or more numerical measures (the “facts” of the fact table) that occur for the combination of keys that define each tuple in the table.
Dimension tables are satellites of the central fact table. They typically contain textual information that describes the attributes of the fact table.
Star Schema
The following graphic illustrates the classic star schema:
Snowflake Schema
The following graphic illustrates the classic snowflake schema:
The E-R Model Versus the DM Model
While a table in a normalized E-R-derived database represents an entity and its relevant atomic descriptors, tables in a DM-derived database represent dimensions of the business rules of the enterprise. The meaning of business rule here is somewhat different from that used by writers in the business rules community, where the term applies to the declarative domain, range, uniqueness, referential, and other constraints you can specify in the database.
While advocates of implementing a normalized physical schema emphasize the flexibility of the model for answering previously undefined questions, DM advocates emphasize its usability because the tables in a DM database are configured in a structure more akin to their business use.
The E-R model for an enterprise is always more complex than a DM model for the same enterprise. While the E-R model might have hundreds of individual relations, the comparable DM model typically has dozens of star join schemas. The dimension tables of the typical DM-derived database are often shared to some extent among the various fact tables in the database.