Category: Data Modeling Comparison

During the development cycle of data model, several changes/updates may take place over time depending upon the needs and requirement of that project. As and when required, different data model versions will be created with little or more changes between them. At one point of time, one may require to know the exact differences between different versions(older version and newer version) of data models. Knowing these will help in assuring the updates in the newer version are correct and staying intact with the needs of the requirement. Also you can apply these changes to the underlying database by creating “ALTER” scripts. In that case, data modeling tools can help us to sort out the differences between data models.

With the help of a data modeling tool you can compare:

between two data models

data model with the script

data model with the underlying database

Significance of comparing data models:

To know the differences between them Or to know what changes have been taken place in the latest version.

Whatever differences are there, we can create scripts and the same can be implemented on the database.

In this article, we are using Toad software to compare between two different data model versions as well as to create ALTER scripts for database update.

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

Logical | Physical Data Modeling:

When a data modeler works with the client, his title may be a logical data modeler or a physical data modeler or combination of both. A logical data modeler designs the data model to suit business requirements, creates and maintains the lookup data, compares the versions of data model, maintains change log, generate reports from data model and whereas a physical data modeler has to know about the source and target databases properties.

A physical data modeler should know the technical-know-how to create data models from existing databases and to tune the data models with referential integrity, alternate keys, indexes and how to match indexes to SQL code. It would be good if the physical data modeler knows about replication, clustering and so on.

The differences between a logical data model and physical data model is shown below.