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.