DB2 Cube Views: A Primer

Chapter 3. Building a cube model in DB2 67

3.1.3 Star and snowflakes characteristics

Whatever the layout is, here are the most important characteristics for a star or

snowflake design:

򐂰 A large fact table that can be in the order of millions of data rows. It contains

the atomic or lowest level of detail, which may be a sales transaction, a phone

call, a reservation, a customer service interaction – whatever represents the

most granular fact of business operation which is meaningful for analysis.

򐂰 Small dimension tables containing a finite number of descriptions and detail

information for codes stored in the fact table.

򐂰 Use of primary and foreign keys

򐂰 Measures in the fact table

򐂰 Fact table with multiple joins connecting to other tables

In this chapter, we will use star schema to also represent snowflake unless

explicitly required to distinguish between them.

3.2 Cube model notion and terminology

A cube model stores metadata to represent a relational star schema, including

information on tables, columns, joins, and OLAP objects, and the relationship

between each of these objects.

It can be visualized as a collection of high-level objects that are obtained by

compounding entities and grouped as dimensional entities such as facts,

dimensions, hierarchies, attributes that relate directly to OLAP-type solutions.

To better understand the notion of a cube model in DB2, we will pursue a layered

approach to this concept in the context of the following scenario.

Imagine that over time, we are tracking sales data of a retail company selling

cosmetics that has stores spread over several states. Information is stored about

each customer, line of products that the company sells, the stores, campaign

details that the company adopts. These questions now arise when the company

wants to decide on a new campaign:

򐂰 Which is the best geographic location that they start with, based on stores

making consistent profits?

򐂰 Which time period is the best to start the campaign?

򐂰 Who is the target market for the new product?

68 DB2 Cube Views: A Primer

The analyst needs to understand the complex table structures and their

relationships, and without this understanding, data retrieved may well prove

meaningless. Building a cube model makes this effort easier and faster than it

would be without DB2 Cube Views.

For our business case study, we used a retail database with the tables

CONSUMER_SALES

,

CONSUMER

,

PRODUCT

,

STORE

,

DATE

,

CAMPAIGN

.

We start with an examination of the relational data (star or snowflake schema)

design in DB2 (see Figure 3-3) and will present the different concepts (measures

facts, dimensions, joins).

Figure 3-3 Relational data schema in DB2

3.2.1 Measures and facts

A measure object defines a measurement entity and populates each fact table

row. They are usually numeric and additive and common examples of measured

facts or measures are

Revenue

,

Cost

, and

Profit

. Consider the set of measures

defined in the fact table

CONSUMER_SALES

in Figure 3-4.

Note: Refer to Appendix E, “The case study: retail datamart” on page 685 for

a complete description of the star schema for the retail database case study.

Fact Table

Dimension Table

Dimension Table

Dimension Table

Dimension Table

DB2 Relational

Objects

Chapter 3. Building a cube model in DB2 69

Figure 3-4 Facts object

Measures can be derived from the columns of the

CONSUMER_SALES

table:

for example Transaction Sale Amount (

TRXN_SALE_AMT

), Transaction Cost

Amount (

TRXN_COST_AMT

),

Profit

as in Example 3-1.

Example 3-1 Derived measure

Profit =@Column(STAR.CONSUMER_SALES.TRXN_SALE_AMT) -

@Column(STAR.CONSUMER_SALES.TRXN_COST_AMT

Some of the measures described in the facts object can be actual columns from

the relational table or aggregated measures (measure that have been calculated

using the aggregation functions as

SUM, AVG). For example, using the

Profit

SQL

expression as input for the SUM aggregation function, an aggregation on the

Profit measure would be:

SUM(Revenue - Cost). For further information on the

options available when creating advanced measures in DB2 Cube Views, please

refer to 3.4, “Enhancing a cube model” on page 118.