Oracle 11g has announced the "cube oriented
materialized view", a new Oracle data structure that makes complex reporting
easier. Standard Oracle materialized views allow the DBA to make copies of
a highly normalized database into a denormalized form by pre-joining tables
together, adding redundant data into the rows. Materialized views are also
useful for storing frequently-referenced summarizations.
This new cube-oriented materialized view takes
this concept one step further and allows for a third dimension. Let's take
a closer look at cube-oriented materialized views.
The ideal of "multidimensional databases" is
not new, and three-dimensional arrays of data have been used in data processing
since the 1960's. The general idea of organizing data along three or more
"dimensions" can be implemented in a variety of ways:
-
Microsoft Excel Pivot Tables - The
pivot table tutorial is the easiest way to understand multidimensional OLAP.
-
Oracle cube-oriented Materialized views
- Oracle 11g provides a way to create and query "cubes". In Oracle
OLAP, you can materialize summary data from fact tables, and present them
along a time dimension. Each "slice" of the cubes third dimension
would be a distinct time slice.
Multi-dimensional analysis in Excel Pivot
tables
Many Oracle shops will implement
multi-dimensional database by querying directly from MS Excel into the pivot
tables, using third-party tools such as
Excel-DB, a spreadsheet add-in
with direct API capabilities to read and write from Oracle:
Oracle multidimensional data fetched with Excel-DB
Now let's see the stages of denormalization
and summarization that lead Oracle to introduce the cubic representation.
Understanding Oracle cubes
As a refresher, most online database systems
are built in a highly normalized form to ensure fast data retrieval and storage:
A typical 3NF OLTP Schema
While the 3NF design is great for
transaction-oriented processing, complex management reports become resource
intensive as many table joins are required to produce analysis and summary
reports.
Hence, the Oracle DBA will create a
de-normalized data warehouse model of the OLTP database, pre-joining all of the
tables together into a single, wide, highly redundant representation of the OLTP
database. When we add dimension details, this is referred-to as a STAR
schema:
A 1NF STAR schema
From this pre-joined representation, reporting
becomes less resource intensive, and the DBA can then summarize and aggregate
the detail data into Materialized Views:
A materialized view of sales by region
The final massaging of this data includes
adding a third dimension. In most Oracle warehouses, the date is commonly
a third dimension:
A cube-oriented materialized view
This is the "cube", a three dimensional
representation of summary information. By allowing a materialized view to
take-on a third dimension,
Cubes and Hierarchies
Essentially, any OLAP system is a
system that captures summarized information and allows the summaries to be
displayed as cross-tabulations between two variables.
Data classification falls into two categories, single valued classification and
multi-valued classification. We may encounter a data entity that has many
permutations, and each permutation may have its own unique data values. For
example, we may be designing a data warehouse for a vehicle rental organization.
A rental vehicle may be a sailboat, a car, or an airplane. While each of these
entities will be represented in our warehouse as a vehicle, each entity type has
distinct data attributes that do not apply to the other entities. A query that
references average hull length would not apply to airplanes and cars. These are
called single-valued classifications, in that there are distinct data values
that ally to each class of entities. Multi-valued data classification applies to
mutually exclusive values between data items. For example, we could track the
sex of a customer with a multi-valued flag that would be set to M, F, and U.
To illustrate the importance of class hierarchies in a data warehouse analysis,
let’s take a look at how data classification may aid data warehouse queries. To
illustrate the principle of data classification, let’s return to the Guttbaum’s
example. Recall that customer information is collected at the time a customer
obtains a Grocery Club Card, and transaction information is obtained at the time
a customer “swipes” the Grocery Club Card at the checkout counter.
Also recall that Guttbaum’s has a fact table in which each item appears as a
separate row in the table. In this way, correlational analysis can be used to
determine associations between grocery items. However, even though the database
stores a row for each item in a transaction, there is no direct information
about the different categories in which each item participates. A transaction
may record the sale of two pounds of calf livers, but it does not record the
fact that calf liver is a part of the beef category, that beef is a part of the
meat category, and that meat is a part of the food category.
In practice, just about every “dimension” in a data warehouse could become a part
of a classification hierarchy, and because a data warehouse is subject-oriented,
it is usually the subject that is classified. Another example would be a data
warehouse for a bookstore where sales of books would need to be classified into
fiction, non-fiction, and so on. The identification of these classification
hierarchies is critical to warehouse analysis because a method must be designed
to allow the subjects to be categorized for queries.
As noted earlier, the idea of classification hierarchies in a data warehouse
analysis has parallels in the object-oriented world. In a C++ program, class
hierarchies can be created to distinguish between different “types” of an
object, where each type has its own data items and behaviors. The same principle
applies to the data warehouse. The end user may want to query, How many paper
products were sold last week? Unless our warehouse knows which items participate
in the paper products categories, the warehouse will not be able to easily
answer this query.
As we will discuss in the next chapter, classification hierarchies are added to
basic entity/relation models so that the data warehouse analyst can describe
each type of item. In Figure 3.19, you can see that a bill-of-material’s entity
for an item has been extended to allow for different types of items. We’ll take
a closer look at extending entity/relation models in the next chapter, when we
begin to apply physical techniques to our logical systems analysis.
Staring with
