4.5 Dimensions

With Oracle 8 i, in addition to defining primary-foreign-key relationships among tables, you can also describe the relationships between the columns within one or more tables by creating a new schema object, called a dimension. Telling Oracle about such relationships in the data makes it possible for the advisor to recommend the best set of summaries to create, for the optimizer to rewrite more queries to use the summaries, and for the OLAP tools to perform rollup and drill-down operations. We will see the power of dimensions in the section 4.6, where we talk about query rewrite.

Typically, relationships between fact and dimension tables are specified using referential integrity constraints. Dimensions are used to define the following types of relationships:

• The hierarchical relationships among the data within the dimension tables. This lets Oracle know what data can be rolled up to higher levels within the hierarchy for summarization and how to drill down to a lower level in the hierarchy to see more detail. Hierarchical relationships are described using HIERARCHY clauses in the CREATE DIMENSION statement.

• Functional dependencies or one-to-one relationships between two columns in a dimension table. This is useful for looking up one value based on another value in the table. For example, given a date, we can determine the day of the week. Given a month number, we can determine the month name. Given a postal code, we can determine the town. Given a product_name, we know its manufacturer. One-to-one relationships are defined...