Operational Database vs. Data Warehouse

In this article, we will evaluate operational database vs. data warehouse. Operational database is live database which uses normalization, concurrency control to manage transactions, and have a recovery mechanism. It is used by OLTP (Online Transaction Processing) applications.

The data warehouse is kept separate from the OLTP databases. It is used by the OLAP( online analytical processing) applications. It is more about summarization and aggregation of data. If applied to a operational database, it will slow down the OLTP system.

Difference Between Operational Database and Data Warehouse

When we say operational database, we actually mean OLTP systems, and data warehouse means OLAP systems. Therefore, in the following table, you find feature-wise comparison of these two databases.

FeaturesOperational DatabaseData warehouse
UsersClients, Clerks, IT guysManagers, Executives, Analysts
System OrientationCustomer oriented, data-to-day operationMarket Oriented, Decision support
DataCurrent dataHistorical data
DB DesignE-R ModelStar or Snow flake Model
ViewFocus on current data, same organizationFocus on multiple versions of data,
heterogeneous sources
VolumeNot very largeHuge, stored in many disks
DB AccessShort, atomic transaction,
ACID property
Read-only access with complex queries
OperationsIndex/Hash on Primary keyLot of Scans
No. of recordsTensMillions
DB Size100 MB to GB100 GB to TB
PriorityHigh priorityHigh flexibility
Metric Transaction throughputQuery response time

Multi-Dimensional Data Model of Data Warehouse

A multi-dimensional model categories data as

  • either facts with numeric measures.
  • or as dimensions that characterize the facts and are mostly textual.

For example.

Product sold to customer in certain amount and price

Fact : purchase

Measures:- amount, price

Dimension:- location, type of product, time of purchase

Multi-dimensional data model is composed of logical cubes, measures, dimensions.

Multi-Dimensional Data Model
Multi-dimensional Data Model

Measures It populate the individual cells of a logical cube.

e.g. quantity of sales which is some number

Dimension They are ways to look at the measures. A cube has 3 dimensions. Each of the dimension has cells with some quantity as measures. One dimension may be location,another time, and so on.

Logical hierarchies They are ways to organize data at different level of aggregation. The members at each level have one-to-many and parent-child relationship.

e.g Quarter-I ( year 2018), Quarter-II (year 2018), Quarter-III(year 2018)

The quarter-i, quarter-ii, and quarter-iii are children of year 2018.

e.g Year -> Month -> Weeks -> Days -> Hours (5 levels)

Sales Target ->Area -> Sales Rep -> Individual Target (4 levels)

The hierarchy and levels have many-to-many relationship.

Logical Attributes It provide additional information about the data.

Multi-Dimensional Data Storage

The data cube represents all measures. Each dimension is the edge of the cube. They divide the cube into cells containing values or data.

Data Cube Storage
Data Cube Storage

The cube can rotate to give many view of same data. It also represents physical storage of measures, just like relations in OLTP database.

A measure can have more than three dimensions, but pictorially, cube has only 3 dimensions and additional dimensions requires additional cubes.

Schema For Multi-Dimensional Database

The relational implementation of the multi-dimensional data model is a

  • A Star Schema
  • A Snowflake Schema
  • A Fact Constellation Schema

We will discuss the architecture of data warehouse in future lessons. In this article, you learned that the data warehouse is different in all aspects compared to operational database.