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 ModelingDimensional Data Modeling
Data is stored in RDBMSData is stored in RDBMS or Multidimensional databases
Tables are units of storageCubes are units of storage
Data is normalized and used for OLTP. Optimized for OLTP processingData is denormalized and used in datawarehouse and data mart. Optimized for OLAP
Several tables and chains of relationships among themFew tables and fact tables are connected to dimensional tables
Volatile(several updates) and time variantNon volatile and time invariant
SQL is used to manipulate dataMDX is used to manipulate data
Detailed level of transactional dataSummary of bulky transactional data(Aggregates and Measures) used in business decisions
Normal ReportsUser friendly, interactive, drag and drop multidimensional OLAP Reports

One thought on “Comparison of Relational and Dimensional Data Modeling

Leave a Reply

Your email address will not be published. Required fields are marked *