Q

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 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

Dig deeper on Oracle data warehousing

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close