Schematics of data warehouse infrastructure
Do have any schematics of an Oracle data warehouse and its infrastructure? Any examples of
frozen, archived or historical files and how the infrastructure should look for individual data
marts? Any help with this would be greatly appreciated, or a resource site that I can look
books have been written on this topic. Generally a data warehouse will utilize a star
schema. A star schema consists of centralized fact tables and outlying dimension tables the
dimension tables are usually only a couple of columns while the fact tables may contain hundreds.
The dimension tables relate back tom the facts through one-to-many relationships. A single
warehouse can have many of these star schemas. Generally the dimension table will contain the
columns you use to slice the data in the fact table with. You search on the dimension tables and
then move in to the fact tables. In 9i and later releases the indexes on the fact table are bitmap
indexes to allow the optimal bitmap merge resolution of the star join. Oracle also provides the
concept of dimension objects. A dimension object is a description of the hierarchies inherent in
the fact table and is used by Oracle to optimize execution plans. Oracle also provides for
materialized views (also called summaries).
This was first published in July 2004