23 OLAP and Data Mining

Scalability

Oracle Database OLAP is highly scalable. In today's environment, there is tremendous growth along three dimensions of analytic applications: number of users, size of data, complexity of analyses. There are more users of analytical applications, and they need access to more data to perform more sophisticated analysis and target marketing. For example, a telephone company might want a customer dimension to include detail such as all telephone numbers as part of an application that is used to analyze customer turnover. This would require support for multi-million row dimension tables and very large volumes of fact data. Oracle Database can handle very large data sets using parallel execution and partitioning, as well as offering support for advanced hardware and clustering.

Availability

Oracle Database includes many features that support high availability. One of the most significant is partitioning, which allows management of precise subsets of tables and indexes, so that management operations affect only small pieces of these data structures. By partitioning tables and indexes, data management processing time is reduced, thus minimizing the time data is unavailable. Another feature supporting high availability is transportable tablespaces. With transportable tablespaces, large data sets, including tables and indexes, can be added with almost no processing to other databases. This enables extremely rapid data loading and updates.

Manageability

Oracle enables you to precisely control resource utilization. The Database Resource Manager, for example, provides a mechanism for allocating the resources of a data warehouse among different sets of end-users. Consider an environment where the marketing department and the sales department share an OLAP system. Using the Database Resource Manager, you could specify that the marketing department receive at least 60 percent of the CPU resources of the machines, while the sales department receive 40 percent of the CPU resources. You can also further specify limits on the total number of active sessions, and the degree of parallelism of individual queries for each department.

Another resource management facility is the progress monitor, which gives end users and administrators the status of long-running operations. Oracle Database 10g maintains statistics describing the percent-complete of these operations. Oracle Enterprise Manager enables you to view a bar-graph display of these operations showing what percent complete they are. Moreover, any other tool or any database administrator can also retrieve progress information directly from the Oracle data server, using system views.

Backup and Recovery

Oracle provides a server-managed infrastructure for backup, restore, and recovery tasks that enables simpler, safer operations at terabyte scale. Some of the highlights are:

• Details related to backup, restore, and recovery operations are maintained by the server in a recovery catalog and automatically used as part of these operations. This reduces administrative burden and minimizes the possibility of human errors.

• Backup and recovery operations are fully integrated with partitioning. Individual partitions, when placed in their own tablespaces, can be backed up and restored independently of the other partitions of a table.

• Oracle includes support for incremental backup and recovery using Recovery Manager, enabling operations to be completed efficiently within times proportional to the amount of changes, rather than the overall size of the database.

• The backup and recovery technology is highly scalable, and provides tight interfaces to industry-leading media management subsystems. This provides for efficient operations that can scale up to handle very large volumes of data. Open Platforms for more hardware options & enterprise-level platforms.

Security

Just as the demands of real-world transaction processing required Oracle to develop robust features for scalability, manageability and backup and recovery, they lead Oracle to create industry-leading security features. The security features in Oracle have reached the highest levels of U.S. government certification for database trustworthiness. Oracle's fine grained access control feature, enables cell-level security for OLAP users. Fine grained access control works with minimal burden on query processing, and it enables efficient centralized security management.

Data Preparation

Data preparation usually requires the creation of new tables or views based on existing data. Both options perform faster than moving data to an external data mining utility and offer the programmer the option of snapshots or real-time updates.

Oracle Data Mining provides utilities for complex, data mining-specific tasks. For example, for some types of models, binning improves model build time and model performance; therefore, ODM provides a utility for user-defined binning.

ODM accepts data in either non-transactional (single-record case) format or transactional (multi-record case) format. ODM provides a pivoting utility for converting multiple non-transactional tables into a single transactional table.

ODM data exploration and model evaluation features are extended by Oracle's statistical functions and OLAP capabilities. Because these also operate within the database, they can all be incorporated into a seamless application that shares database objects. This allows for more functional and faster applications.

Model Building

Oracle Data Mining supports all the major data mining functions: classification, regression, association rules, clustering, attribute importance, and feature extraction.

These algorithms address a broad spectrum of business problems, ranging from predicting the future likelihood of a customer purchasing a given product, to understand which products are likely to be purchased together in a single trip to the grocery store. Since all model building takes place inside the database, the data never needs to move outside the database, and therefore the entire data-mining process is accelerated.

Model Evaluation

Models are stored in the database and are directly accessible for evaluation, reporting, and further analysis by a wide variety of tools and application functions. ODM provides APIs for calculating confusion matrix and lift charts. ODM stores the models, the underlying data, and the results of model evaluation together in the database to enable further analysis, reporting, and application-specific model management.

Model Apply (Scoring)

Oracle Data Mining provides both batch and real-time scoring. In batch mode, ODM takes a table as input. It scores every record, and returns a scored table as a result. In real-time mode, parameters for a single record are passed in and the scores are returned in a Java object.

In both modes, ODM can deliver a variety of scores. It can return a rating or probability of a specific outcome. Alternatively it can return a predicted outcome and the probability of occurrence of the outcome.

ODM Programmatic Interfaces

ODM provides Java and PL/SQL interfaces for data mining. These interfaces make is possible to embed data mining in applications.

ODM Java API

The ODM Java API allows programmers to develop data mining applications or tools in the J2SE/J2EE environment. The API defines a set of classes that can be used to develop a complete data mining solution. The API has built-in data mining metadata management and provides an infrastructure to build data mining applications easily. The API supports importing and exporting of PMML models for Naive Bayes and Association Rules models. The API supports asynchronous execution of mining operations and provides mechanisms to retrieve state transition information for running or completed operations. The API supports real-time scoring for all the supervised models and clustering models. This API supports text mining.

The ODM Java API provides flexible data preparation options. Applications can use either automated data preparation, or they perform data processing using external transformations defined as utility methods in the oracle.dmt.odm.transformation.Transformation class. In addition, applications can embed externals transformation details in the logical data specification as an input for the build operation, then the system will persist the details with the model and perform the embedded transformations in the future apply and test operations.

The ODM Java API design reflects concepts present in the emerging Java standard (JSR-73) for Data Mining, which is being developed through the Java Community Process.

ODM PL/SQL Packages

The following supplied packages support data mining in PL/SQL programs:

• DBMS_DATA_MINING

• DBMS_DATA_MINING_TRANSFORM

DBMS_DATA_MINING provides support for in-database data mining. This package can be used to build and test models and to apply models to new data (scoring). The package provides the basic building blocks for data mining, along with utilities and functions to inspect models and their results. The package also supports export and import of native models from a user's schema or database instance.

DBMS_DATA_MINING_TRANSFORM, a complementary package, provides support for popular data transformations such as numerical and categorical binning and linear and z-score normalization. The DBMS_DATA_MINING_TRANSFORM package is open source in nature, in that the package code is distributed with the product, so that users can study the utility routines and learn how to define their own data transformations using Oracle SQL and PL/SQL scripting.