|
|
| |
 |
|
Oracle Tips
by Burleson Consulting
|
The Data Warehouse Development Life Cycle
Online Analytical Processing and Oracle
Alternatives To OLAP Data Representation
Many traditional database designs can be used to simulate a data
cube. One alternative to the cubic representation would be to leave
the table in linear form, using SQL to join the table against itself
to produce a result, as shown in Figure 5.19.
Figure 5.19 Joining a relational table against itself.
Let's take a look at some queries that might require the
self-joining of a table. For example:
* Show all customers in Hawaii who purchased our product more than
500 times.
* Show all customers in Los Angeles who purchase less than 10 times
per month.
* Show all large customers (buying more than 100 items per month) in
Alaska whose usage has dropped more than 10 percent in 1995.
* Show all customers in New York whose usage in March of 1990
deviated more than 20 percent from their usage in March of 1995.
* Show all customers in California where the company name contains
Widget and usage has dropped more than 20 percent in 1995.
Figure 5.19 shows how a large sales summary table can be logically
partitioned into pieces by extracting rows according to the year and
month. With a single table such as this one, we can issue SQL that
will join the table against itself to compare two date ranges. This
is a very powerful technique for using SQL to perform sophisticated
variance analysis without buying expensive tools. Listing 5.1 shows
that a subset of this data can be extracted such that only
California sites with more than 100 uses per month are displayed.
For display, we chose percentage variance, number of requests, site
number, ZIP code, and city. Note the sort order of the report in
Listing 5.1; it is sorted first by ZIP, followed by city, and then
by percentage variance within city.
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
|
|
|
