Oracle 9i

1.4 Oracle 9i

Beginning in late 2000 and with the recent introduction of Oracle 9i Release 2 in 2002, Oracle released the Oracle 9i line of its core technology products: the Oracle 9i Database, Oracle 9i Application Server, 9iAS, and Oracle 9i Developer Suite. Components previously available separately have now been integrated into suites.

Oracle dramatically reworked its application server, Oracle 9iAS for the Oracle 9i release, to incorporate technology for building portals, analyzing Web traffic, and creating and launching wireless applications. Oracle 9iAS Discoverer workbooks and Oracle 9iAS reports can be published as portlets. Oracle 9iAS clickstream intelligence reports the effectiveness of a Web site using Web server logs.

In addition to advances in application server technology came improvements to the application development tools. Now bundled as Oracle 9i Developer Suite, the tools speed up the creation of Internet-based BI applications. Jdeveloper wizards provide a rapid development environment for creating crosstabs, graphs, and other elements for analysis and presentation.

1.4.1 Data warehousing features in the 9i Database

Many features were added to the Oracle 9i Database to improve the performance, manageability, and scalability of the data warehouse. These features included the following:

• Bitmap join indexes to improve performance when joining tables

• A new partitioning method to allow the DBA to list the data values to be stored in each partition

• Improvements for ETL processing:

  • Change data capture provides a mechanism to identify changed rows in an Oracle database

  • External tables allow you to provide transformations while loading the data into the database

  • Merge is a new SQL command that performs upserts, useful in loading dimension data. If a row already exists, it is modified. If the row is not yet stored in the database, it inserts it.

  • Table functions provide pipelined parallel execution of PL/SQL transformations

• Materialized views can now be incrementally (fast) refreshed after individual DML statements, as well as after direct path loads

We'll take a look at these things and more throughout the book.

Business intelligence (BI) software provides knowledge workers with access to relevant data for reporting and analysis. It encompasses end-user query and reporting tools, OLAP tools, data mining tools, and executive information systems (EIS), which provide drill down, navigation, and exception reporting. In Oracle 9i, many business intelligence functions have been incorporated into the database.

1.4.2 Oracle 9i Database and express server technology converge

OLAP was first defined by Dr. E. F. Codd, the father of relational databases. He stated that relational databases were not originally intended to provide data synthesis, analysis, and consolidation—functions being defined as multidimensional analysis. For many years separate analytical databases such as Oracle Express were necessary to provide the functionality not available in relational databases.

Oracle has added many features that facilitate OLAP queries, and with Oracle 9i Release 2, it is now possible to use the Oracle server directly for OLAP. The SQL language has been extended to provide analytical functions, such as ranking, moving window aggregates, period-over-period comparisons, ratio to report, statistical functions, inverse percentiles, hypothetical rank and distributions, histograms, and first/last aggregates. Multiple levels of aggregation can be calculated using cube, rollup, and grouping sets. Most calculations are done directly within the server. These functions allow the OLAP queries to be expressed without complex self-joins and subqueries and allow the optimizer to choose a better execution plan.

1.4.3 Data mining functionality is embedded in Oracle 9i Database

Oracle 9i Data Mining, a new option to the Enterprise Edition, embeds data mining functionality into the database for making classifications, predictions, and associations.

Data mining is part of the knowledge discovery process. By using statistical techniques, vast quantities of data can be transformed into useful information. Data is like the raw material extracted from traditional mines: When turned into information, data is like a precious metal.

Data mining extracts new information from data. It allows businesses to extract previously unknown pieces of information from their warehouse and use that information to make important business decisions.

The discovery process typically starts with no predetermined idea of what the search will find. Large amounts of data are read, looking for similarities that can be grouped together to detect patterns and trends.

OLAP and DSS tools look at predefined relationships associated with the structure of the data. These are represented by constraints and dimensions. Data mining detects relationships that are associated with the content of the data and not yet defined, such as which products are most likely to be purchased together, known as market-basket analysis. When analyzing data over time, data can be used to detect unexpected patterns in behavior. The likelihood of an activity being performed sometime after another activity can be determined. Common applications for data mining include customer retention, fraud detection, and customer purchase patterns. Data can be mined looking for new market opportunities.

OLAP tools allow you to answer questions such as: Did sales of lava lamps increase in November compared with last year? Data mining tools help to identify answers to questions such as: What factors determine the sales of lava lamps?

With OLAP tools, analysts start with a question or hypothesis and query the warehouse to prove or disprove their theory. With data mining tools, the work is shifted from the analyst to the computer. Data mining tools use a variety of techniques to solve a number of different problems.

Oracle 9iAS Personalization, a real-time recommendation application, uses Oracle 9i Database's embedded data mining functionality to dynamically serve personalized recommendations to both anonymous Web visitors and registered customers, enabling 1:1 marketing for e-businesses. Oracle 9iAS Personalization answers questions such as: Which items is this person most likely to buy or like, with what likelihood? People who bought this item are likely to buy which other item? This type of personalization can be seen at Amazon.com and is becoming common at many other Web sites.