What are the myths about Dimensional Modeling?

A few myths are floating around about dimensional modeling that deserves to be addressed.

It can be executing a dimensional data model that will lead to stovepipe decision support systems. This myth goes on to blame denormalization for providing only specific software that therefore cannot be modified. This is a short-sighted interpretation of dimensional modeling that has managed to get the message exactly backward.

First, we have argued that every entity-relation model has an equivalent set of dimensional models that contain the same information. Second, we have shown that even in the presence of organizational change and end-user adaptation, the dimensional model extends gracefully without altering its form. It is the entity-relation model that whipsaws the application designers and the end-users by requiring SQL to be rewritten.

It can be seen hundreds of really good dimensional designs done by people we have never met or had in our classes. There is a whole generation of designers from the packaged goods retail and manufacturing industries who have been using and designing dimensional databases for the last 15 years.

This myth is rooted in the historical origins of dimensional modeling but not in its current-day reality. Dimensional modeling has been used in multiple business areas, including retail banking, commercial banking, property and casualty insurance, health insurance, life insurance, brokerage customer analysis, telephone company operations, newspaper advertising, oil company fuel sales, government agency spending, manufacturing shipments, health care, and many more.

Snowflaking is eliminating low-cardinality textual attributes from dimension tables and locating them in “secondary” dimension tables. For instance, a product category can be treated this way and physically removed from the low-level product dimension table.

Snowflaking is not different from dimensional modeling. We regard snowflaking as an embellishment to the simplicity of the basic dimensional model. We think that a designer can snowflake with a clear conscience if this technique improves user understandability and improves overall performance.

The argument that snowflaking assists the maintainability of the dimension table is specious. Maintenance issues are indeed leveraged by entity-relation-like disciplines, but all of this happens in the data staging area before the data is loaded into the dimensional schema.

Entity-relationship modeling does not model a business, rather it models the micro relationships among data elements. Entity-relationship modeling does not have business rules, it has data rules.

Entity-relationship models are wildly variable in structure. The wild variability of the mechanism of entity-relationship models defines that each data warehouse required custom-written and fine-tuned SQL. It can also represent that each schema because it is tuned, is very accessible to changes in the customer querying habits because such schemas are asymmetrical.