The Data Warehouse Blog

Here is an ER-diagram of the basic model for dimensions in a relational data warehouse core:

Before you start to wonder at the cryptic names of the tables and columns, I’d like to stress the importance of naming conventions.

Here, all the tables are assigned to a schema called “cmdm”, where “mdm” means “multi dimensional model” and “c” is the prefix for “Cubica”, the name of the data warehouse framework I have developed.

The ER-diagram contains three tables, which will be the mininum number of required tables for a dimension. Please note, that this model is very basic and does not contain any tables or columns for versioning or time-variance. As we go along and cover further aspects of data warehousing the model will successively be enhanced.

So why are there at least three tables instead of just one dimension table like in a “normal” star-schema? Here’s why:

1. there can be raw data on any hierarchy level
2. one raw data member can be a part of any number of consolidation paths
3. there can be non-leaf members with data and three different ways to aggregate them
4. the hierarchy can be irregular and include diamond shapes
5. the data model should be as generic as possible

Let’s examine the tables in detail:

Continue Reading »