FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Physical or Logical?

Design Challenge

A great way to sharpen our analysis and modeling skills is to continuously address real-world scenarios. A modeling scenario along with suggested solutions appears each month in this column. The scenario is emailed to more than 1,000 modelers. The responses are then consolidated into this column. If you would like to become a Design Challenger and submit modeling solutions, please add your email address at www.stevehoberman.com/designchallenge.htm. If you have a challenge you would like our group to tackle, please email me a description of the scenario at me@stevehoberman.com.


The Response

To start with, I would ask each of them for their definition of a logical data model (LDM).

The modeler who believes the dimensional model is a physical data model (PDM) would most likely define an LDM as a representation of how the business works. Moshe Japha, a business process and requirements analyst, summarizes this perspective of the LDM as "the business view of the world, with no derived or redundant data - usually normalized, thereby ensuring a complete analysis of the business."

The modeler who believes the dimensional model is an LDM most likely defines the LDM as any application-independent view. Monica Oliver, a data architect consultant, takes this perspective: "A logical model represents the business view of data and is designed independently of any technical considerations." It is application-independent, so there would be no hardware, software and network constraints placed on your LDM. Therefore, there would be two types of logical data models: the relational LDM and the dimensional LDM. The relational LDM is normalized and is an application-independent view of how the business works, and the dimensional LDM is an application-independent view of how the business is measured.

For example, assume I need to model orders for a company that manufactures widgets. A relational LDM would capture how the business works, including all of the data elements important to order as well as the appropriate business rules. Examples of business rules include: An order must have one and only one valid Product Identifier. Order Quantity must always have a value. An Order must contain one or more Order Lines. A dimensional LDM, on the other hand, would capture how order is measured. For instance, the measure Order Quantity can be viewed at different levels of granularity by navigating up and down the dimension hierarchies. If the Order Quantity is five on April 1, 2006, what would it be for all of April 2006?

I believe the dimensional model is an LDM. The LDM represents a business view, and that view can depict how the business works or how the business is measured. The PDM represents the technical view that uses techniques such as denormalization and partitioning to make data retrieval fast and user-friendly. The data model in Figure 1 summarizes my belief.

Figure 1: The Dimensional Model is an LDM

The semicircle represents a subtyping symbol, which groups like things together. An LDM can be either a relational or dimensional LDM. A PDM can be either a relational or dimensional PDM. A dimensional PDM can be a star schema, snowflake or starflake. There is a many-to-many relationship between logical and physical data models that captures the very complex rule as to how logical and physical data models map to one another.

After reading both perspectives, is a dimensional model an LDM? Tallying up the responses to this challenge, 66 percent believe that the dimensional model is an LDM, and 34 percent believe that the dimensional model is a PDM. What do you think?


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.

For more information on related topics, visit the following channels:



Industry Vendors