Relational Data Modeling - Denormalization

Data System Architecture

About

Denormalization is the process of intentionally backing away from normalization to improve performance by suppressing join and permitting the use of star transformation technique.

Denormalization should not be the first choice for improving performance and should only be used for fine tuning of a database for a particular application.

How to denormalize ?

See Relational Data Modeling - Denormalization Methods (Changing the Database Design to Reduce the Number of Joins)

Why Denormalization in OLAP applications ?

Databases intended for Online Transaction Processing (OLTP) are typically more normalized than databases intended for Online Analytical Processing (OLAP). Normalization tends to multiply the number of tables, as it involves splitting out functionally dependent groups of attributes into separate tables. Normalization results in databases that are optimized for update rather than retrieval.

OLTP Applications are characterized by a high volume of small transactions such as updating a sales record at a super market checkout counter.

The expectation is that each database transaction will leave the database in a consistent state because each change can be made in a single place and so preventing insertion, deletion, and update anomalies

By contrast, databases intended for OLAP operations are primarily “read mostly” databases. OLAP applications tend to extract historical data that has accumulated over a long period of time. For such databases, redundant or “denormalized” data may facilitate business intelligence applications by avoiding join and permitting a star transformation method.

The normalized alternative to the star schema is the snowflake schema. It has never been proven that this denormalization itself provides any increase in performance, or if the concurrent removal of data constraints is what increases the performance. In many cases, the need for denormalization has waned as computers and RDBMS software have become more powerful, but since data volumes have generally increased along with hardware and software performance, OLAP databases often still use denormalized schemas.

Denormalization is also used to improve performance on smaller computers as in computerized cash-registers and mobile devices, since these may use the data for look-up only (e.g. price lookups). Denormalization may also be used when no RDBMS exists for a platform (such as Palm), or no changes are to be made to the data and a swift response is crucial.

Requirements

  • Prior normalization
  • Knowledge of data usage
    • How often are two data items needed together
    • How many rows are involved
    • What is the minimum response time and frequency of an query

Benefits

  • Minimize the need for joins
  • Reduce number of tables
  • Reduce number of foreign keys
  • Reduce number of indices





Discover More
Data Modeling Chebotko Logical
Cassandra NoSql Database

Cassandra is a NoSql database for transactional workloads that require high scale and maximum availability. Cassandra is suited for transactional workloads at high volume and shouldn’t be considered...
Oltp Dwh
Data Warehousing - Contrasting OLTP and Data Warehousing Environments

One major difference between the types of system is that data warehouses are not usually in third normal form (3NF), a type of data normalization common in Online Transaction Processing (OLTP) environments....
Star Schema
Dimensional Data Modeling - Dimension (Perspective)

A dimension is a part of a dimensional schema and provide the basis for analyzing data through slicing and dicing A dimension is just a set of descriptif attribute that are generally grouped: in a...
Star Schema
Dimensional Data Modeling - Dimensional Schemas

This section is dimensional data modeling, That is the building of a cube (hypercube) A dimensional schema is a schema based on dimension. The dimensional schema can modeled: in a relational database...
Obiee Combine With Similar Request
OBIEE 10G - Reporting on Multiple Subject Area (Advanced Logical SQL)

In 11G, you can easily joins two subject areas. This article shows advanced logical SQL statement for the sake of knowledge and was written on a 10G because within an answer, it was possible through theBI...
Data System Architecture
Relational Data Modeling - Denormalization Methods (Changing the Database Design to Reduce the Number of Joins)

You can reduce the number of joins performed during queries by denormalizing the data within your application. In a normalized database, the attributes of a table relate directly to the fullprimary kedenormalized...
Data System Architecture
Relational Data Modeling - Normal Forms

A relational database is : in First Normal Form (1NF) if each attribute is single-valued with atomic values. in Second Normal Form (2NF) if it is in 1NF and each attribute that is not a primary key...
Obiee Pivot Rolap Densification With No Fact
System OLTP vs Data Warehouse RDBMS vs OLAP Server

System OLTP Data Warehouse RDBMS OLAP Server Purpose System charter Operational Historical and detail data Analytic Access Access type Read/write Read-only Read/write Access mode Atomic,...



Share this page:
Follow us:
Task Runner