Relational databases typically are optimized for online transaction processing (OLTP). OLTP systems are designed to meet the day-to-day operational needs of the business, and the database performance is tuned for those operational needs. Consequently, the database can retrieve a small number of records quickly, but it can be slow if you need to retrieve a large number of records and summarize data on the fly. Some potential disadvantages of OLTP systems are as follows:
In contrast, a dimensional database is designed and tuned to support the analysis of business trends and projections. This type of informational processing is known as online analytical processing (OLAP) or decision-support processing. OLAP is also the term that database designers use to describe a dimensional approach to informational processing.
A dimensional database is optimized for data retrieval and analysis. Any new data that you load into the database is usually updated in batch, often from multiple sources. Whereas OLTP systems tend to organize data around specific processes (such as order entry), a dimensional database tends to be subject oriented and aims to answer questions such as, "What products are selling well?" "At what time of year do products sell best?" "In what regions are sales weakest?"
The following table summarizes the key differences between OLTP and OLAP databases.
| Relational Database (OLTP) | Dimensional Database (OLAP) |
|---|---|
| Data is atomized | Data is summarized |
| Data is current | Data is historical |
| Processes one record at a time | Processes many records at a time |
| Process oriented | Subject oriented |
| Designed for highly structured repetitive processing | Designed for highly unstructured analytical processing |
Many of the problems that businesses attempt to solve with relational technology are multidimensional in nature. For example, SQL queries that create summaries of product sales by region, region sales by product, and so on, might require hours of processing on a traditional relational database. However, a dimensional database could process the same queries in a fraction of the time.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]