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

154
Chapter 5
into dollars during ETL otherwise they would be non-additive (NA) facts. In the
case of DISCOUNT some of the source figures were percentages. You could create
a consistent UOM by transforming all discounts into percentages but that would
not be an additive UOM. Additive fact design is covered in detail in Chapter 8.
[FA] : Full Additive fact, can be summed by any dimension
[NA] : Non-Additive fact, cannot be summed
[SA] : Semi-Additive fact, can be summed by certain dimensions only
Percentages make great measures and key performance indicators (KPIs) on
reports and BI dashboards but they make for poor inflexible NA facts in fact
tables. You should define facts that represent the additive components of per-
centage measures and calculate the percentages in BI applications.
Drawing Enhanced Star Schema Diagrams
Star schemas
represent facts and
dimensions using
ER notation
When all the dimension surrogate keys are in place and the facts defined it is time
to draw star schemas: ER diagram versions of the dimensional model that ETL and
BI developers will find useful and familiar. The best way to create and maintain
star schemas is to use a graphical data modeling tool that will also generate physical
schema definitions for your chosen data warehouse platform.
The SQL DDL generated by the BEAM✲Modelstormer spreadsheet allows you to
transfer your model directly into graphical modeling tools that support SQL
import. Alternatively, the DDL can be used to create default physical database
tables, which can then be reverse engineered by many modeling tools.
Create a Separate Diagram for Each Fact Table
Display one fact
table per star
schema ER
diagram
Once you have imported the dimensional model into a graphical modeling tool,
arranging the tables into readable star schemas is usually straightforward. Most
modeling tools support multiple ER diagram views for a single model. Use this
feature to create one diagram for each fact table and add their relevant dimensions,
making sure you are not duplicating the underlying dimensions as you do.
Don’t attempt to create one single ER diagram showing all the fact tables and
dimensions in the data warehouse. Even for a small subset of stars this quickly
becomes a mess of overlapping lines. ER notation is best restricted to viewing
one star at a time. Instead, develop a data warehouse matrix (covered shortly) to
provide a more useful overview of multiple stars or the entire model.
Enhanced star
schema = star +
consistent layout +
BEAM✲ codes
You can do two simple things to turn a standard star schema into an enhanced star
schema. The first is to consistently place dimensions based on their W-type. The
second is to add BEAM ✲ short codes to the tables and columns to describe their
dimensional properties.