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.

Get DB2 Cube Views: A Primer now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.