Dimensions are an optional feature associated with materialized views. The use of dimensions makes query rewrite much more flexible. A dimension is an Oracle object that stores hierarchical information about your data. This information is used by the optimizer as it determines whether it can rewrite your query to use a materialized view.
Data in a data warehouse
typically represents various hierarchical structures. One example would
be the time relationship of data, which might be of the form of decade,
year, quarter, month, week, day, hour, and so on.
These types of
relationships generally are used in data warehouse queries to roll up or
drill down to more or less detail as needed. Oracle has introduced an
object called a dimension that allows you to define these relationships
in tables. Oracle then uses these dimension relationship definitions to
more effectively rewrite queries and to determine if a materialized view
can best be used.
Although Oracle can rewrite queries without the presence of
dimensions, the definition of dimensions makes the query rewrite process
much more versatile. The use of dimensions allows the query rewrite to
move beyond SQL checking of the query's SELECT clause and its join
conditions in the WHERE clause; with dimensions, the query rewrite can
look at the summary grouping and also other operations.
When you create a dimension, you define the various components of the
dimension and then the various hierarchies of the dimension. For
example, if you created a table with columns for the number of landing
cycles that an aircraft might go through one cycle represent a single
landing, you might create a table called TAKEOFF_LAND, and in it, you
might create columns for year, quarter, month, week, day, and column of
takeoff. These columns would be components, or levels, of the hierarchy;
the order in which they appear would constitute the hierarchy itself.
During database services of data warehousing, you need to take care
of dimension object very carefully. You need to understand basic concept
of dimension object. Data warehousing always contains so many schedule
jobs which data extracting continuously and during this data loading,
if dimension object would become invalid or corrupt then you would not
able to recover because size of database always large for data
warehousing. It is not easy to recovery using any backup recovery
scenario. Due to these all reasons, you need to take care of all
dimension objects in data warehousing about levels and hierarchy
representation.
source : http://www.dbametrix.com/
