shop = Pinewood

product = 4 date = 7 Mar 2004

count = 5 value = $215 discount = $32 cost = $75

Dimension columns

Aggregate columns

Figure 15.5 The cube used for OLAP is divided into subcubes. Each subcube contains the key for that subcube and summary information for the data falls into that subcube.

Of course, the ease of getting a report that can answer one of these questions depends on the particular implementation of the reporting interface. However, even for ad hoc reporting, accessing the cube structure can prove much easier than accessing a normalized relational database.

Three Varieties of Cubes

The cube described in the previous section is an example of a summary data cube. This is a very common example in OLAP. However, not all cubes are summary cubes. And, a data warehouse may contain many different cubes for different purposes.

These subcubes correspond to the purchase of the same product at one store on all days.

These are some of the subcubes in more detail.

The answer to the question is the number of subcubes where count is not equal to 0.

Figure 15.6 On how many days did store X not sell any product Y ?

Another type of cube represents individual events. These cubes contain the most detailed data related to customer interactions, such as calls to customer service, payments, individual bills, and so on. The summaries are made by aggregating events across the cube. Such event cubes typically have a customer dimension or something similar, such as an account, Web cookie, or household, which ties the event back to the customer. A small number of dimensions, such as the customer ID, date, and event type are often sufficient for identifying each subcube. However, an event cube often has several other dimensions, which provide more detailed information and are important for aggregating data. The facts in such a table often contain dollar amounts and counts.

Event cubes are very powerful. Their use is limited because they rapidly become very big-the database tables representing them can have millions, hundreds of millions, or even billions of rows. Even with the power of OLAP and parallel computers, such cubes require a bit of processing time for routine queries. Nonetheless, event cubes are particularly valuable because they make it possible to drill down from other cubes-to find the exact set of events used for calculating a particular value.

The third type of cube is a variant on the event cube. This is the factless fact table, whose purpose is to represent the evidence that something occurred. For instance, there might be a factless fact table that specifies the prospects included in a direct mail campaign. Such a fact table might have the following dimensions:

Prospect ID (perhaps a household ID)

Source of the prospect name

Target date of the mailing

Type of message

Type of creative

Type of offer

This is a case where there may not be any numeric facts to store about the individual name. Of course, there might be interesting attributes for the dimensions-such as the promotional cost of the offers and the cost of the names. However, this data is available through the dimensions and hence does not need to be repeated at the individual prospect level.

Regardless of the type of fact table, there is one cardinal rule: any particular item of information should fall into exactly one subcube. When this rule is violated, the cube cannot easily be used to report on the various dimensions. A corollary of this rule is that when an OLAP cube is being loaded, it is very important to keep track of any data that has unexpected dimensional values. Every dimension should have an other category to guarantee that all data makes it in.

When choosing the dimensions for a cube, be sure that each record lands in exactly one subcube. If you have redundant dimensions-such as one dimension for date and another for day of the week-then the same record will land in two or more subcubes. If this happens, then the summarizations based on the subcubes will no longer be accurate.

Apart from the cardinal rule that each record inserted into the cube should land in exactly one subcube, there are three other things to keep in mind when designing effective cubes:

Determining the facts

Handling complex dimensions

Making dimensions conform across the data warehouse

These three issues arise when trying to develop cubes, and resolving them is important to making the cubes useful for analytic purposes.