A Dimensional Enterprise Data Model

The Challenge

Is this right? Can an EDM be dimensional? Why or why not?

The Response

An EDM is a subject-oriented and integrated data model containing all of the data produced and consumed across an entire organization. Subject-oriented means that the concepts on a data model fit together as the CEO sees the company, as opposed to how individual functional or department heads view the company. For example, there is a single concept called Customer with one agreed-upon definition, as opposed to the sales and accounting departments’ distinct and sometimes conflicting views of Customer. Integration means that all of the data and rules in an organization are depicted once and fit together seamlessly.

The definition of the EDM does not specify whether it must be built using relational or dimensional modeling structures. Relational modeling mimics business processes, and dimensional modeling captures how businesspeople monitor their business. In my data modeling classes, I summarize relational and dimensional modeling by saying that relational modeling captures business execution whereas dimensional modeling captures business evaluation.

So can an EDM focus on business evaluation and therefore be dimensional? A number of folks, including Gangadhar Jakkula, architect, and Tim Klein, data warehouse architect, say yes. Klein believes that an enterprise can be in any format, including dimensional. “The term enterprise in enterprise data model refers to the scope of the model, not the structures used.” Jeff Lawyer, senior data architect, is also supportive of this view. “My answer today is also tempered with the fact that, if a dimensional EDM is useful in some way - that is, if it brings clarity to the enterprise business processes and the data they use - then why not?” Ronald, project lead, also believes the dimensional EDM could provide some value. “Analysts and business users need to view this time-historical data to make key business decisions to strategically place a company in today’s ever-changing market.”

The modeler must identify the purpose of an EDM, and this purpose will determine whether relational or dimensional is ideal. Most organizations have relational EDMs because there is a need to understand how the business works as a prerequisite to a very large project, such as a business intelligence or an enterprise resource planning implementation. In addition, one very real issue with having a dimensional EDM is the possibility that the model may not be extensible and easily accommodate changing business needs. A dimensional model by its definition is built to address a very specific business need. If the enterprise has conflicting or changing business needs, the model will need to change too.

Lawyer does not consider a dimensional EDM the best way to represent the EDM because such a dimensional view could be missing key relationships that only exist in a true relational view. Consider an Order in a dimensional EDM. The measures in an Order will be constrained by several dimensions, including Product, Customer and Time. There are more than a dozen dates associated with an Order, such as Order Entry Date, Order Drop Date and Order Canceled Date. In a dimensional model, usually only one of these dates is associated with Time. The other dates are not captured, and, therefore, valuable data can be lost.

Scott Meredith, architect, also feels skeptical toward the dimensional EDM. “Although a logical representation of the business can be achieved using dimensional structures, using these structures could have negative effects on extensibility and industry data integration.” Warren Cotton, data architecture manager, says relational is preferred because “an EDM should capture data at a very low granular level - preferably individual transactions. This allows maximum flexibility as to what can be done with the data.”

If you would like to become a Design Challenger and have the opportunity to submit modeling solutions, please add your email address at http://www.stevehoberman.com/. If you have a challenge you would like our group to tackle, please email me a description of the scenario at me@stevehoberman.com.

Steve Hoberman has worked as a business intelligence and data management practitioner and trainer since 1990. He is a Certified Business Intelligence Professional (CBIP), having achieved mastery level certification in data analysis and design. He is a popular and frequent presenter at industry conferences, both nationally and internationally.  Hoberman is a columnist and frequent contributor to industry publications, as well as the author of  Data Modeler's Workbench and Data Modeling Made Simple (available for purchase through the DM Review bookstore). He is the founder of the Design Challenges group, inventor of the Data Model Scorecard and a recognized innovator and thought leader in the field of data modeling. He can be reached at me@stevehoberman.com.

Graeme Simsion's latest book is out! Data Modeling Theory and Practice. Here's a link where you can read more about the book and purchase it at a discounted price.