Previous Next  

  Overview: Dimensional Modeling

Online Transaction Processing (OLTP) Databases such as the AdventureWorks data source or other ERP and CRM systems typically use what’s known as Third Normal Form, or 3NF. This model attempts to reduce duplication as much as possible by “snowflaking” dimensions out into a large number of tables. For example, customer information would be grouped into smaller "categories" of information such as personal details, contact details, organizational details and so on.

However, this “snowflaking” of tables is extremely inefficient for analysis simply due to the large number of joins(relationship between two tables) that need to be established every time a query(question in the form that a database can understand) is executed. Instead, the Dimensional Model uses what’s called Denormalized Flattened Dimensions combined with the fact tables in a structure known as the Star Schema. This is where all the related table attributes are brought together into a single table and hierarchical descriptive information is stored redundantly.

It is important to note that, in recent years, the performance issues addressed by denormalization has largely been addressed by in-memory technology such as columnstore indexes. The primary reason why denormalizing database tables is still important is because it is much easier to query and understand.

In the AdventureWorks OLTP database, Customer data is split out into the following tables: Store, Person, EmailAddress, and Customer. In the following lessons, we will bring the necessary tables into the database and denormalize these "snowflaked" attribute tables into one single customer table.