CHAPTER 10
Tuning Oracle Data Warehouse And OLAP Applications

As more companies begin to embrace the concept of creating a historical data repository for online analytical processing (OLAP) and decision support systems (DSS) applications, new client/server issues have emerged as developers struggled to create Oracle-based client/server applications that would perform acceptably. This chapter will review the data warehouse with a focus on creating fast Oracle applications. Topics for this chapter will include:

•  The evolution of data warehouses and online analytical processing (OLAP)

•  Tuning multidimensional databases

•  Tuning relational database warehouses

•  STAR schema design techniques

•  Tuning techniques for relational warehouses

•  Using middleware solutions to data warehousing

Data Warehousing And Multidimensional Databases

A great deal of interest has surfaced in the application of data warehousing and multidimensional databases to advanced systems. These systems, including expert systems and decision support systems, have been used to solve semistructured and even unstructured problems. Traditionally, these types of systems combine inference engines and relational databases in order to store the knowledge processing components. Unfortunately, very little work has been done with the application of warehouse databases for decision support and expert systems.

Expert Systems

Expert system is a term that is used very loosely in the computer community regarding anything from a spreadsheet program to any program which contains an IF statement. In general terms, an expert system is one that models the well-structured decision process of the human mind, applying that reasoning process to a real-world situation. Any decision-making process with quantifiable rules can have the rules stored in an inference engine. An inference engine is used to drive the information-gathering component of the system, eventually arriving at the solution to the problem.

It has been said that an expert system makes a decision “for” the user, while a decision support system makes a decision “with” the user. This distinction is essentially true, because an expert system makes no provision for human intuition in the decision-making process. Many real-world management decisions do not require human intuition. For example, one of the crucial jobs of a retail manager is the choice of what goods to order, the quantity, and the ordering time frame. These decisions can be represented by a model called economic order quantity (EOQ). If the EOQ equation knows the velocity at which the goods are leaving the retail store, the delivery time on reorders, the average time on the shelf, and the cost of the goods, the computer can confidently produce automatic daily reports specifying which goods to order and the appropriate quantity. Also, a DSS can presummarize data so that the manager can quickly take a high-level look at the relevant figures, as shown in Figure 10.1.

Figure 10.1  Defining different levels of aggregation.

Decision Support Systems And Data Warehouses

Decision support systems (DSS) are generally defined as the type of system that deals with the semistructured problem. In other words, the task has a structured component as well as a component that involves human intuition. The well-structured components are the decision rules that are stored as the problem-processing system. The intuitive, or creative component, is left to the user.

The following represent some examples of semistructured problems:

•  Choosing a spouse

•  Building a factory

•  Choosing magazine artwork

•  Building a new sports car

•  Designing a Graphical User Interface (GUI) for an OODBMS

Decision support technology recognizes that many tasks require human intuition. For example, the process of choosing a stock portfolio is a task that has both structured and intuitive components. Certainly, rules are associated with choosing a stock portfolio, such as diversification of the stocks and choosing an acceptable level of risk. These factors can be easily quantified and stored in a database system, allowing the user of the system to create “what-if” scenarios. However, just because a system has well-structured components does not guarantee that the entire decision process is well-structured (see Figure 10.2).

Figure 10.2  Comparing decision support with expert systems.

One of the best ways to tell if a decision process is semistructured is to ask the question, “Do people with the same level of knowledge demonstrate different levels of skill?” For example, it is possible for many stock brokers to have the same level of knowledge about the stock market. However, these brokers clearly demonstrate different levels of “skill” when assembling stock portfolios.