Why build a dimensional database?

This type of informational processing is known as online analytical processing (OLAP) or decision support system (DSS) processing. OLAP is also the term that database designers use to describe a dimensional approach to informational processing.

A dimensional database needs to be designed to support queries that retrieve a large number of records and that summarize data in different ways. 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 certain products sell best? In what regions are sales weakest?

In a dimensional data model, the data is represented as either facts or dimensions. A fact is typically numeric piece of data about a transaction, such as the number of items ordered. A dimension is the reference information about the numeric facts, such as the name of the customer. Any new data that you load into the dimensional database is usually updated in a batch, often from multiple sources.

Relational databases are optimized for online transaction processing (OLTP) are designed to meet the day-to-day operational needs of the business. OLTP systems tend to organize data around specific processes, such as order entry. The database performance is tuned for those operational needs by using a normalized data model which stores data by using database normalization rules. Consequently, the database can retrieve a small number of records very quickly.

Some of the advantages of the dimensional data model are that data retrieval tends to be very quick and the organization of the data warehouse is easier for users to understand and use.

If you attempt to use a database that is designed for OLTP as your data warehouse, query performance will be very slow and it will be difficult to perform analysis on the data.

Many of the problems that businesses attempt to solve 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 an OLTP database. However, a dimensional database could process the same queries in a fraction of the time.

Besides the characteristic schema design differences between OLTP and OLAP databases, the query optimizer typically should be tuned differently for these two types of tasks. For example, in OLTP operations, the OPTCOMPIND setting (as specified by the environment variable or by the configuration parameter of that name) should typically be set to zero, to favor nested-loop joins. OLAP operations, in contrast, tend to be more efficient with an OPTCOMPIND setting of 2 to favor hash-join query plans. For more information, see the OPTCOMPIND environment variable and the OPTCOMPIND configuration parameter. See the Informix® Performance Guide for additional information about OPTCOMPIND, join methods, and the query optimizer.

also supports the SET ENVIRONMENT OPTCOMPIND statement to change OPTCOMPIND setting dynamically during sessions in which both OLTP and OLAP operations are required. See the Informix Guide to SQL: Syntax for more information about the SET ENVIRONMENT statement of SQL.

Informix is designed to help businesses better leverage their existing information assets as they move into an on-demand business environment. In this type of environment, mission-critical database management applications typically require combination systems. The applications need both online transaction processing (OLTP), and batch and decision support systems (DSS), including online analytical processing (OLAP).