My first Publication Agile-Data-Warehouse-Design-eBook | Page 178

Modeling Star Schemas
157
Figure 5-15
Snowflake schema
Do not use snowflake schema outriggers to document hierarchies if your database
or BI toolset doesn’t need them explicitly defined as 1:M relationships—most
don’t. Draw hierarchy charts instead. Do be pragmatic, if your toolset works better
with snowflake schemas, create them as a physical optimization.
Use hierarchy
charts rather than
snowflakes to define
hierarchies
Do Create Rollup Dimensions
Conformed rollup dimensions (RU), such as the product rollup PRODUCT TYPE
[RU] or the Calendar Rollup MONTH [ RU] described in Chapter 4, can look
similar to outriggers and have similar relationships with their base dimensions
(their surrogate keys are often carried in the base dimension). The important
difference is that rollup attributes are not normalized out of the base dimensions. Rollup dimensions
Rollup dimensions are often not explicitly modeled as BEAM ✲ tables because they
do not contain any attributes or values that are not present in their base dimen-
sions. If a rollup dimension is as yet undefined you should create it at the star
schema level by copying its base dimension and removing all the attributes below
the rollup level in the base dimension hierarchy. This is analogous to the ETL
process that should build the rollup from its base dimension data, rather than
source data, to keep the two in sync and guarantee conformance. Define rollups by
(RU) look similar to
outriggers but do
not normalize their
base dimensions
copying and editing
their base
dimensions
Creating Physical Schemas
Dimensional modeling does not make a strong distinction between logical and
physical modeling. Aside from the addition of DBMS-specific storage and indexing
options there is very little difference between logical and physical star schemas.
These database-specific additions are best defined in a data modeling tool that can
apply them consistently to each table and column type, eliminating the need to
directly edit Data Definition Language (DDL) scripts by hand.
If you are using the BEAM✲Modelstormer spreadsheet, you can edit its DDL
template to generate custom SQL for your DBMS.
Logical and physical
star schemas are
very similar