Decision Support technologies evolved considerably in the past few years and they are about to reach a plateau and enter a phase where they will mature. The next significant growth of those technologies will be achieved by merging the distinct decision support technologies as we know them today (OLAP/ROLAP, Ad Hoc Query/Report Writing and Data Mining) into an end-to-end solution for data consumers ranging from business analysts to senior executives.

The acronym OLAP stands for On-Line Analytical Processing and refers to interactive data analysis on a computer as opposed to browsing through stacks of thick printed reports.

Currently, OLAP tools are typically used by a small community, mainly composed of managers and executives, to analyze trends in their organization for general management and strategic planning. These users want to know whether: revenues and expenses are increasing or decreasing on a monthly or quarterly basis compared to the previous years; Are we meeting our objectives (Budget vs. Actual)?; Which regions or product lines have the most significant impact on our business?

The concept of OLAP first appeared in the late 1970s but it started gaining recognition in the 1980s with the appearance of the first significant OLAP applications, called EIS (Executive Information System). An EIS system was a programmed interface that allowed managers to easily track some key business indicators. The fact that the EIS application was programmed meant that the layout or presentation was rather static, users were able to navigate through the OLAP structure and data in a pre-determined fashion. EIS applications were host-based and only used desktop PCs as clients. The early 1990s marked the birth of desktop and client/server OLAP products. As OLAP applications evolved, they became more sophisticated, flexible and interactive.

A concept that is inseparable from OLAP is multidimensional analysis meaning that the OLAP structure can model multiple factors or variables. The business world is composed of a vast array of dimensions such as time (years, months), geography (countries, regions), business partners (customers, suppliers) and the products or services they exchange, see Table 1. A significant benefit from multidimensionality is that by storing the information in a structure similar to the business environment, it simplifies the process of business analysis by recreating a world that users can understand, which is generally not true of a relational database schema.

Even though the OLAP structure is multidimensional, our computer screens limit us to presentations in two dimensions. To navigate through the OLAP structure with the tools that we have, we slice and dice through the data. Basically, what this means is since we cannot visualize all the data at once, we will extract a piece of it from the structure, analyze that part and if that chunk is still too large to comprehend, dice it into even smaller pieces, see Table 2. Once we have isolated an area of interest, for instance Table 2 shows that sales of product B are declining in the East region, we want to extend our analysis and retrieve additional information pertaining to this focal point, this is called "drilling down" (see Table 3).

Categories of OLAP

The SQL language and the business language are different which means that some simple business problems or questions can be difficult to translate in a SQL statement. Users will rapidly run into roadblocks as they try to use those tools to answer questions such as :

Compare the revenues of this year vs. last year - to achieve this, the query tool will need to generate two statements, one for this year’s revenues and another for last year, then combine the results from both queries. This is called a multi-pass SQL.

What percent of customers have orders with more than 3 products? - This will require a multi-pass SQL as well as a correlated sub-query.

While many tools can now generate queries that will satisfy the first example, probably less than a handful of products will be able to help your users with the second one.

Data mining is part of a process called knowledge discovery where the goal is to better understand the organization’s data in order to resolve business problems or capitalize on opportunities. Some of the steps in the knowledge discovery process are defining the problems or opportunities you want to address, collecting, cleaning, preparing and analyzing the data and most importantly, interpreting the results. Data mining is the step where specific algorithms are applied to historical data in order to identify hidden patterns and relationships (correlations).

The origins of data mining date back to the 1960s and statistical analysis. Some of the algorithms used in those days were correlation, regression, chi-square and cross tabulation. In the 1980s, the field of artificial intelligence emerged and brought additional algorithms or techniques used to analyze data such as fuzzy logic, heuristic reasoning and neural networks.

OLAP and query tools help you manage your organization by letting managers and business analysts keep an eye on trends and visually identify hot spots in the data that require their attention or answer day-to-day business questions as they arise. Those tools help them answer questions like "What were the sales of product X last year and how were they distributed?". Data mining tools help answer questions like "Why did sales of product A decline after September?"

A major distinction between OLAP/Query tools and data mining is in the task distribution. With OLAP/Query tools the user navigates through the data but with data mining, the tools analyze the data and provide feedback to the users.

With the current proliferation of data marts and data warehouses, data mining is just starting to emerge. The impact of data warehousing on this field is that data mining tools work on historical data and as the amount and variety of data increase, the chances of accurately predicting the outcome of new transactions will also increase. This is true of any analysis based on statistical functions or algorithms. As the use of data mining increases, numerous business applications will surface such as the following :

Customer relationship management. Analysis of customer attrition (churn) is of great interest for most organizations who need to understand why their customers are turning to their competitors in order to increase customer retention and attract others from their competitors by understanding the characteristics of those customers who are ready to change provider.

Marketing. A common application of data mining is to improve profitability of direct-mail campaigns by focusing on the audience most likely to response and generate more significant revenues while at the same time reducing costs by limiting the scope of the campaign. Another popular application is market basket analysis which shows the relationships among items purchased at the same time.

Finance. Data mining is commonly used for credit risk analysis and fraud detection.

Healthcare. Applications can be used for tracking the effectiveness of medical procedures or detect side effects of various drug combinations.

Data mining tools create analytical models that are descriptive, predictive or both. Descriptive models help answer questions such as "Why did sales of product A decline last month?". Predictive models are used for forecasting and help answer questions like "How much revenue will be generated by the new product C?".

Data mining tools can model a number of different problems. The most common of these are :

Classification and regression - the most widely spread category of data mining is used to create models that predict class membership (classification) or a value (regression). Some business applications are to predict customer behavior or store profitability, detect potentially fraudulent transactions or identify candidates for medical procedures.

There are several techniques used for classification and regression including decision trees, neural networks, Naïve-Bayes and nearest neighbor. Classification and regression generate predictive models.

To illustrate the various techniques used, let’s build a simple example. A travel agency offers 2 vacation destinations : Disneyland and Europe. The agency’s database contains information regarding the customer name, the price paid, the number of children, the duration of the vacation and the destination selected. The destination is called the dependent variable which suggests that the selection will be based on the other columns, which are defined as independent variables implying that the price paid does not affect the number of children nor the duration of the vacation (in real life, we can all agree that they can all be related but for this example let’s pretend they are independent).

Some tools will strictly work with categorical data meaning that the variables must contain discrete values instead of continuous. For instance, the price paid for the vacation could be anywhere from $100 to $10 000. To convert those values into categorical data we apply a process called bracketing or binning. Basically, we group the values into a limited set such as moderate for a price between $100 and $3000 and high for the other values. For this example, we will also group the children information into no (0) and yes (1 or more) and the duration will be short (1 week or less) or long (more than 1 week). Table 9 illustrates the data from previous transactions.

Decision trees. A decision tree generates a model that is both predictive and descriptive. The data mining algorithm will analyze the data, generate a model presented in the form of a tree structure (this process is called tree induction) and display the output either graphically (see figure 1) or textually (see figure 2). The resulting output is very easy to understand and makes this technique very popular.

To predict the destination selected by a potential new customer (Bill) who wants to go on a long vacation with no children and has a high budget, simply follow the decision tree and you will predict that this customer will more than likely select the trip to Europe. On a larger scale, if the travel agency is planning to open a new office they can look at demographics information and organize an advertising campaign that will be adapted to the new prospect base.

Neural Networks. A neural network generates a model that is strictly predictive. They are very efficient and fast enough to be used for predictions in real time. The reasoning behind the output of a neural network is very hard to comprehend and is often referred to as a "black box" technology which causes other techniques to be preferred to it. A neural network is basically a mathematical function that computes an output by applying the function to the input variables. A neural network only works with numerical values, so the first thing we will do is translate the transaction values into numbers. To simplify the example we will use 0 and 1 as numbers but any number would do, the need is to adapt the data type not the values. Let’s translate our values as 0 for moderate price, no children, short duration, Disneyland destination and 1 for high price, yes to children, long duration and Europe (see Table 10).

A neural network is composed of nodes and the number of input, hidden and output nodes is called the network topology. In our example, the input nodes are the independent variables (price, children, duration) and the output node is the destination. Intermediate results leading to the final output are stored in what is defined as hidden nodes because the user is only aware of the input and the output of the neural network. The algorithm computes a weighted sum based on the input variables at each node and returns an output. The weight applied to each input and the number of hidden nodes are established by separate algorithms. For our example, we will label the input nodes as A (price), B (children) and C (duration), use 2 hidden nodes (labeled D and E) and label the output node as F (destination), see Figure 3.

We will use the following mathematical functions to compute the output of nodes D,E and F :

D = IF (A - 3B + 2C) >= 0 THEN 1 ELSE 0
E = IF (-2A + 4B - C) >= 0 THEN 1 ELSE 0
F = IF (4D - 3E) >= 0 THEN 1 ELSE 0

If we process the potential customer Bill, who (A) has a high budget (1), (B) wants to go on a vacation with no children (0) and (C) for a long time (1) through the neural network, we will obtain the following prediction :
D = 1 - 3*0 + 2*1 = 3 >= 0 -> 1
E = -2*1 + 4*0 - 1 = -3 < 0 -> 0
F = 4*1 - 3*0 = 4 >= 0 -> 1

The output (F = 1) of the neural network for Bill is that we predict he will go to Europe.

Naïve-Bayes. The Naïve-Bayes technique works on categorical values and generates a model that is both predictive and descriptive which can be used for classification only. It is based on a Bayes’ Theorem and computes probabilities that are used to make predictions. In theory, all the independent variables must be statistically independent for the prediction to be accurate, since this assumption is usually not true in practice, the data mining technique inherited the adjective "naïve" and was called Naïve-Bayes. The application of Bayes’ Theorem is to predict the probability of the dependent variable (destination) based on the conditional probabilities of the independent variables. We will then compute a score for each possible value of the dependent variable and select the highest score to be the predicted value.

The first step is to evaluate the conditional probabilities for each independent variable as well as the prior probabilities for each destination, see Table 11. For instance, the conditional probability that a vacation to Europe has a high price is 0.75 because 3 out of the 4 trips to Europe had a high price and the prior probability that Europe is the destination is 0.67 (4 out of 6 trips).

The second step is to assign a score to each combination of independent variables and predict the destination, see Table 12. To obtain each score we multiply the conditional probabilities of each independent variable by the prior probability. For instance, Michael obtains a score of (0.75 * 0.75 * 0.75 * 0.67 = 0.283) for Europe and (0 * 0 * 0 * 0.330= 0) for Disneyland.

Nearest neighbor. The nearest neighbor technique is usually referred to as k-nearest neighbor (or k-NN) and generates a predictive model used for classification. This algorithm makes a prediction for a new entry based on a specified number (k) of similar cases (neighbors).

The nearest neighbors are determined by defining a distance measure that will be applied to the historical data set in comparison with the new transaction. This definition is strictly arbitrary, for instance, we could determine that for a vacation destination selection, the difference between a duration of 1 or 2 weeks is greater (has more impact on the choice) that the difference between 2 or 6 weeks. In that case, we could define the distance between 2 and 6 weeks as 1 unit and the distance between 1 and 2 weeks as 2 units even though it appears mathematically illogical.

In our travel agency example, since every variable can take 2 values, we will define the measure as 0 if the values are identical and 1 if they are different; and select k = 3. Table 13 illustrates the distance between each transaction in the historical data set. For instance, the distance between Robert and Mary is 1 [0 (Moderate = Moderate) + 1 (Yes ¹ No) + 0 (Short = Short)].

To validate the model, let’s process the historical data through the model (algorithm). Table 14 gives the prediction for each historical transaction and achieves an accuracy rate of 83% because 5 out of the 6 predictions were correct. To improve the accuracy, we could either increase k or modify the distance measure.

Association and sequencing (often called market basket analysis) - this category of data mining is used to understand the relationships between events that occurred simultaneously (association) or consecutively (sequencing). The first popular application of this category was to understand customer behavior or purchasing trends (market basket analysis) for marketing purposes and has evolved into other areas such as finance and medicine. Association and sequencing generate descriptive models.

Association tools analyze the data and generate observations (rules) such as "When customers buy meat, they also buy red wine 60% of the time" or "When customers open a checking account, they also open a savings account 35% of the time".

While association considers simultaneous events, sequencing also takes into account subsequent transactions. For instance, a sequencing observation could be that 80% of the patients who take medication X develop disorder Y within 6 months.

Some products generate text rules while others display results in a compact tabular form.

If we take another look at our travel agency example and display additional information regarding options purchased as part of the vacation package, see Table 15, we could derive the following observations or rules : "100% of the people who took the hotel reservations also rented a car" and "50% of the people who rented a car also chose the local tours".

Those rules can be broken into 2 components, the antecedent (left-hand side) like "people who took the hotel reservations" and the consequent (right-hand side) like "also rented a car". Generally, since real life applications are more complex that this example and are composed of many variables, each component of the rules can contain multiple elements.

Rules have two basic measures called support (or prevalence) and confidence (or predictability). Support measures how often elements occur in the same transaction as a percentage of total and confidence measures the dependency of one element versus another. To evaluate the impact of a rule, we add another measure called lift which is expressed either as the difference or the ratio between the confidence of the rule and the expected confidence. The expected confidence is the percentage of occurrence for the consequent (right-hand side) of the rule. For instance, the expected confidence for the rule "when people take the hotel they also rent a car" is 66.67% (people took the car rental option in 4 transactions out of 6).

In first rule (see Table 16), the confidence of 100% denotes the fact that every time customers take hotel reservations, they also rent a car and the support of 50% represents the fact that people select both options half the time.

In the second rule (see Table 9), "when people rent car, they also take hotel reservations", the lift ratio of 2.00 means that customers who rent cars are twice as likely to reserve an hotel than customers who do not rent cars. A lift ratio smaller than 1 is sometimes referred to as negative lift. For instance, in the third rule, the negative lift ratio (0.67) implies that a customer who makes a hotel reservation is less likely to take the local tours than a customer who does not make a hotel reservation.

Users of this technique, generally want to concentrate on the rules with the highest and lowest lift ratios accompanied by an acceptable support level. When the support level is very low, it means that the event happens rarely and if the enterprise tries to capitalize on these events, the impact of the measures taken will likely be less significant than if the enterprise acted on an event with a similar lift ratio but a very high support.

The synergy between those distinct technologies will come from two key concepts : integration and standardization. This will be achieved through mergers and partnerships that are developing rapidly throughout the industry. With the proliferation of data warehousing initiatives, decision support products are becoming an important part of a suite of tools implemented along with the data warehouse or data mart.

Common metadata : OLAP, query and data mining tools all need some form of metadata (information about the data structure they interact with) and this is also true of data warehouses, data marts. Each of these technologies looks at the world in its own fashion. Those mergers and partnerships will allow those various products to share the metadata information through the development of standards. An example of this is Informatica’s Metadata Exchange (MX) architecture which employs an API to exchange metadata between its data mart repository and various OLAP and query products.

Common language : Microsoft’s Universal Data Access (UDA) initiative includes OLE DB as the interface to data and will more than likely become the de facto standard used to interact with SQL (by encompassing ODBC) and non-SQL data sources. Another element of UDA is ActiveX Data Objects (ADO) which provide data access components optimized for Web applications and visual programming environments (Visual Basic, C++ or Java).

RDBMS vendors also have various Java integration projects currently in the works (triggers, stored procedures, storing Java objects, JDBC or even writing the DB server in Java). You can already see some of this in products such as IBM’s DB2 Universal Server, Oracle Lite 3.0 and Sybase Adaptive Server Anywhere.

Performance, flexibility and ease of use will come from the integration of those technologies at various levels. Query tools and report writers are generally already merged and hybrid OLAP is reducing the gap between MOLAP and ROLAP. To improve performance, you can anticipate that some OLAP and data mining functionality will be integrated within the kernel of relational database engines as database are already extending the core offerings. Some examples of this are Microsoft who will provide an OLAP engine called Plato within its next release of MS SQL Server, Informix and its Metacube product, Oracle and Oracle Express, Sybase whose ROLAP adventure gave birth to WhiteLight Systems Inc. and a recent partnership between IBM and Arbor Software. Red Brick provided its RISQL extensions and is now offering a data mining product (Data Miner) as part of its Red Brick Warehouse.

As those technologies get pushed behind the scenes (server-based) and are accessed through common interfaces (metadata and languages), their distribution will be simplified through a more unified interface. This will help to promote new ways of interacting with the information such as groupware and webcasting.