This is Chapter 9 from the 2002 Addison-Wesley book "Oracle SQL & PL/SQL Handbook," available for purchase here.
Oracle has enhanced its ability to develop business intelligence. It has placed
a variety of analytical functions in its database. These functions became available
with the release of Oracle 8.1.6. The functions allow the business analyst
to perform rankings, moving window calculations (i.e., moving averages), and
lag/lead analysis. Oracle has also included families of statistic and regression
functions in the database. These functions are part of the core SQL processing.
Previous to this release, these functions could be obtained only through
the use of purchased software. Incorporating these capabilities allows Oracle
to enhance scalability, database performance, and simplicity.
Analytic functions are well suited for the data warehouses that are common
in the workplace. The purpose of these warehouses is to derive business
information. Data warehouses look at data derived from OLTP systems
in different ways. One way is to look at the data across different dimensions.
For example, a user of our employee data may want to view the data in the
following ways:
- Total costs by current position
- Total costs by department
- Total costs by state
- Number of employees by state
- Number of employees by department
- Number of employees by current position
As you can see, there is virtually an unlimited number of ways to look at or
analyze the same basic data. Each of these different ways of looking at the
data is a dimension. Data warehouses generally have the ability to provide
information across different dimensions, which makes them multidimensional
databases. In this chapter, you will see that analytic functions like ROLLUP and CUBE
are ideally suited for computing values across multidimensions.
Read the complete chapter here.
