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

Modeling Star Schemas
141
Star Schema Design
After you have updated the BEAM ✲ model to reflect the data realities and altered
priorities, you are ready to create a (logical) dimensional model and draw the star
schemas that will be used by the DW/BI team to generate the physical data ware-
house and design ETL and BI applications. This involves the purely technical steps
shown in Figure 5-7, none of which require stakeholder input or participation.
The model is
now ready to be
drawn as
star schemas
Figure 5-7
Creating a (logical)
dimensional model
If you are using the BEAM✲Modelstormer spreadsheet, copy your BEAM✲ model
to a graphical modeling tool for star schema layout by using the customizable
SQL DDL it generates. If you haven’t done so already, download the spreadsheet
template and find full instructions for using it at modelstorming.com
Adding Keys to a Dimensional Model
The major difference between the BEAM ✲ business model and a dimensional
model that can be used to create a physical database schema is the addition of
primary and foreign keys that define the relationships between the dimension and
fact tables. There is no need to discuss these keys with the business stakeholders,
because data warehouse key definition is purely a technical activity.
To convert the
BEAM✲ model into
a dimensional
model just add keys
Choosing Primary Keys: Business Keys vs. Surrogate Keys
During modelstorming you defined at least one attribute in each dimension as a
business key (denoted by the code BK) to uniquely identify each dimension mem-
ber. Business keys, such as PRODUCT CODE or CUSTOMER ID, are the unique
primary keys of source system reference tables. They may appear the obvious
candidate keys for similar-looking dimension tables, but source system business
keys never turn out to be as unique, stable, minimal or omnipresent across multi-
ple business processes as a data warehouse needs them to be. Instead dimensional
modelers use (data warehouse) surrogate keys (SK) as the primary keys for dimen-
sions. These are integer sequence numbers assigned uniquely to each dimension
table row, by ETL processes, and used by BI applications to join dimensions to fact
tables—where they act as foreign keys.
Do not use business
keys as dimension
primary keys. Use
(data warehouse)
surrogate keys
instead