Oracle Dimensions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose 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.
Dependencies
ALL_DIMENSIONS CDB_DIM_LEVEL_KEY DBA_DIM_LEVEL_KEY
CDB_DIMENSIONS DBA_DIMENSIONS DBMS_DIMENSIONS
CDB_DIM_CHILD_OF DBA_DIM_CHILD_OF DIM$
CDB_DIM_HIERARCHIES DBA_DIM_HIERARCHIES USER_DIMENSIONS
CDB_DIM_JOIN_KEY DBA_DIM_JOIN_KEY  
Reverse Engineering Dimension Source Code conn sys@pdborcl as sysdba

desc dba_dimensions

SELECT *
FROM dba_dimensions;

desc dim$

-- this works
SELECT dbms_metadata.get_ddl('DIMENSION', 'TIMES_DIM', 'SH')
FROM dual;

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

-- this is clearer to read but you must turn it into DDL
set serveroutput on
exec dbms_dimension.describe_dimension('SH.TIMES_DIM');
System Privileges
ALTER ANY DIMENSION CREATE DIMENSION DROP ANY DIMENSION
CREATE ANY DIMENSION    
 
Create Dimension
Dimension Creation with Attribute CREATE DIMENSION [schema_name.]<dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
..
LEVEL <level_name> IS (<level_table_name.column_name>)
[SKIP WHEN NULL]
ATTRIBUTE <level_name> DETERMINES (<dependent_column_name_list>);
 
Dimension Creation with Extended Attribute Clause CREATE DIMENSION [schema_name.]<dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
..
LEVEL <level_name> IS (<level_table_name.column_name>)
[SKIP WHEN NULL]
ATTRIBUTE <attribute_name> LEVEL <level_name> DETERMINES (<dependent_column_name_list>);
 
Dimension Creation with Hierarchy CREATE DIMENSION [schema_name.]<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>);
conn sh/sh@pdborcl

CREATE DIMENSION test1_dim
LEVEL dim_demo1 IS (customers.cust_id);

exec dbms_dimension.describe_dimension('TEST1_DIM');

CREATE DIMENSION dim_demo2
LEVEL city      IS (customers.cust_city)
LEVEL state     IS (customers.cust_state_province)
LEVEL country   IS (countries.country_id)
HIERARCHY geog_rollup (customer.city
  CHILD OF city
  CHILD OF state
  CHILD OF country);
Dimension Creation with Join CREATE DIMENSION [schema_name.]<dimension_name>
LEVEL <level_name> IS (<table_name.column_name>)
JOIN ?
conn sh/sh@pdborcl

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 Attribute ALTER DIMENSION <dimension_name>
DROP ATTRIBUTE <attribute_name>
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

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved