CREATE ATTRIBUTE DIMENSION

Purpose

Use the CREATE ATTRIBUTE DIMENSION statement to create an attribute dimension. An attribute dimension specifies dimension members for one or more analytic view hierarchies. It specifies the data source it is using and the members it includes. It specifies levels for its members and determines attribute relationships between levels.

Tip:

You can view and run SQL scripts that create attribute dimensions at the Oracle Live SQL website at https://livesql.oracle.com/apex/livesql/file/index.html. The website has scripts and tutorials that demonstrate the creation and use of analytic views.

Prerequisites

To create an attribute dimension in your own schema, you must have the CREATE ATTRIBUTE DIMENSION system privilege. To create an attribute dimension in another user's schema, you must have the CREATE ANY ATTRIBUTE DIMENSION system privilege.

Syntax

create_attribute_dimension::=

Description of the illustration create_attribute_dimension.eps

sharing_clause::=

Description of the illustration sharing_clause.eps

classification_clause::=

Description of the illustration classification_clause.eps

attr_dim_using_clause::=

Description of the illustration attr_dim_using_clause.eps

attributes_clause::=

Description of the illustration attributes_clause.eps

attr_dim_attributes_clause::=

Description of attr_dim_attribute_clause.eps follows

Description of the illustration attr_dim_attribute_clause.eps

attr_dim_level_clause::=

Description of the illustration attr_dim_level_clause.eps

key_clause::=

Description of the illustration key_clause.eps

alternate_key_clause::=

Description of the illustration alternate_key_clause.eps

dim_order_clause::=

Description of the illustration dim_order_clause.eps

all_clause::=

Description of the illustration all_clause.eps

Semantics

OR REPLACE

Specify OR REPLACE to replace an existing definition of an attribute dimension with a different definition.

FORCE and NOFORCE

Specify FORCE to force the creation of the attribute dimension even if it does not successfully compile. If you specify NOFORCE, then the attribute dimension must compile successfully, otherwise an error occurs. The default is NOFORCE.

schema

Specify the schema in which to create the attribute dimension. If you do not specify a schema, then Oracle Database creates the attribute dimension in your own schema.

attr_dimension

Specify a name for the attribute dimension.

sharing_clause

Specify whether to create the attribute dimension as an application common object. Specifying METADATA shares the attribute dimension's metadata, but its data is unique to each container. Specifying DATA shares the attribute dimension object; its data is the same for all containers in the application container and the data is stored only in the application root. Specifying NONE excludes the attribute dimension from being shared.

classification_clause

Use the classification clause to specify values for the CAPTION or DESCRIPTION classifications and to specify user-defined classifications. Classifications provide descriptive metadata that applications may use to provide information about analytic views and their components.

You may specify any number of classifications for the same object. A classification can have a maximum length of 4000 bytes.

For the CAPTION and DESCRIPTION classifications, you may use the DDL shortcuts CAPTION 'caption' and DESCRIPTION 'description' or the full classification syntax.

You may vary the classification values by language. To specify a language for the CAPTION or DESCRIPTION classification, you must use the full syntax. If you do not specify a language, then the language value for the classification is NULL. The language value must either be NULL or a valid NLS_LANGUAGE value.

DIMENSION TYPE

An attribute dimension may be either a STANDARD or a TIME type. A STANDARD type attribute dimension has STANDARD type levels. Each level of a TIME type attribute dimension is one of the time types. The default DIMENSION TYPE is STANDARD.

attr_dim_using_clause

Specify a table or view. You may specify an alias for the table or view by using the AS keyword.

attributes_clause

Specify one or more attr_dim_attribute_clause clauses.

attr_dim_attribute_clause

Specify a column from the attr_dim_using_clause source. The attribute has the name of the column unless you specify an alias using the AS keyword. You may specify classifications for each attribute.

attr_dim_level_clause

Specify a level in the attribute dimension. A level specifies key and optional alternate key attributes that provide the members of the level.

If the key attribute has no NULL values, then you may specify NOT NULL, which is the default. If it does have one or more NULL values, then specify SKIP WHEN NULL.

LEVEL TYPE

A STANDARD type attribute dimension has STANDARD type levels. You do not need to specify a LEVEL TYPE for a STANDARD type attribute dimension.

In a TIME type attribute dimension, you must specify a level type. The type of the level may be one of the time types. You must specify a time type even if the values of the level members are not of that type. For example, you may have a SEASON level with values that are the names of seasons. In defining the level, you must specify any one of the time level types, such as QUARTERS. An application may use the level type designations for whatever purpose it chooses.

DETERMINES

With the DETERMINES keyword, you may specify other attributes of the attribute dimension that this level determines. If an attribute has only one value for each value of another attribute, then the value of the first attribute determines the value of the other attribute. For example, the QUARTER_ID attribute has only one value for each value of the MONTH_ID attribute, so you can include the the QUARTER_ID attribute in the DETERMINES phrase of the MONTHS level.

key_clause

Specify one or more attributes as the key for the level.

alternate_key_clause

Specify one or more attributes as the alternate key for the level.

dim_order_clause

Specify the ordering of the members of the level.

all_clause

Optionally specify MEMBER NAME, MEMBER CAPTION, and MEMBER DESCRIPTION values for the implicit ALL level. By default, the MEMBER NAME value is ALL.

Examples

The following example describes the TIME_DIM table:

desc TIME_DIM

Name              Null?   Type          
----------------- -----   ------------- 
MONTH_ID                  VARCHAR2(10)  
CATEGORY_ID               NUMBER(6)     
STATE_PROVINCE_ID         VARCHAR2(120) 
UNITS                     NUMBER(6)     
SALES                     NUMBER(12,2)  
YEAR_ID          NOT NULL VARCHAR2(30) 
YEAR_NAME        NOT NULL VARCHAR2(40) 
YEAR_END_DATE             DATE         
QUARTER_ID       NOT NULL VARCHAR2(30) 
QUARTER_NAME     NOT NULL VARCHAR2(40) 
QUARTER_END_DATE          DATE         
QUARTER_OF_YEAR           NUMBER       
MONTH_ID         NOT NULL VARCHAR2(30) 
MONTH_NAME       NOT NULL VARCHAR2(40) 
MONTH_END_DATE            DATE         
MONTH_OF_YEAR             NUMBER       
MONTH_LONG_NAME           VARCHAR2(30) 
SEASON                    VARCHAR2(10) 
SEASON_ORDER              NUMBER(38)   
MONTH_OF_QUARTER          NUMBER(38)

The following example creates a TIME type attribute dimension, using columns from the TIME_DIM table:

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
DIMENSION TYPE TIME
USING time_dim
ATTRIBUTES
 (year_id
   CLASSIFICATION caption VALUE 'YEAR_ID'
   CLASSIFICATION description VALUE 'YEAR ID',
  year_name
    CLASSIFICATION caption VALUE 'YEAR_NAME'
    CLASSIFICATION description VALUE 'Year',
  year_end_date
    CLASSIFICATION caption VALUE 'YEAR_END_DATE'
    CLASSIFICATION description VALUE 'Year End Date',
  quarter_id
    CLASSIFICATION caption VALUE 'QUARTER_ID'
    CLASSIFICATION description VALUE 'QUARTER ID',
  quarter_name
    CLASSIFICATION caption VALUE 'QUARTER_NAME'
    CLASSIFICATION description VALUE 'Quarter',
  quarter_end_date
    CLASSIFICATION caption VALUE 'QUARTER_END_DATE'
    CLASSIFICATION description VALUE 'Quarter End Date',
  quarter_of_year
    CLASSIFICATION caption VALUE 'QUARTER_OF_YEAR'
    CLASSIFICATION description VALUE 'Quarter of Year',    
  month_id
    CLASSIFICATION caption VALUE 'MONTH_ID'
    CLASSIFICATION description VALUE 'MONTH ID',
  month_name
    CLASSIFICATION caption VALUE 'MONTH_NAME'
    CLASSIFICATION description VALUE 'Month',
  month_long_name
    CLASSIFICATION caption VALUE 'MONTH_LONG_NAME'
    CLASSIFICATION description VALUE 'Month Long Name',
  month_end_date
    CLASSIFICATION caption VALUE 'MONTH_END_DATE'
    CLASSIFICATION description VALUE 'Month End Date',
  month_of_quarter
    CLASSIFICATION caption VALUE 'MONTH_OF_QUARTER'
    CLASSIFICATION description VALUE 'Month of Quarter',
  month_of_year
    CLASSIFICATION caption VALUE 'MONTH_OF_YEAR'
    CLASSIFICATION description VALUE 'Month of Year',
  season
    CLASSIFICATION caption VALUE 'SEASON'
    CLASSIFICATION description VALUE 'Season',
  season_order
    CLASSIFICATION caption VALUE 'SEASON_ORDER'
    CLASSIFICATION description VALUE 'Season Order')
LEVEL month
  LEVEL TYPE MONTHS
  CLASSIFICATION caption VALUE 'MONTH'
  CLASSIFICATION description VALUE 'Month'
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (month_end_date,
    quarter_id,
    season,
    season_order,
    month_of_year,
    month_of_quarter)
LEVEL quarter
  LEVEL TYPE QUARTERS
  CLASSIFICATION caption VALUE 'QUARTER'
  CLASSIFICATION description VALUE 'Quarter'
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY quarter_end_date
  DETERMINES (quarter_end_date,
    quarter_of_year,
    year_id)
LEVEL year
  LEVEL TYPE YEARS
  CLASSIFICATION caption VALUE 'YEAR'
  CLASSIFICATION description VALUE 'Year'
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY year_end_date
  DETERMINES (year_end_date)
LEVEL season
  LEVEL TYPE QUARTERS
  CLASSIFICATION caption VALUE 'SEASON'
  CLASSIFICATION description VALUE 'Season'
  KEY season
  MEMBER NAME season
  MEMBER CAPTION season
  MEMBER DESCRIPTION season
LEVEL month_of_quarter
  LEVEL TYPE MONTHS
  CLASSIFICATION caption VALUE 'MONTH_OF_QUARTER'
  CLASSIFICATION description VALUE 'Month of Quarter'
  KEY month_of_quarter;

The following example describes the PRODUCT_DIM table:

desc PRODUCT_DIM

Name            Null?    Type          
--------------- -------- ------------- 
DEPARTMENT_ID   NOT NULL NUMBER        
DEPARTMENT_NAME NOT NULL VARCHAR2(100) 
CATEGORY_ID     NOT NULL NUMBER        
CATEGORY_NAME   NOT NULL VARCHAR2(100)

The following example creates a STANDARD type attribute dimension, using columns from the PRODUCT_DIM table:

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING product_dim 
ATTRIBUTES
 (department_id,
  department_name,
  category_id,
  category_name)
LEVEL DEPARTMENT
  KEY department_id
  ALTERNATE KEY department_name
  MEMBER NAME department_name
  MEMBER CAPTION department_name
  ORDER BY department_name
LEVEL CATEGORY
  KEY category_id
  ALTERNATE KEY category_name
  MEMBER NAME category_name
  MEMBER CAPTION category_name
  ORDER BY category_name
  DETERMINES(department_id)
ALL MEMBER NAME 'ALL PRODUCTS';

The following example describes the GEOGRAPHY_DIM table:

desc GEOGRAPHY_DIM

Name                Null?    Type          
---------------     -------- ------------- 
DEPARTMENT_ID       NOT NULL NUMBER        
DEPARTMENT_NAME     NOT NULL VARCHAR2(100) 
CATEGORY_ID         NOT NULL NUMBER        
CATEGORY_NAME       NOT NULL VARCHAR2(100) 
REGION_ID           NOT NULL VARCHAR2(120) 
REGION_NAME         NOT NULL VARCHAR2(100) 
COUNTRY_ID          NOT NULL VARCHAR2(2)   
COUNTRY_NAME        NOT NULL VARCHAR2(120) 
STATE_PROVINCE_ID   NOT NULL VARCHAR2(120) 
STATE_PROVINCE_NAME NOT NULL VARCHAR2(400)

The following example creates an STANDARD type attribute dimension, using columns from the GEOGRAPHY_DIM table:

CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
USING geography_dim
ATTRIBUTES
 (region_id,
  region_name,
  country_id,
  country_name,
  state_province_id,
  state_province_name)
LEVEL REGION
  KEY region_id
  ALTERNATE KEY region_name
  MEMBER NAME region_name
  MEMBER CAPTION region_name
  ORDER BY region_name
LEVEL COUNTRY
  KEY country_id
  ALTERNATE KEY country_name
  MEMBER NAME country_name
  MEMBER CAPTION country_name
  ORDER BY country_name
  DETERMINES(region_id)
LEVEL STATE_PROVINCE
  KEY state_province_id
  ALTERNATE KEY state_province_name
  MEMBER NAME state_province_name
  MEMBER CAPTION state_province_name
  ORDER BY state_province_name
  DETERMINES(country_id)
ALL MEMBER NAME 'ALL CUSTOMERS';