$page_title = $titlebar_text; $header_bar_text = $titlebar_text; ///////////////////////////////////// // get header include('rlib_header.php'); // print << Oracle Dimensions Version 11.1  
General
Note:
A dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. However, columns in one column set (called a level) can come from a different table than columns in another set. The optimizer uses these relationships with materialized views to perform query rewrite. The SQLAccess Advisor uses these relationships to recommend creation of specific materialized views.

A dimension defines hierarchical (parent/child) relationships between pairs of columns or column sets. Each value at the child level is associated with one and only one value at the parent level. A hierarchical relationship is a functional dependency from one level of a hierarchy to the next level in the hierarchy. A dimension is a container of logical relationships between columns, and it does not have any data storage assigned to it.
Dimension Related Data Dictionary Objects dim$
dbms_dimensions dba_dimensions all_dimensions user_deminsions

Dimension Source Code
desc dba_dimensions

SELECT DISTINCT dimension_name
FROM dba_dimensions;

desc dim$

set pagesize 30
set linesize 121
col dimtext format a120
set long 100000

SELECT DISTINCT dimension_name
FROM dba_dimensions

SELECT d.dimtext
FROM obj$ o, dim$ d
WHERE o.obj# = d.obj#
AND o.name = 'TIMES_DIM';

set serveroutput on

exec dbms_dimension.describe_dimension('SH.TIMES_DIM');
System Privileges
alter any dimension create any dimension
create dimension drop any dimension
 
Create Dimension

Dimension Creation with Hierarchy
CREATE DIMENSION <dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
..
LEVEL <level_name> IS (<table_name.column_name>)
[SKIP WHEN NULL]
HIERARCHY <heirarchy_name> (<child_level CHILD OF <parent_level>);
CREATE DIMENSION test_dim
LEVEL lat IS (servers.latitude)
LEVEL lon IS (servers.longitude)
HIERARCHY latlon_rollup (lon CHILD OF lat);

Dimension Creation with Join
CREATE DIMENSION <dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
JOIN ?
CREATE DIMENSION test_dim
LEVEL e IS (servers.latitude)
HIERARCHY latlon_rollup (lon CHILD OF lat);

Dimension Creation with Attribute
CREATE DIMENSION <dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
..
LEVEL <level_name> IS (<table_name.column_name>)
[SKIP WHEN NULL]
HIERARCHY <heirarchy_name> (<child_level
  CHILD OF <parent_level>
  ..
  CHILD OF <parent_level>
JOIN KEY (child_key_column) REFERENCES <parent_level>);
ATTRIBUTE ...
CREATE DIMENSION customers_dim
LEVEL customer IS (customers.cust_id)
LEVEL city      IS (customers.cust_city)
LEVEL state     IS (customers.cust_state_province)
LEVEL country   IS (countries.country_id)
LEVEL subregion IS (countries.country_subregion)
LEVEL region    IS (countries.country_region)
HIERARCHY geog_rollup (customer
  CHILD OF city
  CHILD OF state
  CHILD OF country
  CHILD OF subregion
  CHILD OF region
JOIN KEY (customers.country_id) REFERENCES country)
ATTRIBUTE customer DETERMINES (
cust_first_name, cust_last_name, cust_gender,
cust_marital_status, cust_year_of_birth,
cust_income_level, cust_credit_limit)
ATTRIBUTE country DETERMINES (countries.country_name);
 
Alter Dimension
Alter Dimension Add Attribute Clause ALTER DIMENSION <dimension_name>
ADD <attribute_clause>
ALTER DIMENSION customers_dim
Alter Dimension Add Dimension Join Clause ALTER DIMENSION <dimension_name>
ADD <attribute_clause>
ALTER DIMENSION customers_dim
Alter Dimension Add Extended Attribute Clause ALTER DIMENSION <dimension_name>
ADD <attribute_clause>
ALTER DIMENSION customers_dim
Alter Dimension Add Hierarchy Clause ALTER DIMENSION <dimension_name>
ADD <hierarchy_clause>
ALTER DIMENSION customers_dim
Alter Dimension Add Level Clause ALTER DIMENSION <dimension_name>
ADD <level_clause>
ALTER DIMENSION customers_dim
Alter Dimension Drop  
ALTER DIMENSION customers_dim
DROP ATTRIBUTE country;
Alter Drop With Cascade Option  
 

Alter Drop With Restrict Option
 
EXPLAIN PLAN FOR
SELECT last_name, employee_id, manager_id, LEVEL
FROM emp
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

SELECT * FROM TABLE(dbms_xplan.display);

CREATE DIMENSION emp_dim
LEVEL empid IS (emp.employee_id)
LEVEL mgrid IS (emp.manager_id)
HIERARCHY emp_rollup (mgrid CHILD OF empid);
 
Drop Dimension
Drop Dimension DROP DIMENSION <dimension_name>;
DROP DIMENSION test_dim;
 
Related Topics
DBMS_DIMENSION
DBMS_OLAP
Materialized View
  EOM; ///////////////////////////////////// // get footer include('rlib_footer.php'); ?>