![]() Previous |
![]() Next |
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:
TIMESTAMP
[( truncation-code )]TIMESTAMP_TZ
[( truncation-code )]TIMESTAMP_LTZ
[( truncation-code )]Parameters
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a dimension.
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.
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.
Specifies a special text data type that stores up to 8 single-byte characters for each line in the database character set.
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.
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.
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.
The total number of digits a value of type NUMBER
can have.
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.
Specifies that the values of the dimension have the DATETIME
data type.
Specifies that the values of the dimension have the TIMESTAMP
data type.
Specifies that the values of the dimension have the TIMESTAMP_TZ
data type.
Specifies that the values of the dimension have the TIMESTAMP_LTZ
data type.
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.
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
.
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.
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:
Controlled sparsity occurs when a range of one or more dimensions has no data; for example, a new variable dimensioned by month
for which you do not have data for past months.
Random sparsity occurs when some combinations of dimension values never have any data. For example, a district might only sell certain products and never have data for other products. Other districts might sell some of those products and other ones, too.
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