Comparison of Relational and Dimensional Data Modeling

Relational | Dimensional Data Modeling:

Relational Data Modeling is used in OLTP systems which are transaction oriented and Dimensional Data Modeling is used in OLAP systems which are analytically based. In a data warehouse environment, staging area is designed on OLTP concepts, since data has to be normalized, cleansed and profiled before loaded into a data warehouse or data mart. In OLTP environment, lookups are stored as independent tables in detail whereas these independent tables are merged as a single dimension in an OLAP environment like data warehouse.

Relational vs Dimensional Data Modeling

Relational Data Modeling

Dimensional Data Modeling

Data is stored in RDBMS

Data is stored in RDBMS or Multidimensional databases

Tables are units of storage

Cubes are units of storage

Data is normalized and used for OLTP. Optimized for OLTP processing

Data is de-normalized and used in data warehouse and data mart. Optimized for OLAP

Several tables and chains of relationships among them

Few tables and fact tables are connected to dimensional tables

Volatile(several updates) and time variant

Non volatile and time invariant

SQL is used to manipulate data

MDX is used to manipulate data

Detailed level of transactional data

Summary of bulky transactional data(Aggregates and Measures) used in business decisions