Oracle | Dimensions

Oracle Dimensions Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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>;