Kimball’s Dimensional Modeling

Operational database vs. analytical database

In every organization, information serves two purposes: operational record-keeping and analytical decision-making. Operational systems store operational data, such as taking online orders, resolving customer tickets, and collecting website traffic. Data is generated and continually updated in real-time as the business proceeds.

Operational databases, also known as Online transaction processing databases (OLTP), are optimized for processing transactions and providing the best application performance to users. A common practice is to store only the most recent state of objects without keeping the history. Additionally, operational databases extensively use normalized 3NF structures because an update or insert transaction only affects one table in the database.

While operational databases prioritize performance, they are not the optimal choice for facilitating decision-making. Business users often deal with multiple transactions at once, for instance, comparing data from different time periods and manipulating it in various ways. Using normalized data models only introduces unnecessary complexity because users must navigate and remember all the models and build complicated join logic themselves, which can hinder efficiency and impede company growth.

Analytical databases, also known as online analytical processing (OLAP) databases, are optimized for handling complex SQL logic. This capability is a result of the underlying parallel processing engines, query optimization techniques, and elastic hardware resources. In contrast to OLTP databases, which only preserve the latest object state, OLAP databases maintain the historical data to support various analyses spanning an extended timeframe. Moreover, external-facing tables are typically denormalized in OLAP databases because we want to provide an efficient and simplified way for users to retrieve data.

In this lesson, we will focus on modeling data in OLAP databases. Among all the data modeling techniques, Kimball's dimensional modeling is well-accepted as the favored approach for presenting analytical data within ...