People often engage us to conduct dimensional model design reviews. In this column, I’ll provide a laundry list of common design flaws to scout for when performing a review. I encourage you to use this list to critically review your own draft schemas in search of potential improvements. What’s the Grain? When a data warehouse team […]

We are often asked about degenerate dimensions in our modeling workshops. Degenerate dimensions cause confusion since they don’t look or feel like normal dimensions. It’s helpful to remember that according to Webster, “degenerate” refers to something that’s 1) declined from the standard norm, or 2) is mathematically simpler. A degenerate dimension (DD) acts as a dimension key in […]

The three fundamental themes that make up the soul of every data warehouse are drilling down, drilling across, and handling time. In Part One of “The Soul of the Data Warehouse,” I showed that drilling down was nothing more than adding a row header, any row header, to an existing query. Although we often grouse about […]

Although data warehouses come in many shapes and sizes and deal with many different subject areas, every data warehouse must embody a few fundamental themes. The three most important are drilling down, drilling across, and handling time. Modern data warehouses so deeply embed these three themes that I think an “if-and-only-if” relationship has developed between […]

In debugging literally thousands of dimensional designs from my students over the years, I have found that the most frequent design error by far is not declaring the grain of the fact table at the beginning of the design process. If the grain isn’t clearly defined, the whole design rests on quicksand. Discussions about candidate […]

Most relational databases support the use of a null value to represent an absence of data. Nulls can confuse both data warehouse developers and users because the database treats nulls differently from blanks or zeros, even though they look like blanks or zeros. This design tip explores the three major areas where we find nulls in our source […]

Dimensional modeling is a design discipline that straddles the formal relational model and the engineering realities of text and number data. Compared to entity/relation modeling, it’s less rigorous (allowing the designer more discretion in organizing the tables) but more practical because it accommodates database complexity and improves performance. Contrasted with other modeling disciplines, dimensional modeling […]