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
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?