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?
Requires Free Membership to View
Yes, whether to use 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.
This was first published in February 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation