Relational model
The relational model is used in transactional systems where many transactions are executed, most of them concurrently. A transaction inserts, updates or in any other way processes data in a database. In many occasions a transaction is an integral part of the business process. As an example, a bank transaction is a business transaction and at the same time a transaction of its transactional information system. The relational model must serve the transactional system in the best way. Since the transactional system executes many transactions, the relational model has to trace the execution of each transaction in the system. In other words, each transaction has to be traceable throughout the data model. In some way such a data model is a process flow model because the data in the flow of business process is modelled.
Dimensional model
The dimensional model is used in the decision support systems or the data warehousing systems. The data in the decision support systems represents the effects of the business process. They are the effects of many transactions executed in the transactional system. As opposed to the transactional system in the decision support system the trace of the transactions is not visible. Only the effects of their execution have to be visible. The dimensional model is therefore an informational model because the data of the effects of the business process is modelled.
Difference between relational and dimensional modelling
Relational Modeling
Data modelling of transactional systems
Execution of business process → process flow modelling
Data is normalized and used for OLTP
Tables are units of storage
Several tables and chains of relationships
Volatile and time variant
Used for Normal reports
Dimensional modelling
Data modelling of decision support system
Effects of business process → process effect modelling or informational modelling
Data is de-normalized and used in Datawarehouse and data mart, optimized for OLAP.
Cubes are units of storage
Few tables and fact tables are connected to dimensional tables
Non-volatile and time invariant
Interactive drag and drop multidimensional User Friendly OLAP reports