Dimension Tables and Columns

When naming dimension tables, follow these rules:

Place the dimension in the schema: Dimension

Use the default schema of the database (dbo in the case of SQL server

This does not allow the dimension tables to be identified easily. While is pre or postfix could be used for that, this would simply mimic the behaviour of a schema.
Using a schema also makes it convenient to grant business users access to all facts and dimension, but not to other tables that are used by the ETL process.

Use the shorter schema dim instead

While this does allow for much less typing, it would be inconsistent with the names of all the other schemas

Name the dimension after the entity it represents

Abbreviate the entity name

Abbreviation makes it faster to type the name of the table for experience user who write queries. The downside is that it makes it hard to understand the schema for new users. To serve the expert users, create an alternative, short name for the table by declaring a database synonym or view. If you do this, maintain a list of abbreviated names

Use views to represent role playing dimensions

When the same dimension can play multiple roles, it is convenient for users to have different names depending on the role. Instead of duplicating data, create a view that

When creating role playing dimension views – postfix the view with _[Role]

By post-fixing the view name, sorting on the name of objects groups the different roles of the dimension nicely together. Using an underscore for the postfix help distinguish between roles and names that are made up of multiple words.

A dimension table Dim.Date plays two roles in the warehouse: one for shipping dates and another for delivery date. In this case, create two views:

  • Dim.Date_Shippping
  • Dim.Date_Delivery

Column Conventions

When naming dimension columns, follow these rules:

Follow the default rules for naming columns

Always add the following columns to a dimension table:

Attention: The internal data of table “3” is corrupted!

Only add these columns when you need them

It may seem frugal to save space on unnecessary columns (especially in dimension tables that only have type 1 history). However, space use of dimension tables really is a non-issue in data warehouse systems. The typical dimension tables up a tiny fraction of the total warehouse space. If you find yourself needing these columns later, any historical tracking will by then have been lost due to your frugality of space. Better to simply add these by default.

A fully consistent set of default columns aids metadata driven ETL tool generate code that loads dimensions.

For special columns, use the following prefixes:

EK_[DimensionEntity key. Appears in both the Type1 dimension table , where it is the primary key and the Type2 history tracking table where it forms a key together with ValidFrom.

If snow-flaking - entity keys incan also point to another dimension table (and should have referential integrity added to them.
HK_[Dimension]History Key. Appears in the history tracking table and is the primary key there
BK_Business key. Use this prefix for keys that arrive from the source system
MD_Metadata. Used to prefix tracking columns like lineage, batch ID number etc.

Don’t prefix special columns

The special columns will often be auto generated by metadata driven ETL tools. Unless the are prefixed, their naming may overlap with columns created by users and by names from source systems. Prefixing business keys also makes it easy for end users to locate columns of certain types by filtering on them