Previous
Previous
 
Next
Next


DEFINE SURROGATE

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

name

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

SURROGATE

The object type when you are defining a dimension surrogate.

targetname

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.

TEXT
NTEXT
ID

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.

WIDTH n

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.

INTEGER

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.

NUMBER

Specifies that the dimension surrogate has a data type of NUMBER. See "Numeric Data Types" for more information.

precision

Specifies the total number of characters in the value of a dimension surrogate of type NUMBER.

scale

Specifies the number of characters that can be to the right of a decimal point of a dimension surrogate of type NUMBER.

datetime_datatype

Specifies a datetime data type (that is, DATETIME , TIMESTAMP, TIMESTAMP_TZ, or TIMESTAMP-LTZ). See "Datetime and Interval Data Types" for more information.

AW workspace

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.

SESSION

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