Ask the Expert

Data modeling: Entity relationship (E-R) vs. dimensional data models

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: