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

158
Chapter 5
Choose BI-Friendly Naming Conventions
Use business
friendly names for
physical facts and
dimensions to
reduce BI tool
metadata
What’s in a name? Database object naming is a strangely emotive subject and
naming conventions for facts and dimensions vary greatly. The convention used in
this book is singular nouns for dimensions (for example, CUSTOMER and
PRODUCT) and plural nouns with a FACT suffix for fact tables (for example,
SALES FACT and ORDERS FACT). Doubtless you will have your own table name
standards, but before you adopt any semi-cryptic standard that exists for tradi-
tional database application development consider the BI users who will use these
tables and how they will interact with them. Adapt naming standards to work well
with your BI tools. The closer you can name dimensions and facts to the labels
stakeholders want to see on their reports—the terms they used during model-
storming—the less BI tool metadata the DW/BI team will have to maintain.
A common naming convention is to prefix all dimension tables with DIM_ so that
they sort together. What do stakeholders and developers (subconsciously) think
every time they see DIM_CUSTOMER or DIM_EMPLOYEE? Instead, reserve a
common schema or owner name, perhaps “DIMENSION”, for creating dimensions,
to achieve the same grouping and avoid such pejorative table names.
Use Data Domains
Data domains
enable consistent
translation into
database-specific
data types
Table 5-3
Many database modeling tools allow you to create data domains (or user-defined
data types) to help standardize physical column properties for similar column
types. If your modeling tool supports domains take advantage of them to translate
the default data types imported from the BEAM ✲ model into database-specific
data types with appropriate constraints. Data domains are especially useful for
making a data warehouse design portable across different database management
systems. Table 5-3 shows a starter list of recommended domains for a dimensional
model.
D OMAIN U SAGE D ATA
T YPE N ULLS D EFAULT
V ALUE
Surrogate Key Dimension primary keys,
Fact table foreign keys Integer Not Null 0
Flag Yes/No flags Char(1) Not Null “?”
Code Short codes,
Business keys Varchar(20) Not Null “Unknown”
Name Longer names Varchar(60) Not Null “Unknown”
Description Full descriptions Varchar(99) Not Null “Unknown”
Count Count facts Integer Nullable Amount Monetary facts Currency or
Number Nullable Duration Duration facts Integer Nullable
Example data
domains for a
dimensional model