Is online analytical processing for you?

Every day we create reams of data in customer relationship management applications, order entry applications, and warehouse management systems. We're drowning in a sea of data. However, even with all that data we don't have a large amount of information. We have the ones and zeros of the transactions, but we don't have the answers we need to simple questions like:

In the 1990's, the talk was about decision support systems and executive dashboards. The maturation of those concepts is realized in online analytical processing (OLAP). OLAP is designed to convert data into usable information by allowing the aggregation of data. This process allows you to answer questions like these—even when you don't know what characteristics may be important to the question. What is OLAP?
To understand what OLAP is, you must first understand a few OLAP terms. OLAP works on facts, and facts are numbers. A fact could be a count of orders, the sum of the order amounts, or an average of order amounts.

OLAP organizes facts into dimensions which are ways that the facts can be broken down. For instance, total sales might be able to be broken up by geography. Similarly, total sales might also be broken down by time. Dimensions are also hierarchies of levels. For instance, a geography dimension might contain the levels of country, state, and county. Similarly, a time dimension might be broken down by year, month, and day.

In using OLAP tools, you typically arrange one or more dimensions along the rows as well as the columns. You then place one or more facts in the data portion of the grid. The result looks very similar to a cross-tab spreadsheet. In our example above, perhaps we would have geography along the rows and time along the columns. The end result would be something that looks like Figure A.

A final OLAP term that you should know is cubes. Cubes are collections of facts and dimensions. In practical terms, you'll probably have a separate cube for production data and sales data because there is not a one-to-one relationship between all the facts in sales information and the production information. Within a single cube every fact must have a one-to-one relationship.