Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Data Warehouse Design

The database serves as the foundation of the data warehouse: it is the place where the data is stored. But there is more to a data warehouse than simply data—a data warehouse becomes useful only when there are business users who want to gain access to the information stored within it. This may seem like a trivial point, but we’ve seen numerous cases of companies building data warehouses without consulting the business to determine what its needs actually are; thus, the database ends up with very few users and little activity.

Assuming that your warehouse is well planned and that there is a demand for the data, your next challenge will be to figure out how to handle the demand. You will be faced with the need to design your data warehouse to deliver appropriate performance to your users—performance that may initially seem far beyond the capabilities of your system, since the information requested from the data warehouse can involve summaries and comparisons of massive amounts of detailed data.

When you start designing your data warehouse, you also need to remember that a data warehouse is never complete. When the business changes, so too must the data warehouse. Thus, the ability to track changes through metadata stored in a repository often becomes critical in design phases.

Various design tools in the market provide this capability. In the Oracle world, Oracle Designer is extremely useful because, in addition to its repository, it provides the capability ...