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

Modeling Star Schemas
155
Use a Consistent Star Schema Layout
Drawing star schemas with a consistently dimensional layout may seem trivial but
as the number of stars grow with every release, the DW/BI team will find it tre-
mendously helpful when they scan multiple stars every day. Figure 5-13 shows the
recommended layout for star schemas designed using the 7Ws framework. The
four corners are reserved for the four major W-types: who, what, when and where
with top left reserved for the most common W: when. Think of this as the dimen-
sional modeling equivalent of drawing maps “north up”.
Consistent star
schema layout helps
developers to speed
read multiple stars
Figure 5-13
Consistent
dimensional layout
based on W-type
Discover the BI
Model Canvas at
modelstorming.com
to help you model
collaboratively using
this layout
Display BEAM✲ Short Codes on Star Schemas
Enhancing a star schema with BEAM ✲ codes allows you to document dimensional
properties and design decisions not supported by standard ER modeling tools.
Figure 5-14 shows an enhanced star schema for the CUSTOMERS ORDERS event
(as described in Chapter 2). BEAM ✲ codes are used to describe ORDERS FACT as
a transaction fact (TF) table and the CALENDAR, and EMPLOYEE as role-playing
(RP) dimensions. Table level codes (FV, CV, HV) describe the default slowly
changing policy for each dimension. Column level codes identify surrogate keys
(SK) degenerate dimensions (DD), and fully additive facts (FA).
If your modeling tool allows you to include table and column comments or ex-
tended attributes on ER diagrams you can use these to display BEAM✲ codes.
Alternatively, if this feature isn’t available you may be able to display the codes by
appending them to the business or logical table and column name in your model
and setting the tool’s model view to conceptual or logical. The BEAM✲Model-
stormer spreadsheet contains configurable options for export names and codes
as comments or extended database attributes that can be imported by many
modeling tools.
Star schema
BEAM✲ codes
document
dimensional
properties not
supported by
standard ER
notation