Oracle Dimensions
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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@pdbdev 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 Caluse CREATE DIMENSION [schema_name.]<dimension_name>
LEVEL <level_name> IS (<level_table_name.level.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 (<level_table_name.level.column_name>,...,...)
[SKIP WHEN NULL]
ATTRIBUTE <attribute_name> LEVEL <level_name> DETERMINES (<dependent_column_name_list>);
 
Dimension Creation with Hierarchy Clause CREATE DIMENSION [schema_name.]<dimension_name>
LEVEL <level_name> IS (<level_table_name.level.column_name>,...,...)
[SKIP WHEN NULL]
HIERARCHY <heirarchy_name> (<child_level CHILD OF <parent_level>);
conn sh/sh@pdbdev

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 (customers.city
  CHILD OF city
  CHILD OF state
  CHILD OF country);
Dimension Creation with Join CREATE DIMENSION [schema_name.]<dimension_name>
LEVEL <level_name> IS (<level_table_name.level.column_name>,...,...)
[SKIP WHEN NULL]
<attribute_clause | extended_attribute_clause | hierarchy_clause>
JOIN KEY (child_key_column, ..., ...) REFERENCES (parent_level)
ATTRIBUTE <level> DETERMINES (dependent_column, ..., ...)
conn sh/sh@pdbdev

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 [schema_name.]<dimension_name>
ADD <attribute_clause>;
ALTER DIMENSION customers_dim
Alter Dimension Add Dimension Join Clause ALTER DIMENSION [schema_name.]<dimension_name>
ADD <attribute_clause>
ALTER DIMENSION customers_dim
Alter Dimension Add Extended Attribute Clause ALTER DIMENSION [schema_name.]<dimension_name>
ADD <attribute_clause>
ALTER DIMENSION customers_dim
Alter Dimension Add Hierarchy Clause ALTER DIMENSION [schema_name.]<dimension_name>
ADD <hierarchy_clause>
ALTER DIMENSION customers_dim
Alter Dimension Add Level Clause ALTER DIMENSION [schema_name.]<dimension_name>
ADD <level_clause>
ALTER DIMENSION customers_dim
Alter Dimension Drop Attribute ALTER DIMENSION [schema_name.]<dimension_name>
DROP ATTRIBUTE <attribute_name>
[LEVEL <level> [COLUMN <column_name>]]
ALTER DIMENSION customers_dim
DROP ATTRIBUTE country;
Alter Dimension Drop Level ALTER DIMENSION [schema_name.]<dimension_name>
DROP LEVEL <level> [<CASCADE | RESTRICT>]
ALTER DIMENSION customers_dim 
DROP LEVEL customer CASCADE;
 
Drop Dimension
Drop Dimension DROP DIMENSION <dimension_name>;
DROP DIMENSION test_dim;

Related Topics
Built-in Functions
Built-in Packages
Analytic Views
DBMS_DIMENSION
DBMS_OLAP
Materialized View
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx