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 denormalized and used in datawarehouse 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 |
| Normal Reports | User friendly, interactive, drag and drop multidimensional OLAP Reports |
well said explanation