A Potted History of Business Intelligence

All of these techniques and procedures, including dimensional design, grew out of a need. This is appropriate, of course, but surprisingly rare in our industrymore often, perceived need lags technical capability. At any rate, once the majority of operational systems had been computerized, people realized that the data stored in OLTP systems would be useful in the managerial decision-making process, but the data wasn't accessible.

Printed reports are the traditional method of providing information based on OLTP data. Unfortunately, it's difficult to predict the parameters of analysis reports in advance. Ad hoc reporting is problematic, the problem being that most reporting tools are not really intended for end users. I don't want to be in the room when you suggest that the Marketing Manager of a major corporation learn Crystal Reports or the Microsoft Access report writer, and as report writers go, these two are wonderfully intuitive.

Of course, there's always Microsoft Excel. I'd be prepared to bet that at least 90 percent of business analysis is done using Microsoft Excel, simply because it's the only game in town. It works, but not as effectively as one would hope. (I will admit to a certain prejudice here. Excel is a mathematical tool, not a data tool, and I rather resent the amount of my life I've had to spend explaining that to people.)

In the first place, we still have the problem of normalized schemas. If you restrict business analysts and managers to pre-defined queries, you may be restricting the analysis process unacceptably. But if you let these users have full access to the raw data, you risk kneecapping the system.

Remember that the OLTP systems in question are in use for transaction processing. That means that one analyst running a Cartesian product of three tables can bring the organization to a complete halt. Even in the best of circumstances, many of the questions analysts and managers want answered require large (slow) joins that place an unacceptable burden on the system.

It's obviously unacceptable to prevent users in the Southwest region from entering orders because the system is busy answering questions about orders in the Southwest region.

Data warehouses were designed to address these problems. In a data warehouse, data from multiple OLTP systems is extracted, "scrubbed," and made available to analysts and managers in an easily-understandable form. Dimensional design represents the current thinking regarding the best method for presenting that data.

Until the advent of SQL Server 2000, data warehouses were obscure, expensive, and usually restricted to large corporations. Dimensional analysis was not considered part of the mainstream of relational database design, despite the fact that the majority of data warehouses were implemented on relational databases.

But as has always been the case, now that business intelligence is available on the desktop, it is becoming integrated into mainstream application design. I urge you to consider it where appropriateeven small organizations can benefit from a better understanding of their operations.

And I promise that I am not employed by Microsoft's Marketing department. It's simply that I'm old enough to understand just how revolutionary this is...rather like replacing typewriters with word processors. (And I'm old enough to remember that, too!)