Entity Relational Modeling
vs.
Dimensional Modeling

Dimensional Modeling

• Dimensional Data Modeling comprises of one or more dimension tables and fact tables.
• Good examples of dimensions are location, product, time, promotion, organization etc.
• Dimension tables store records related to that particular dimension and no facts(measures) are stored in these tables.
• Adheres to a discipline of Relational Model
• High Performance Access
• Dimensions Star Join with Fact
• Fact – Multi Part Primary Key , Many to Many Relation
• Dimensional modeling is a top-down design process.
  • First you identify the main business processes that act as the sources of the fact tables
  • then you populate the fact tables with numeric, additive facts. You describe each fact record by as many business dimensions as you can identify.
• The resulting fact table records consist entirely of key values that have many-to-many relationships with one other, together with numeric data representing measurements of each dimension. Overall, the storage of the fact table records is quite efficient.
• The dimension tables represent the biggest departure from the usual ER and normalization techniques.
• It is important that the dimension tables remain as flat, single-level tables without being further normalized.
• This is the hardest design step for relational data modelers to accept.

Dimension Modeling Example

For example, Product dimension table will store information about products(Product Category, Product Sub Category, Product and Product Features) and location dimension table will store information about location( country, state, county, city, zip. A fact(measure) table contains measures(sales gross value, total units sold) and dimension columns. These dimension columns are actually foreign keys from the respective dimension tables.

Location dimension

Product Dimension

Organizational Dimension

Time Dimension

Back to Entity Relational Modeling   vs.  Dimensional Modeling Index