Home > ETL Best Practices > Difference between 3NF and dimensional models

Difference between 3NF and dimensional models

Majority of the operational databases are in 3NF (3rd Normalized Form). Data warehouse, on the other hand, uses dimensional models. What really is the key difference between 3NF and dimensional models?

As I understand it’s the degree of normalization. Remember both model types can be presented as ERDs (Entity Relationship Diagram). Normalized models, however, are too complicated for data queries. RDBMSs can’t query a normalized model efficiently.

The following are some texts from the book “The Data Warehouse Toolkit”.

Dimensional modeling addresses the problem of overly complex schema in the presentation area. A dimensional model contains the same information as a normalized model.

If the presentation are is based on a relational database, then these dimensionally modeled tables are referred to as star schema.

If the presentation are is based on multidimensional database or OLAP technology, then the data is stored in cubes.

Dimensional modeling is applicable to both relational and multidimensional database. Both have a common logical design with recognizable dimensions; however, the physical implementation differs.

so I might be wrong by referring to our Asset Management database an operational database. The data can be updated through the client application, but in general, our data updates are managed-load updates, not transactional updates.

Categories: ETL Best Practices
  1. Ajendra
    March 4, 2011 at 6:06 am

    This is my thought on the difference between a 3NF and Dimensinal schema.

    A Dimensinal view may or may not be in 3NF. But the primary key should always be a number.

    In a 3NF it should be in 2NF and 1NF. But in a dimensinal view, the data can be stored in 3NF fashion but not valdidating the 1NF.

    eg Every attributes may not be atomic but every attributes may depend on the PK

    Like

  1. May 11, 2011 at 8:58 am
  2. July 4, 2011 at 5:47 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: