Previous
Previous
 
Next
Next

DEFINE DIMENSION (simple)

The DEFINE DIMENSION (simple) command defines a simple dimension. When a variable is dimensioned by regular dimensions, Oracle OLAP creates an array element for each set of its dimension values. The values of a simple dimension must be unique data values with the same data type. A simple dimension can be a flat dimension or a hierarchical dimension that contains values from different levels of a hierarchy.


Tip:

To create a hierarchical dimension using duplicate values or values of different data types, use a concat dimension as described in DEFINE DIMENSION CONCAT.

Syntax

DEFINE name DIMENSION type [TEMP] [AW workspace] [SESSION]

where type is the data type of the dimension. The syntax of type varies depending on the data type:

TEXT  [WIDTH n]
NTEXT  [WIDTH n]
ID
INTEGER
NUMBER [(precision , scale)]
DATETIME [( truncation-code )]
TIMESTAMP [( truncation-code )]
TIMESTAMP_TZ [( truncation-code )]
TIMESTAMP_LTZ [( truncation-code )]

Parameters

name

The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.

DIMENSION

The object type when you are defining a dimension.

TEXT

Specifies that the values of the dimension have the TEXT data type which is equivalent to the CHAR and VARCHAR2 data types in Oracle Database. This data type stores up to 4,000 bytes for each line in the database character set.

NTEXT

Specifies that the values of the dimension have the NTEXT data type which is equivalent to the NCHAR and NVARCHAR2 data types in the Oracle Database. This data type stores up to 4,000 bytes for each line in UTF-8 character encoding.

ID

Specifies a special text data type that stores up to 8 single-byte characters for each line in the database character set.

WIDTH n

For TEXT or NTEXT dimensions, the width, in bytes, of the storage area of each value of an object. Valid width values are 1 through 4000. Specify a fixed width only when you are certain that the values of a particular dimension are of similar size. When a value exceeds the specified width, it is truncated.

INTEGER

Specifies that the values of the dimension have the INTEGER data type. The data type for a dimension with values that are identified by their numeric position (1, 2, and so on). A data type of INTEGER means that the dimension has no character values. For ease of use, use a text or time period data type, when possible.

NUMBER

Specifies that the values of the dimension have the NUMBER data type. A NUMBER dimension differs from other dimensions in that its values cannot be specified by position, only by value. To specify the values of a NUMBER dimension by position, you can define an INTEGER type dimension surrogate for the NUMBER dimension.

precision

The total number of digits a value of type NUMBER can have.

scale

The number of digits a value of type NUMBER can have to the right of a decimal point. For example, when you specify a precision of 7 and a scale of 2, then the highest value that the dimension can have is 99999.99. When you specify a precision value, but do not specify a scale value, then the scale is 0.

DATETIME

Specifies that the values of the dimension have the DATETIME data type.

TIMESTAMP

Specifies that the values of the dimension have the TIMESTAMP data type.

TIMESTAMP_TZ

Specifies that the values of the dimension have the TIMESTAMP_TZ data type.

TIMESTAMP_LTZ

Specifies that the values of the dimension have the TIMESTAMP_LTZ data type.

truncation_code

A text expression that specifies a format model shown in Table: Datetime Format Templates for the ROUND and TRUNC Date Functions. A format model indicates how the date and time number should be truncated.

TEMP

Indicates that the dimension's values are only temporary and only for the current session. The dimension has a definition in the current workspace and can contain values during the current session. However, when you update and commit, only the definition of the dimension is saved. When you leave end your session or switch to another workspace, the data values are discarded. Each time you start the workspace, the values of a temporary dimension are NA.

AW workspace

The name of an attached analytic workspace in which you want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.

SESSION

Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.

Usage Notes

NA Values in Variables Dimensioned by Simple Dimensions

When a variable is dimensioned by regular dimensions, Oracle OLAP creates an array element for each set of its dimension values. When an array element is empty, then the element is said to contain an NA value. In some cases, this can result in a sparse variable—that is, a variable in which a relatively high percentage of array elements that are empty. There are two types of sparsity:

When a sequence of array elements contain enough NA values to fill up an analytic workspace page, Oracle OLAP does not actually store any of the NA values and, instead, keeps tracks of the values internally. However, when an analytic workspace page contains both regular values and NA values, then Oracle OLAP stores all of the values. You can reduce the number of array elements with NA values by dimensioning a variable with one or more composites or conjoint dimensions. See the DEFINE COMPOSITE and DEFINE DIMENSION (conjoint) commands.

Examples

Defining a Simple Dimension

This example adds the dimension city to an analytic workspace. You can attach a description to the object immediately after defining it. (You can also add the description later when you use CONSIDER and LD statements.) After defining the dimension city, you can give it values with a MAINTAIN statement.

The statements

DEFINE city DIMENSION ID
LD List of cities
MAINTAIN city ADD 'Boston' 'Chicago' 'Dallas' 'Seattle'
DESCRIBE city

produce the following definition.

DEFINE city DIMENSION ID
LD List of cities