CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [233 users online]    © 2010 psoug.org