November 2004

Dimensions are Not Entities, or Why Data Structures Aren’t Equal

It takes some mental adjusting to adapt to data warehousing methods of data structure design. Both first-time data warehousers and experienced folks, who have kept their noses a little too close to that proverbial grindstone, can run into problems when attempting a query against a lone dimension table. These problems occur because a dimension is not the same thing as an entity. The previous statement is true even if the dimension is implemented as a table in a relational database. A dimension is simply not the same thing as a 3rd normal(-ish) form relational table. When an unsuspecting individual attempts to query a dimension table as if it were just another table, more often than not the querier will jump to the conclusion that something is wrong -- because the answers do not fit the expectations.
       A normalized table includes data that is functionally dependent on the key of the table. Under this dependency, data is fairly narrow in scope. Alternately, a dimension‘s scope could be fairly broad. Superficially, the design of a dimension table should, after a fashion, place everything into a functional dependence on the dimension‘s generated primary key. But while confusingly close, these two concepts (normalized functional dependence vs. generated dimensional dependence) are not the same thing. In the normalized table, included data functionally depends on the identity of an occurrence of the object described by that table. Therefore, contained data should be related closely to that single kind of object regardless of whether it is people, animals, or postage stamps. The dimension table is different; its primary key is jerry-rigged, specifically so that the key fits the needs of the data. Data items may be closely related to a single object; or a range of hierarchies in the ballpark of the object; or even varying groups of otherwise unrelated items that work more conveniently for some kind of query when mashed together. As an example, a "customer" dimension could contain direct customer-dependent data items; or it could contain loosely related data items under a broad category referred to as "customer," such as items that might be found in normalized tables for customer, corporation, customer locations, customer representative, etc.; or even sales hierarchy data and shoe size descriptions shoved together with some customer elements designated as a group named "customer." Clearly, the dimension has purposefully put that primary-key-cart right there in front of the dependency-horse, which you may have noticed is indeed a horse of a different color.
       On one hand, a normalized customer table would definitely lead one to believe that each row within the table represents a customer; on the other hand, the same cannot be said of a customer dimension. A dimension is designed so that valid answer sets are obtained through grouping or selecting values from inside the columns within the dimensions; there is no reliance on a value existing on a single row within the dimension. Due to the varying needs for managing the dimension content via slowly changing dimension logic, the number of rows found within the dimension table only indicates how often certain kinds of data change their values -- a trivial number, generally of limited interest. In order to emulate a normalized result against these dimensions, queries often must be formatted to return a count of distinct values within individual columns or column groupings, instead of performing the generic row counts usually executed. Therefore, people attempting to query a dimension as if it were no different than a normalized structure observe results that may be misleading. And in being misled, the user will look to the dimensional construction as being in error. All data structures are not created equal. Consider this the next time you need to write a query: look, and evaluate, before you leap. Dimensionalized designs are different, and that difference is not an accident. Multidimensional structures conform to a strict arrangement, albeit not a normalized one.

2004 Database Elaborations Archives:

• A Name Confers Much More Than Mere Identity December 2004
• Dimensions are Not Entities, or Why Data Structures Aren’t Equal November 2004
• Anything Worth Doing Is Worth Doing Again October 2004
• Simplicity is in the Eyes of the Beholder September 2004
• The Object-Relational Divide Can Be Bridged August 2004
• Normal is as Normal Does July 2004
• Column Placement in Tables and Indexes Can Determine Performance June 2004
• Flexibility Has Its Costs May 2004
• Managing the Time Dimension Challenges Data Mart Builders April 2004
• To Avoid Inertia, Learn to Compromise March 2004
• Build Better Relationships through Data Modeling February 2004
• Talkin‘ ‘Bout Key G-G-Generation January 2004

2003 Database Elaborations Archives:

• Full Table Scans Happen December 2003
• Better DBMS Domain Support Means Simpler Models November 2003
• The Process to Populate Fact Tables Must Adjust to Disruptions October 2003
• Multidimensional Nonloss Decomposition Made Simple September 2003
• Follow this Path to Painless and Almost Rational XML Structures August 2003
• You Have Heard It Before, But the Devil is Still in the Details July 2003
• Domains Require Subtlety June 2003
• Does the Term “Bureaucracy of Databases” Make You “Stutter”? May 2003
• Should You Change the Way You Think about Change? April 2003
• Your Database Models May Change Over Time, and That’s Good March 2003
• Beware of Developers Bearing Gifts of XML February 2003
• With Surrogate Keys, Use Caution January 2003

2002 Database Elaborations Archives:

• What is a Database? The Answer is Not So Obvious December 2002
• No No Repeating Repeating Groups Groups November 2002
• Ambiguity Makes Gathering Data Knowledge a Distinct Challenge October 2002
• Thoughts of Database Portability Dance in My Head Sepetmber 2002
• Development Teams Stare Across the Object-Data Divide August 2002
• How Comprehensive are Your Dimensions? July 2002
• The Design Implications of a Hierarchy of One June 2002