Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

OLAP in the Database

In certain applications, it may make sense to analyze the data at its source in addition to retrieving that data. Although online analytical processing (OLAP) is often done within client tools (as we describe later), Oracle8i and Oracle9i added analytic functions as SQL extensions to the core database to help client reporting and analysis tools and developers perform OLAP-style aggregation more easily and efficiently in the database. These functions include:

The ROLLUP operator

Used in conjunction with a GROUP BY clause; causes the data to be grouped by multiple levels of detail. ROLLUP can deliver data from two dimensions with an increasing level of detail in one dimension.

The CUBE operator

Used in conjunction with the GROUP BY clause. Creates a multidimensional data cube, such as one used in a crosstabulation report.

Ranking functions

Used to compute a record’s rank with respect to other records. Functions include RANK, DENSE_RANK, CUME_DIST, PERCENT_RANK, NTILE, and ROW_NUMBER. Oracle9i further adds a hypothetical rank function.

Windowing and reporting functions

Used to compute cumulative and moving averages and to calculate share. Functions include SUM, AVG, MIN, MAX, COUNT, VARIANCE, STDDEV, FIRST_VALUE, LAST_VALUE, and RATIO_TO_REPORT. Oracle9i further adds a histogram function and inverse percentiles.

LAG/LEAD functions

Often used to compare values from similar time periods, such as the first quarter of 1999 and the first quarter of 2000.

Statistics ...