Data modeling: Entity relationship (E-R) vs. dimensional data models |
 |
EXPERT RESPONSE FROM: William McKnight
|
|
|
|
| > |
QUESTION POSED ON: 09 February 2004
Which data modeling technique (entity relationship (E-R) data modeling or dimensional modeling) is better in
which situation? Is there any problem associated with performance for either of them?
|
|
|
To continue reading for free, register below or login
To read more you must become a member of SearchDataManagement.com
Yes, whether to use entity relationship (E-R) data modeling techniques or dimensional data modeling techniques depends on the situation. However, the situation needs to be broadly defined to include adherence to shop standards and the ability of the team to properly design with the better technique. I
believe dimensional data modeling should be a skill that a business intelligence team has so it can
properly deploy dimensional data models when appropriate.
Generally speaking, entity relationship (E-R) data modeling is good for reporting and point queries while
dimensional data modeling is good for ad-hoc query analysis. Many times, this
translates to an entity relationship-based data warehouse and a dimensional data mart layer.
Dimensional imposes some rules on the modeling, but results in a data model that
has the access methods inherent by virtue of the relationships. Users are
also better able to relate to the 'see measure by dimensional value(s)'
paradigm than 'anything goes'.
Although, especially in shops that start small and grow into a robust
architecture, the data warehouse itself may be dimensional. Dimensional data modeling is
hard to come by directy from source, so in this approch dimensional is
probably supported by some E-R, normalized tables. Also, there should be
flexibility in the mart architecture such that marts are designed 'for
purpose' - not all marts are for ad-hoc query analysis or generalized
purposes and thus, not all marts should be dimensional.
I have reviewed hundreds of data warehouse data models. I have yet to find
a textbook-perfect E-R (i.e., 3rd normal form) or dimensional model so don't
fret the details. Pick a technique as a guideline and build your data models for
purpose.
|
|
|
|
|
|
|
|
Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and
answer pairs from more than 250 TechTarget industry experts.
|
|
|
|
|
|
|
|
|
|
|
|
