One day a colleague asks you for feedback on an enterprise data model (EDM) she has been tasked with building in partnership with business analysts. The model appears to cover all of the concepts within the organization. The only issue is that the model has been built completely using dimensional structures. There is a Customer dimension, a Product dimension and many other dimensions. There are financial fact tables, customer satisfaction fact tables and many other fact tables.

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.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access