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?
QUESTION POSED ON: 05 OCT 2004
QUESTION ANSWERED BY: Ian Abramson
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.
|
|
|
