OLAP for Not Yet Techies

OLAP stands for Online Analytical Processing. The online actually refers to its processing, not to being on the Internet. OLAP is a complicated and not easily understood plan for querying databases to obtain useful information.

OLAP could be said to go back to relational databases. Recall that flat databases are simply two-dimensional tables of data. They have their uses but in the 1970s computer scientists realized that electronic rather than paper databases could deal with a lot more complexity that would be more useful and also more efficient than flat databases.

So OLAP began with relational databases which relate the data in different flat databases. That is, say you're a business. You have a database listing basic information about customers. You could also include a field for the last purchases that relates to other databases listing all their purchases, plus databases of sales data etc.

When spreadsheet programs such as 1,2,3 appeared they seemed to offer much of the same functionality of combining information within cells to transform data into more useful information. However, they are inefficient and limited in that respect. So the next step to OLAP is SQL.

Structured Query Language does help people get useful information from databases but has its own limits. For instance, it cannot transpose rows and columns. So OLAP goes beyond SQL.

Databases are two dimensional - composed of vertical rows and horizontal columns. Relational databases add a 3rd dimension through the related variable column, making a box or cube.

OLAP can extend that to as many dimensions as necessary to obtain the information needed. We can't visualize dimensions beyond the 3rd physically but that's not necessary. Computers don't care that the extra "dimensions" don't exist physically - that's just a metaphor to help us visualize the relationships.

OLAP uses Multidimensional Type Structures (MTSs) for this purpose, and the Multimensional Expressions (MDX) language.

OLAP is fast and now supports XML.

The real challenge, however, lies in asking OLAP the right questions. Users must figure out exactly what information would be useful to them and then structuring that is a technical issue. But if the users ask the wrong questions they will get the wrong answer.

It's amazing now, but until quite recently, companies did not keep many records on their customers for marketing purposes. They kept financial records of transactions for accounting, financial and tax purposes, but could not tell you a year later whether they had the most sales with men or women, in Illinois or Missouri, among the rich or poor, except in obvious product-related issues.

Perhaps that a certain cologne is bought by more men than women in a certain city. This is known as data mining.

OLAP changes these capabilities and now companies with good sense get and keep and process as much customer information as possible. For instance, when I was a phone psychic I was paid 25 cents extra for getting the caller's address and phone number, so the company could market to them later. I'm also sure that the company kept track of where the callers came from, to target their TV infomercials.

The possibilities are nearly endless. They do raise some privacy concerns but companies basically just want to sell more of their products and services to the most profitable customers in their marketplace. OLAP enables them to focus their marketing on these people.

Next: Pocket PC