![]() Previous |
![]() Next |
The DEFINE command with the SURROGATE keyword adds a a new dimension surrogate object to an analytic workspace. A surrogate provides an alternative set of values for a dimension. You can use a surrogate rather than a dimension in a model, in a LIMIT command, in a qualified data reference, or in data loading with statements such as FILEREAD, FILEVIEW, SQL FETCH, and SQL IMPORT.
Note: Defining a surrogate merely adds the definition of the dimension surrogate to the analytic workspace; it does not populate the surrogate. To populate surrogates using the OLAP DML, you can issue OLAP DML SQL, FILEREAD, SET, or SET1 statements. |
Syntax
DEFINE name SURROGATE targetname type [AW workspace] [SESSION]
where type has the following syntax:
[TEXT|NTEXT] [WIDTH n]|ID|INTEGER|NUMBER (precision[, scale] | datatime-datatype)
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 surrogate.
The name of the dimension for which you are creating a surrogate. See "Restrictions on the Use of Surrogates" for points to keep in mind when determining the target.
The data type for a dimension surrogate with text values. When all the values of a dimension surrogate are eight single-byte characters or less, give it a data type of ID. When one or more dimension values has more than eight single-byte characters, you must give it a data type of TEXT or NTEXT. For greater efficiency and ease of use, give dimensions a data type of ID whenever possible.
For TEXT or NTEXT dimension surrogate, 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 surrogate are of similar size. When a value exceeds the specified width, Oracle OLAP truncates it.
The data type for a dimension surrogate with values that are the ordinal positions (1, 2, and so on) of the values in its dimension. You might create an INTEGER type dimension surrogate for a NUMBER
type dimension so that you can specify dimension values by position instead of by the value of the dimension. When you define an INTEGER type dimension surrogate, Oracle OLAP automatically assigns an INTEGER
value to the surrogate for each of the positions in the dimension.
Specifies that the dimension surrogate has a data type of NUMBER
. See "Numeric Data Types" for more information.
Specifies the total number of characters in the value of a dimension surrogate of type NUMBER
.
Specifies the number of characters that can be to the right of a decimal point of a dimension surrogate of type NUMBER
.
Specifies a datetime data type (that is, DATETIME
, TIMESTAMP
, TIMESTAMP_TZ
, or TIMESTAMP-LTZ
). See "Datetime and Interval Data Types" for more information.
The name of an attached workspace in which you want to define the dimension surrogate. The dimension for which you define the surrogate 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. Use this keyword when the definition of the targetname dimension includes SESSION.
Usage Notes
Restrictions on the Use of Surrogates
Keep the following restrictions in mind when determining a target for your surrogate:
You cannot create a surrogate for a dimension that has a type of DAY, WEEK, MONTH, QUARTER, or YEAR or for a composite.
When you create a surrogate for a conjoint, you cannot convert the conjoint to a composite.
You cannot specify a dimension surrogate as the dimension or related dimension argument when you define a concat dimension, a formula, a program, a relation, a valueset, or a variable. Additionally, in data loading you cannot create new dimension values using a dimension surrogate
Examples
Creating an INTEGER Dimension Surrogate
The following statement creates an INTEGER type dimension surrogate for the store_id
dimension.
DEFINE storepos SURROGATE store_id INTEGER
Creating a NUMBER Dimension Surrogate
The following statement creates an NUMBER
type dimension surrogate for the product
dimension, which is a TEXT dimension that has product names as values. The precision argument to the NUMBER keyword specifies that a value in prodnum
can have no more than seven characters and the scale argument specifies that no more than three characters can be to the right of the decimal point.
DEFINE prodnum SURROGATE product NUMBER(7, 3)
The following statement sets the first value of prodnum
to 1083.375
.
prodnum(product 1) = 1083.375