Ask the Expert

Data warehouse as a fully normalized DB or as a collection of tables?

Would you design a data warehouse as a fully normalized database with the referential integrity and other business rules enforced through the DBMS or as a collection of tables with an application logic taking care (as much as an application can do it) of the business rules?
The

    Requires Free Membership to View

answer I have for you is my favorite one: It depends. Building a data warehouse that is fully normalized is an option. I assume that you mean that you are saying you do not want to use dimensional design. Generally when I try to design a warehouse, I build it on multiple levels. I build the lowest level data generally in a more normal form, whereas the data marts are built dimensionally. Each has its advantages and disadvantages. Generally we need to balance performance and function. You always need to remember to keep it simple.

The question of referential integrity (RI) is not a question at all. I feel that you always need to ensure that RI is maintained. RI is critical to successful and repeatable reporting. So I would encourage you to use the RI in the database. As for business rules I would usually implement these in the application that will load the warehouse. I don't generally build business rules into the warehouse. I find that they change too often and at times are too complicated and time-consuming to include within the warehouse. This logic is contained in your loading routines.

This was first published in October 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: