DEFINE

The DEFINE command adds a new object to the analytic workspace. This entry describes the DEFINE command in general. The following entries discuss the use of the DEFINE command for creating specific types of object:

• DEFINE AGGMAP

• DEFINE COMPOSITE

• DEFINE DIMENSION

  • DEFINE DIMENSION (simple)

  • DEFINE DIMENSION (conjoint)

  • DEFINE DIMENSION CONCAT

  • DEFINE DIMENSION ALIASOF

• DEFINE FORMULA

• DEFINE MODEL

• DEFINE PARTITION TEMPLATE

• DEFINE PROGRAM

• DEFINE RELATION

• DEFINE SURROGATE

• DEFINE VALUESET

• DEFINE VARIABLE

• DEFINE WORKSHEET

Syntax

DEFINE name object-type attributes [AW workspace] [SESSION]

Arguments

name

A TEXT expression that is the name for the new object. Follow these guidelines when specifying a value for name:

• The name must consist of 1 to 64 characters. When you are using a multibyte character set, you can still specify 64 characters even when this requires more than 64 bytes. Each character may be a letter (A-Z), a number (0-9), an underline (_), or a dot (.). However, the following restrictions apply to the use of these characters:

  • The name cannot consist of a single dot (.) character or a single underscore (_) character.

  • The name cannot duplicate a reserved word. For more information on identifying reserved words, see the RESERVED function.

  • The first character in the name cannot be a number.

  • The first character cannot be a dot (.) when the second character is a number.

• By default Oracle OLAP creates the definition in the current workspace. To create the definition in a different attached workspace, you can specify a qualified object name for name or you can use the AW argument to specify the workspace. Do not use both.

object-type

The type of object being defined. The default is VARIABLE. The object types are discussed in the subsections for the DEFINE command.

attributes

Attributes are different for each type of object. The attributes are listed in the entry for each object type.

AW workspace

The name of an attached workspace in which you wish to define the object. You can also specify a noncurrent attached workspace using a qualified object name for name. Do not use this phrase when qualified object name for name.

SESSION

Specifies that the object exists only in the current session. The object is created in the EXPRESS analytic workspace to which you have read-only access. When you close the current session, the object no longer exists.

Notes

Triggering Program Execution When DEFINE Executes

Using a TRIGGER_DEFINE program, you can make the DEFINE command an event that will automatically execute an OLAP DML program. See "Trigger Programs" for more information.

Effect of DEFINE on the Status of the NAME Dimension

When you execute a DEFINE command with the NAME dimension limited to less than all its values, the status of NAME is automatically limited to ALL.

Viewing Session Objects

Objects created with the SESSION keyword are stored in the analytic workspace named EXPRESS instead of the current analytic workspace. Therefore, statements that operate against the current analytic workspace (such as LISTNAMES) do not list session objects unless you do one of the following:

• Specify the EXPRESS analytic workspace in the statement (such as LISTNAMES AW EXPRESS)

• Make the EXPRESS analytic workspace the current analytic workspace by issuing an AW ATTACH EXPRESS statement.

DEFINE AGGMAP

The DEFINE command with the AGGMAP keyword adds a new aggmap object to an analytic workspace. An aggmap object is a specification for how Oracle OLAP allocates or aggregates variable data.

Defining an aggmap merely creates an aggmap object in the analytic workspace; it does not define the calculation specification. The aggmap specification can either specify how to aggregate or how to allocate data:

• For information on coding an aggregation specification, see the AGGMAP command.

• For information on coding an allocation specification, see the ALLOCMAP command.

Syntax

DEFINE aggname AGGMAP [<dims...>][AW workspace][SESSION]

Arguments

aggname

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

AGGMAP

The object type when you are defining an aggmap.

dims

(Optional; retained for compatibility with earlier software versions.) When defining an aggmap object for aggregation (that is, an AGGMAP-type aggmap), the names of the dimensions. You cannot specify a conjoint dimension as a base dimension in the definition or specification for the aggmap.

AW workspace

The name of an attached workspace in which you wish to define the object. For more about this argument, see the main entry for the DEFINE command.

SESSION

Specifies that the object exists only in the current session. For more information about this argument, see the main entry for the DEFINE command.

Examples

DEFINE sales VARIABLE <time, product, geography>

DEFINE sales.agg AGGMAP <time, product, geography>
AGGMAP
RELATION time.r PRECOMPUTE (time NE 'Year99')
RELATION product.r PRECOMPUTE (product NE 'All')
RELATION geography.r
CACHE STORE
END

AGGREGATE sales USING sales.agg

DEFINE sales VARIABLE <time, product, geography>

DEFINE sales.alloc AGGMAP
ALLOCMAP
RELATION time.r OPERATOR EVEN
RELATION product.r operator EVEN NAOPERATOR HEVEN
SOURCEVAL ZERO
CHILDLOCK DETECT
END

INFILE 'salesalloc.txt'

ALLOCATE sales USING sales.alloc

DEFINE COMPOSITE

The DEFINE command with the COMPOSITE keyword adds a new named composite to an analytic workspace. Conceptually, you can think of a composite consisting of two structures:

• The composite object itself. The composite contains the dimension-value combinations (that is, a composite tuples) that Oracle OLAP uses to determine the structure of any variables dimensioned by the composite.

• An index between the composite values and its base dimension values.

For a variable that is dimensioned by composite, Oracle OLAP creates array elements (that is, variable cells) only for those dimension values that are stored in the tuples of the composite; it does not create a cell for every value in the base dimensions. Data for the variable is stored in order, cell by cell, for each tuple in the composite. From the perspective of data storage, each combination of base dimension values in a composite is treated like the value of a regular dimension. This means that when you define a variable with one regular dimension and one composite, the data for the variable is stored as though it was a two-dimensional variable. Using composites to reduce the number of elements created for a variable results in more efficient data storage.

Syntax

DEFINE name COMPOSITE <dims...> [AW workspace] [index-algorithm] [SESSION]

where index-algorithm specifies the algorithm that Oracle OLAP uses to create an index that relates the composite values to its base dimension values. When you omit this optional argument, Oracle OLAP uses the value specified by the SPARSEINDEX option. Valid values for index-algorithm are:

BTREE

BTREE64

COMPRESSED

HASH

Arguments

name

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

COMPOSITE

The object type when you are defining a named composite.

dims

The names of two or more dimensions that you want to be the base dimensions of the composite. When you specify COMPRESSED as the value of index-algorithm, at least one of the dimensions must be a hierarchal dimension.

The order of the dimensions in dims varies by the value you specify for index-algorithm:

• For b-tree or hash composites, specify the dimensions in fastest to slowest-varying order as discussed in "Effect of Dimension Order on Variable Storage and Statement Looping".

• For compressed composites, it does not matter in which order you specify the dimensions. Oracle OLAP selects the order in which to store the values unless you override this optimization by specifying FORCEORDER in an AGGREGATE command or AGGREGATE function. To see the optimized order chosen by Oracle OLAP, view the POUTFILELOG.

You must define all the dimensions and named composites used in the list before defining the composite. DEFINE will automatically create any unnamed composites in the list for you.

AW workspace

The name of an attached workspace in which you wish to define the object. For more information about this argument, see the main entry for the DEFINE command.

BTREE

Specifies the creation of a b-tree index to relate composite values to base dimension values. BTREE is the standard indexing method for composites. For a variable that is dimensioned by a BTREE composite, Oracle OLAP creates array elements (that is, variable cells) only for those dimension values that are stored in the tuples of the composite; it does not create a cell for every value in the base dimensions.

BTREE64

Specifies the creation of a highly-scalable b-tree index to relate composite values to base dimension values. For a variable that is dimensioned by a BTREE64 composite, like a BTREE composite, Oracle OLAP creates array elements (that is, variable cells) only for those dimension values that are stored in the tuples of the composite; it does not create a cell for every value in the base dimensions. However, unlike a BTREE composite, a BTREE64 composite supports b-trees greater than 2 gigabytes.

Note:

Typically, you define a BTREE64 composite when you want to use it to dimension a variable which you will populate from a relational table that is larger than 2 gigabytes.

COMPRESSED

Specifies the creation of a compressed index to relate composite values to base dimension values. You specify COMPRESSED only when you want to create a composite for a variable that has at least one hierarchical dimension is specified in dims and that will be aggregated.

A compressed composite contains one composite tuple for each set of base dimension values that identifies non-NA detail data in the variables that use it. Additionally, for variables dimensioned by compressed composite Oracle OLAP reduces redundancy in the variable, composite, and composite index by creating a physical position in the composite only for those tuples that represent a parent with more than one descendant. Oracle OLAP then creates an index between this composite structure and the base dimensions and uses this composite structure as the dimension of the variable. Since the actual structure of a compressed composite is smaller than that of a b-tree or hash composite, a variable dimensioned by a compressed composite is also smaller than a variable dimensioned by a b-tree or hash composite. Also, since the index for a compressed composite only has nodes for parents with more than one descendant, the index of a compressed composite has fewer levels and is smaller than the index of a b-tree composite. Although performance varies depending on the depth of the hierarchies and the order of the dimensions in the composite, aggregating variables defined with compressed composites is typically much faster than aggregating variables defined with b-tree or hash composites.

Note:

Oracle OLAP compresses the data in variables dimensioned by compressed composites using the "intelligence" of the AGGREGATE command or AGGREGATE function. Consequently, there are special considerations that apply when aggregating a variable dimensioned by one or more compressed composites. See "Aggregating Variables Dimensioned by Compressed Composites" for more information.

HASH

Specifies the creation of a hash index to relate composite values to base dimension values. HASH is rarely used and, then, typically, only when the composite has two or three dimensions. For a variable that is dimensioned by a b-tree or hash composite, Oracle OLAP creates array elements (that is, variable cells) only for those dimension values that are stored in the tuples of the composite; it does not create a cell for every value in the base dimensions.

SESSION

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

Notes

Shared Composites

You can use the same b-tree or hash composite to dimension several variables. (Compressed composites cannot be shared in this manner.) The actual sparsity of a variable dimensioned by a b-tree or hash composite varies depending on whether or not the composite is an unshared composite or a shared composite:

• An unshared composite is a composite that is used to dimension only one variable. All types of composites (that is, b-tree, hash, and compressed composites) can be unshared composites. An unshared composite is populated only when the variable that uses it is populated. Consequently, an unshared composite perfectly reflects the sparsity of the variable that it is used to dimension. It only has the dimension value combinations for each non-NA value in that variable.

• A shared composite is a composite that is used to dimension more than one variable. A shared composite can be either a b-tree or hash composite; it cannot be a compressed composite. A shared composite is populated when any of the variables that use it are populated. A shared composite has all of the dimension value combinations for non-NA values for all of the variables that it dimensions. A shared composite reflects the sparsity of all of the variable that it is used to dimension. Typically, therefore, variables dimensioned by shared composites are not perfectly sparse variables.

When the size of variables is important, when you have variables that will be sparse along the same dimensions, but will have significantly different patterns of sparsity, define different composites for the different variables.

Examples

This section contains a simple example of creating a named b-tree composite. For examples of using composites to dimension variables, see Example 9-98, "Defining a Variable Dimensioned by an Uncompressed Composite" and Example 9-99, "Defining a Variable Dimensioned by a Compressed Composite".

DEFINE market.product COMPOSITE <market product>
DEFINE expenses DECIMAL <month market.product <market product>> 

DEFINE DIMENSION

The DEFINE command with the DIMENSION keyword adds a new dimension object to an analytic workspace. A dimension is a list of values that provides an index to the data.

Because the syntax of the DEFINE DIMENSION command is different depending on the type of the dimension that you are defining, four separate entries are provided:

• DEFINE DIMENSION (simple) for defining a dimension with unique values of the same data type.

• DEFINE DIMENSION (DWMQY) for defining a non-hierarchical dimension whose values represent a time period (day, week, month, quarter, or year).

• DEFINE DIMENSION (conjoint) for defining a dimension over two or more other base dimensions when the base dimensions do not contain duplicate values or have different data types and when you want to explicitly specify the dimension value combinations.

• DEFINE DIMENSION CONCAT for defining a dimension over two or more other base dimension when the base dimensions contain duplicate values or different data types or when you want Oracle OLAP to automatically populate the dimension value combinations.

• DEFINE DIMENSION ALIASOF for defining an alias for a simple dimension.

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.

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

Arguments

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 4000 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 4000 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 will be 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, you should 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 one of the format models shown in Table 8-6, "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 wish 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.

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

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

DEFINE city DIMENSION ID
LD List of cities

DEFINE DIMENSION (DWMQY)

The DEFINE DIMENSION (DWMQY) command defines a DWMQY dimension (that is a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR) whose values represent time periods. After defining a DWMQY dimension, you can use a VNF statement to add a value name format to the dimension's definition. The VNF command controls the format for entering dimension values as well as the format for showing them in output.

Syntax

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

where dwmqy is the time period of the dimension. The valid types for dwmqy are DAY, WEEK, MONTH, QUARTER, and YEAR. Each type indicates the span of the time period represented by the individual dimension values of the dimension. The syntax of dwmqy varies depending on the type:

     DAY 

     [multiple] WEEK [BEGINNING phase ] [ ENDING phase ] 

     [multiple] MONTH [BEGINNING phase ] [ ENDING phase ] 

     QUARTER [BEGINNING phase ] [ ENDING phase ] 

     YEAR [BEGINNING phase ] [ ENDING phase ] 

Arguments

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.

multiple

For the WEEK and MONTH types, specifies time periods that span a multiple number of weeks or months. With the WEEK keyword, multiple can be an INTEGER from 2 to 52. With the MONTH keyword, multiple can be 2, 3, 4, or 6.

BEGINNING phase

ENDING phase

Specifies the beginning or ending phase of a WEEK, MONTH, QUARTER, or YEAR dimension:

• For single weeks, phase can be a day of the week (corresponding to a name in the DAYNAMES option) or a date.

• For multiple weeks, phase must be a date.

• For months, quarters, or years, phase must be a month, expressed as a month name (corresponding to a name in the MONTHNAMES option) or as a date.

When you specify phase as a date, you give the month, day, and year, enclosed in single quotes, using any of the input styles that are valid for variable values with a data type of DATE. When you specify a date with an ambiguous meaning (such as '03 05 97'), the date is interpreted according to the current setting of the DATEORDER option.

Note:

When you define a multiple-period dimension of type WEEK but you do not specify a BEGINNING or an ENDING argument, DEFINE automatically supplies a phase that begins with the date '31DEC1899'.

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

Notes

Implicit Relations Between DWMQY Dimensions

When you define two or more dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR, Oracle OLAP automatically defines implicit relations between the values of the dimensions. For example, when you define a dimension of type MONTH and a dimension of type YEAR, Oracle OLAP automatically defines a relation that associates all the MONTH values that fall within a particular year with the corresponding value of the YEAR dimension.

Using BEGINNING or ENDING Phase to Organize Data by Fiscal Calendar

For dimensions of type MONTH, QUARTER, and YEAR, the BEGINNING phase or ENDING phase argument is especially useful for data organized on a fiscal-year calendar.

By specifying a phase for a dimension of type MONTH or QUARTER, you identify the time period that is the first or last period within a year. For example, when you define a dimension of type MONTH with an ending phase of June, then June is identified as the twelfth month of the year. When a dimension of type QUARTER has an ending phase of June, the quarter ending in June is identified as the fourth quarter of the year. When you give a dimension a VNF that includes a period code, you can enter or report dimension values according to their period within the year.

By default, the single or multiple weeks in a dimension of type WEEK end on Saturday. The BEGINNING phase or ENDING phase argument lets you specify the day of the week on which each period begins or ends. For multiple-week periods, the phase argument also controls the starting or ending date for grouping the weeks into periods. By default, the starting point for grouping multiple weeks is December 31, 1899 (a Sunday).

However, the phase argument does not determine the period that is counted as the first period within a year. For dimensions of type WEEK, Period 1 in a given calendar year is always the first period that ends in that year. For example, suppose you specify a dimension of type WEEK with a four-week period ending on June 7, 1997. DEFINE works backward and forward from this date, forming weeks into four-week periods. For 1997, Period 1 will be the period beginning on December 22, 1996 and ending on January 18, 1997.

Examples

DEFINE fyear DIMENSION YEAR ENDING june

LD Fiscal years ending June 30
VNF 'FY<ff>'
MAINTAIN fyear ADD 'FY97' 'FY00' 

DEFINE twoweek DIMENSION 2 WEEK
DESCRIBE TWOWEEK

DEFINE twoweek DIMENSION 2 WEEK ENDING '13Jan1900'

DEFINE DIMENSION (conjoint)

The DEFINE DIMENSION (conjoint) command defines a conjoint dimension.

Conceptually, you can think of a conjoint dimension consisting of two structures:

• The dimension object itself. The values of the dimension are combinations of values of two or more other dimensions (that is, a conjoint tuples) that Oracle OLAP uses to determine the structure of any variables dimensioned by the conjoint dimension.

• An index between the conjoint dimension values and its base dimension values.

Composites are another object that you can use to dimension a variable using a list of dimension value combinations. See "Differences Between Conjoint Dimensions and Composites" for a discussion of the major differences between composites and conjoint dimensions.

Syntax

DEFINE name DIMENSION <dims. . .> index-algorithm  [AW workspace] [SESSION]

where index-algorithm specifies the algorithm that Oracle OLAP uses to create the index into the conjoint dimension. Valid values for index-algorithm are:

BTREE

NOHASH

HASH

Arguments

name

The name of the conjoint dimension 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 conjoint dimension.

dims

One or more previously defined dimensions that are the base dimensions of the conjoint dimension. Specify the dimensions in fastest to slowest-varying order as discussed in "Effect of Dimension Order on Variable Storage and Statement Looping". You must enclose the dimension list in angle brackets.

Typically, a base dimension of a conjoint dimension is a simple dimension, but it can also be another conjoint dimension. However, when you do have a simple dimension for one of the values of dims, you cannot also specify for dims a conjoint or concat dimension that has same simple dimension as one of its bases.

BTREE

Specifies the creation of a b-tree index to relate conjoint values to base dimension values. Typically, you specify BTREE as the index algorithm for a conjoint dimension.

Tip:

When you are unsure whether to specify BTREE or NOHASH, use NOHASH, since you can always use a CHGDFN statement to change a NOHASH conjoint into a BTREE conjoint, while you can use a CHGDFN statement to change a BTREE conjoint into a NOHASH conjoint only when the conjoint was originally defined as a NOHASH conjoint

NOHASH

Specifies that Oracle OALP does not create an index for the conjoint dimension, but instead uses internal structures to relate conjoint values to base dimension values. Because no index is created for NOHASH, NOHASH decreases the number of structures associated with the conjoint dimension; and, in many cases, decreases the time it takes to load and access conjoint dimension values. However, NOHASH is used infrequently, as it is a complicated algorithm that, on occasion, can result in unpredictable performance.

HASH

(Default, but not recommended.) Specifies the creation of a has index to relate conjoint values to base dimension values.

Tip:

Even though HASH is the default, typically, you specify BTREE as the index algorithm for a conjoint dimension. When your conjoint dimension has more than 3 base dimensions, for best performance, use BTREE instead of HASH.

AW workspace

The name of an attached analytic workspace in which you wish 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.

Notes

Differences Between Conjoint Dimensions and Composites

You can use either a composite or a conjoint dimension to dimension a variable with a list of dimension value combinations. Keep the following points in mind when deciding on which type of object to use:

• Object population maintenance—Conjoint dimensions offer the most control, while composites provide the greatest ease of use:

  • Oracle OLAP determines the dimension value combinations stored in a composite. Oracle OLAP populates a composite automatically when a variable dimensioned by composite is populated.

  • You determine the dimension value combinations that are stored in a composite. You must explicitly populate and maintain a conjoint dimension using MAINTAIN statements the same way you populate and maintain other dimensions.

• Dimension operations —You can perform dimension operations on conjoint dimensions, but not composites; however, you can only perform dimension operations on the base dimensions of composites. For example, you can LIMIT conjoint dimensions, but you must limit the base dimensions of a composite to limit your view to a subset of composite values; and you can define relations using conjoint dimensions, but not composites.

For more information on composites, see the DEFINE COMPOSITE command.

Relationship of Conjoint Dimensions to Base Dimensions

The values of the conjoint dimension are related to the base dimensions. You can specify data in a variable dimensioned by the conjoint dimension using the conjoint value combinations, the individual values of the base dimensions, or other dimensions related to either of the base dimensions of the conjoint dimension.

Defining a Subset of a Dimension's Values

You can have a conjoint dimension with only one base dimension, which enables you to create a subset of that dimension's values. You must still enclose that one base dimension within angle brackets.

Using Conjoint Dimension Values in Expressions

To refer to the value of a conjoint dimension in an expression, specify the value following these guidelines:

• Enclose the entire dimension value specification in angle brackets and then enclose this entire specification in single quotes; do not enclose the individual values in single quotes.

• Use the exact upper- and lowercase spellings for the base dimension values.

• When the specification includes a text value with an embedded blank, you must separate the dimension values with commas.

For example, when item.org is a conjoint dimension with base dimensions item and org, use the following format to refer to values of item.org.

'<Expenses, Direct Sales>'

Examples

CITY             STATE           REGION
---------       ----------       ------
Princeton       New Jersey       East
Newark          New Jersey       Central
Patterson       New York
New York        Illinois
Chicago         Indiana

DEFINE cityandstate DIMENSION <city state>
MAINTAIN cityandstate add <'Princeton' 'New Jersey'>
MAINTAIN cityandstate add <'Newark' 'New Jersey'>
MAINTAIN cityandstate add <'Patterson' 'New Jersey'>
MAINTAIN cityandstate add <'New York' 'New York'>
MAINTAIN cityandstate add <'Chicago' 'Illinois'>
MAINTAIN cityandstate add <'Princeton' 'Indiana'>

DEFINE DIMENSION CONCAT

The DEFINE DIMENSION CONCAT commands defines a concat dimension. A concat dimension is a dimension that groups a set of base dimensions with duplicate values or different data types into one dimension.

When there are duplicate data values, you create a non-unique concat dimensions. For example, you would create a nonunique dimension for a geography hierarchy when "New York" is both the value at the city level and at the state level. When all of the data values in all of the base dimensions are unique, you can create a unique concat dimension.

Syntax

DEFINE name DIMENSION CONCAT(basedimlist. . .)[UNIQUE] [TEMP] [AW workspace] [SESSION]

Arguments

name

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

DIMENSION CONCAT

The object type when you are defining a concat dimension.

basedimlist

One or more previously-defined dimensions that are the base dimensions of the concat dimension. Specify the dimensions in fastest to slowest-varying order as discussed in "Effect of Dimension Order on Variable Storage and Statement Looping". You must enclose the dimension list in parenthesis.

The types of dimensions that can be base dimensions varies depending on whether you are defining a unique or nonunique concat dimension:

• When defining a non-unique concat dimension, a base dimension can be a simple dimension of any data type, a conjoint dimension, or another concat dimension.

• When defining a unique concat dimension, a base dimension can be a simple dimension of type TEXT or ID, or another unique concat dimension as long as the data values of all of the base dimensions are unique and not duplicated in any of the base dimensions.

A composite cannot be the base dimension of a concat dimension.

Simple dimensions and conjoint dimensions are the bottom-level components of a concat dimension. When you specify a concat dimension as a base dimension when defining a concat, then the base dimensions of that inner concat are component dimensions of the outer concat.

The same dimension cannot appear more than once in the component dimensions of a concat dimension. However, in a concat, a conjoint dimension is an indivisible unit and Oracle OLAP does not consider the base dimensions of a conjoint in the definition of the concat. Therefore, a simple dimension can be a base dimension of a conjoint and that conjoint and the same simple dimension can be base dimensions (or components) of a concat dimension.

For example, the following definitions are permissible.

DEFINE conjointdim.a DIMENSION <simpledim.b, simpledim.c>
DEFINE conjointdim.b DIMENSION <simpledim.a, simpledim.b>
DEFINE conjointdim.c DIMENSION <simpledim.a, conjointdim.a>
DEFINE concatdim.a DIMENSION CONCAT (simpledim.a, conjointdim.a)
DEFINE concatdim.b DIMENSION CONCAT (simpledim.a, conjointdim.b)
DEFINE concatdim.c DIMENSION CONCAT (simpledim.b, conjointdim.b)
DEFINE concatdim.d DIMENSION CONCAT (simpledim.a, concatdim.c)

In the definition of concatdim.a, the base dimensions are simpledim.a and conjointdim.a. In the definition of concatdim.d, the base dimensions are simpledim.a and concatdim.c. The component dimensions of concatdim.d are simpledim.a, simpledim.b, and conjointdim.b. simpledim.a and simpledim.b appear only once as component dimensions even though they are the base dimensions of conjointdim.b because the base dimensions of a conjoint are not component dimensions of a concat.

However, the following definition is not permitted because the same simple dimension is a base dimension of concatdim.e and a component of concatdim.e because it is a base dimension of concatdim.b.

DEFINE concatdim.e DIMENSION CONCAT (simpledim.a, concatdim.b)

Note:

The simple dimensions in the basedimlist argument, and the simple dimensions that are base dimensions of any conjoint dimensions or concat dimensions in basedimlist, cannot have an INTEGER data type.

UNIQUE

Specifies that the text values of the base dimensions are unique. When you specify this keyword, the dimensions listed in basedimlist must be either simple text or ID dimensions or unique concat dimensions.

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

Examples

CITY             STATE           REGION
---------       ----------       ------
Princeton       New Jersey       East
Newark          New Jersey       Central
Patterson       New York
New York        Illinois
Chicago         Indiana

DEFINE geog DIMENSION CONCAT(region cityandstate)

<REGION: East>
<REGION: Central>
<CITYANDSTATE: <Princeton New Jersey>>
<CITYANDSTATE: <Newark New Jersey>>
<CITYANDSTATE: <Patterson New Jersey>>
<CITYANDSTATE: <New York New York>>
<CITYANDSTATE: <Chicago Illinois>>
<CITYANDSTATE: <Princeton Indiana>>

DEFINE DIMENSION ALIASOF

The DEFINE DIMENSION ALIASOF command defines a dimension alias for a simple dimension. An alias dimension has the same type and values as its base dimension. Typically, you define an alias dimension when you want to dimension a variable by the same dimension twice.

Additionally, You can use a LIMIT statement to limit alias dimensions and define variables and relations using an alias dimension. However, you cannot maintain an alias dimension directly; instead you maintain its base dimension using MAINTAIN.

Syntax

DEFINE name DIMENSION ALIASOF dimension [TEMP] [AW workspace] [SESSION]

Arguments

name

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

DIMENSION ALIASOF

The object type when you are defining a dimension. Indicates that the dimension being defined is an alias for another dimension.

dimension

The name of a simple dimension for which you want to define an alias. This dimension cannot be a concat or conjoint dimension, composite, or surrogate.

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

Examples

DEFINE year DIMENSION TEXT
DEFINE project DIMENSION TEXT
DEFINE employee DIMENSION TEXT
DEFINE leader DIMENSION ALIASOF employee
DEFINE hours VARIABLE INTEGER <year project employee leader>

MAINTAIN year ADD '2001' '2002' '2003'
MAINTAIN project ADD 'projA' 'projB'
MAINTAIN employee add 'Adams' 'Baker' 'Charles'

EMPLOYEE
--------------
Adams
Baker
Charles
 
LEADER
--------------
Adams
Baker
Charles

LIMIT year TO '2001'
LIMIT employee TO 'Adams'
LIMIT project TO 'projA'
REPORT DOWN leader ACROSS employee: hours
 
PROJECT: projA
YEAR: 2001
               --HOURS---
               -EMPLOYEE-
LEADER           Adams
-------------- ----------
Adams                   1
Baker                   2
Charles                 1

LIMIT employee TO ALL
LIMIT leader TO 'Adams'
LIMIT project TO 'projA'
REPORT DOWN leader ACROSS employee: hours
 
PROJECT: projA
YEAR: 2001
               -------------HOURS--------------
               ------------EMPLOYEE------------
LEADER           Adams      Baker     Charles
-------------- ---------- ---------- ----------
Adams                   1          3          3

DEFINE FORMULA

The DEFINE command with the FORMULA keyword adds a new formula object to an analytic workspace. You define a formula to save an expression. A formula can take the place of an expression you use repeatedly. The name of the formula takes the place of the text of the expression. Oracle OLAP does not store the data for a formula in a variable; instead it is calculated at runtime each time it is requested.

Syntax

DEFINE name FORMULA {expression | datatype [<dimensions...>]} [AW workspace] [SESSION]

Arguments

name

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

FORMULA

The object type when you are defining a formula.

expression

The calculation to be performed to produce values when you use the formula. It can be any valid expression, including a constant or the name of a variable as described in Chapter 2, "OLAP DML Expressions".

You can specify an expression for a formula when you define it or after you define using an EQ statement. When you define a formula without specify an expression, a formula returns NA with the specified data type.

Note:

Oracle OLAP does not automatically convert text in a formula to uppercase.

datatype

The intended data type for the formula when you do not specify a value for expression. You can use any of the data types that apply to variables.

When you include an expression in the formula definition, DEFINE automatically determines the data type for a formula defined using expression. Later, when you add the expression using an EQ statement, its data type should match the type you specify now. When it does not, DEFINE converts the output to the specified type.

dimensions

The dimensions of the formula. Enclose the list in angle brackets. The dimensions argument is optional. When the formula is a single-cell value, you do not specify any dimensions. Also, when you include an expression in the definition, you do not specify a value. DEFINE automatically determines the dimensions.

However, when you do not include an expression in the definition, you must specify the dimensions. When you add the expression later using an EQ statement, the expression must have the same dimensions as the formula definition. When it does not, DEFINE forces the output to have the specified dimensions.

Restriction:

You cannot define a formula that is dimensioned by a composite.

AW workspace

The name of an attached workspace in which you wish to define the formula. When the formula is dimensioned, it must be defined in the same workspace as its dimensions. 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.

Notes

Effect of Changing the Characteristics of Objects Used by a Formula

When you change the name, data type, or dimensions of any of the objects used by a formula, the formula is not automatically updated. The formula causes an error when objects it refers to have been deleted or are now the wrong data type.

Storing Complex Expressions and Calculations

To define a very complex calculation, you can define a program that uses a RETURN statement to return a value. You can then use the program as a function wherever you would use an expression or formula.

Examples

DEFINE sales.diff FORMULA LAGPCT(TOTAL(actual year) 1 year)
DESCRIBE sales.diff

DEFINE sales.diff FORMULA DECIMAL <year>
EQ lagpct(TOTAL(actual year) 1 year) 

DEFINE MODEL

The DEFINE command with the MODEL keyword adds a new model object to an analytic workspace. A model is a set of interrelated equations. The calculations in an equation can be based either on variables or on dimension values. You can assign the results of the calculations directly to a variable or you can specify a dimension value for which data is being calculated. For example, in a financial application, all the equations might be based on the values of a line item dimension, and data would be calculated for line items such as total expenses and net income.

Syntax

DEFINE name MODEL [AW workspace] [SESSION]

Arguments

name

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

MODEL

The object type when you are defining a model.

AW workspace

The name of an attached workspace in which you wish to define the object. For more 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.

Examples

DEFINE income.calc MODEL
LD Model for calculating Income Statement items

DEFINE income.calc MODEL
LD Model for calculating Income Statement items
MODEL
dimension line
net.income = opr.income - taxes
opr.income = gross.margin - (marketing+selling+r.d)
gross.margin = revenue - cogs
END

income.calc actual 

DEFINE PARTITION TEMPLATE

The DEFINE command with the PARTITION TEMPLATE keywords adds a new partition template object to an analytic workspace. A partition template is a specification for the partitions of a partitioned variable. A partitioned variable is stored as multiple rows in the relational table of LOBs that is the analytic workspace—each partition is a row in the table. You define both partitioned and unpartitioned variables using DEFINE VARIABLE statements. Before you can define a partitioned variable you must first define a partition template object.

Syntax

DEFINE name PARTITION TEMPLATE <dimlist>  PARTITION BY

     {RANGE|LIST} (dims_partitioned_by)  ([partition_definition_statement...]) [AW workspace]

where partition_definition_statement defines a partition. The syntax varies depending on whether you specify RANGE or LIST:

• For RANGE:

  PARTITION partition-name VALUES LESS THAN const-exp <partition-dimlist>

• For LIST:

  PARTITION partition-name VALUES ([valuelist)] <partition-dimlist>

Arguments

name

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

dimlist

A list of all of the logical dimensions for the variable that you are partitioning. You must enclose the names of the dimensions in a single set of angle brackets (< >). You must define a dimension before you can include it in the definition of a partition template.

dims_partitioned_by

The subset of dimensions specified by dimlist that actually specify the partitions of the variable. For range and list partitioning (that is, when you specify either the RANGE or LIST keywords), you can specify only one dimension for dims_partitioned_by. You cannot partition a variable along an INTEGER dimension.

PARTITION partition-name

The name of the partition.

VALUES LESS THAN

Indicates that you are specifying a RANGE partition by comparing values.

constant-exp

A constant expression that has the same data type as the data type of the dimension specified for dims_partitioned_by.

partition-dimlist

A list of all of the of dimensions of the partition template object (although the dimensions may be members of a composite). You must enclose the names of the dimensions in a single set of angle brackets (< >). Use this argument to specify the composite (if any) used to dimension the partitions that correspond to partition-name. When you do not specify a value then the partition is dimensioned densely by all of the of dimensions of the partition template object.

VALUES

Indicates that you are specify a LIST partition by specifying values.

valuelist

A list of dimension values, separated by commas. You must surround text values with single quotes (for example, 'mytext'). Specify values of conjoints by specify the values of the base dimensions, separated by a comma, in a single set of angle brackets (for example, <'Value1', 'Value2'>). Specify values of nonunique concat dimensions by specify the values of the base dimensions, separated by a colon, in a single set of angle brackets (for example, <'Value1': 'Value2'>).

Tip:

I f you want to use a valueset object to specify values, do not specify values for valuelist. Instead, omit valuelist from the partition template definition and use a MAINTAIN ADD TO PARTITION statement to specify values for the partition.

Examples

See Example 9-100, "Defining a Variable with Partitions".

DEFINE PROGRAM

The DEFINE command with the PROGRAM keyword adds a new OLAP DML program object to an analytic workspace. An OLAP DML program is a collection of OLAP DML statements that helps you accomplish some workspace management or analysis task. Defining a program merely creates a program object in the analytic workspace. You must also code the actual lines of the program.

Syntax

DEFINE name PROGRAM [datatype|dimension] [AW workspace] [SESSION]

Arguments

name

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

PROGRAM

The object type when you are defining a program.

datatype

The data type of the value to be returned by the program when it is called as a function. You can use any of the data types that apply to variables.

dimension

The name of a dimension, whose value the program returns when it is called as a function. The return value is a single value of the dimension, not a position (INTEGER). The dimension must be defined in the same workspace as the program.

AW workspace

The name of an attached workspace in which you wish to define the program. When the program returns a dimension, the program must be defined in the same workspace as the dimension. 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.

Notes

Returning Values

Use a RETURN statement in a program when you want it to return a value. The argument to the RETURN statement is an expression that specifies the value to return. When the expression does not match the declared data type or dimension, the value is converted (if possible) to the declared data type or dimension value.

When you do not specify a data type or dimension in the definition of a program, its return value is treated as worksheet data. This means Oracle OLAP converts any return value to the data type required by the calling context. This may lead to unexpected results.

For a program to return a value, you must call the program as a function. That is, you must use it as an expression in a statement. In the following example, the program isrecent is being treated as a function. It is an argument to the REPORT command.

REPORT isrecent(actual)

When the program returns values of a dimension, the program is in the output of the LISTBY function, and OBJ(ISBY) is TRUE for the dimension.

See the entries for the ARGUMENT, CALL, and RETURN commands for more information about programs as user-defined functions.

Examples

DEFINE SALESEVAL PROGRAM
PROGRAM
ARGUMENT onemonth MONTH
VARIABLE excess DECIMAL
ALLSTAT
LIMIT month TO onemonth
IF TOTAL(sales, month) GT TOTAL(sales.plan, month)
   THEN DO
     excess = (TOTAL(sales, month) - 
       - TOTAL(sales.plan, month)) -
       / TOTAL(sales.plan, month) * 100
     SHOW JOINCHARS('Sales exceeded plan by ' excess '%.') 
     DOEND
ELSE SHOW JOINCHARS('We\'re not meeting plan. ' -
   'Let\'s get working!')
REPORT DOWN product W 10 ACROSS district: sales - sales.plan
END

DEFINE RELATION

The DEFINE command with the RELATION keyword adds a new relation object to an analytic workspace. A relation describes a correspondence between the values of two or more dimensions. It can have dimensions, just like a variable, but the values of the relation must be values from the related dimension.

Syntax

DEFINE name RELATION related-dim [<dimensions...>] [TEMP] [AW workspace] [SESSION]

Arguments

name

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

RELATION

The object type when you are defining a relation.

related-dim

Specifies the dimension to which one or more dimensions are related. A relation is normally used to store information about the relationship between two dimensions; for example, the cities that belong in each region.

In the definition, the dimension having fewer values is normally specified as the related dimension (for example, regions). The dimension having more values is normally specified as a dimension of the relation (for example, cities).

<dimensions...>

The names of the dimensions of the relation. You must enclose the names of the dimensions in a single set of angle brackets (< >). You must define a dimension before including it in the definition of a relation. Do not include composites in the dimension list.

Restriction:

Oracle OLAP does not support the use of composites as dimensions for relations. Do not attempt to define them.

Tip:

When defining two relations between the same dimensions, use the RELATION command to identify which relation is the default relation.

TEMP

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

AW workspace

The name of an attached workspace in which you wish to define the relation. The relation must be defined in the same workspace as its dimensions. 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 the session ends, the object no longer exists. This differs from the TEMP keyword, which specifies that the values are temporary but the object definition remains in the workspace in which you create it.

Examples

DEFINE div.prod RELATION division <product>

LIMIT product TO 'Tents' 'Canoes'
div.prod = 'Camping'
LIMIT product TO 'Racquets'
div.prod = 'Sporting'
LIMIT product TO 'Sportswear' 'Footwear'
div.prod = 'Clothing'

report div.prod

PRODUCT        DIV.PROD
------------- ----------
Tents         Camping
Canoes        Camping
Racquets      Sporting
Sportswear    Clothing
Footwear      Clothing

REPORT TOTAL(units division) 

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.

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)

Arguments

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, you should 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 one of the datetime data types (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 wish 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.

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

DEFINE storepos SURROGATE store_id INTEGER

DEFINE prodnum SURROGATE product NUMBER(7, 3)

prodnum(product 1) = 1083.375

DEFINE VALUESET

The DEFINE command with the VALUESET keyword adds a new valueset object to an analytic workspace. A valueset is a list of dimension values for one or more dimensions. You use a valueset to save dimension status lists across sessions.

Syntax

DEFINE name VALUESET dimension [<dims...>] [TEMP] [AW workspace] [SESSION]

Arguments

name

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

VALUESET

The object type when you are defining a valueset.

dimension

The name of the previously-defined dimension whose values you want to store in the valueset.

dims

When defining a multi-dimensional valueset, the names of the previously-defined dimensions by which you want the valueset dimensioned.

TEMP

Indicates that the valueset's values are only temporary. The valueset 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 valueset is saved. When you end the session or switch to another workspace, the values are discarded. Each time you start the workspace, the value of a temporary valueset is null.

AW workspace

The name of an attached workspace in which you wish to define the valueset. The valueset must be defined in the same workspace as its dimensions. 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 the session ends, the object no longer exists. This differs from the TEMP keyword, which specifies that the values are temporary but the object definition remains in the workspace in which you create it.

Examples

LIMIT line TO FIRST 2
STATUS line

The current status of LINE is:
REVENUE, COGS

" Define the valueset and specify a long description for it

DEFINE lineset VALUESET line
LD Valueset for LINE dimension values
" Assign the values that are currently in status for line 
" as the values of valueset
LIMIT lineset TO line
UPDATE

SHOW lineset

Revenue
Cogs 

DEFINE geography DIMENSION TEXT
DEFINE product DIMENSION TEXT
DEFINE sales VARIABLE DECIMAL <geography product>
DEFINE salestax VARIABLE DECIMAL <geography>

DEFINE all_variables DIMENSION TEXT
MAINTAIN all_variables ADD 'sales' 'salestax'
DEFINE all_dims DIMENSION TEXTMAINTAIN all_dims ADD 'geography' 'product'

DEFINE variables_dims VALUESET all_dims <all_variables>
" Assign all values of all_dims and all_variables to the valueset 
LIMIT variables_dims TO ALL
REPORT variables_dims
 
ALL_VARIABLES        VARIABLES_DIMS
---------------- ------------------------------
sales            geography
                 product
salestax         geography
                 product
 

LIMIT variables_dims(all_variables  'salestax') TO 'geography'REPORT variables_dimsALL_VARIABLES        VARIABLES_DIMS---------------- ------------------------------sales            geography                 productsalestax         geography

DEFINE VARIABLE

The DEFINE command with the VARIABLE keyword adds a new variable object to an analytic workspace. Variables store one type of data, which can be numeric, text, Boolean, or dates. Beside the data type of a variable, the definition that you create for a variable also determines the following characteristics of the variable:

• The number of elements that are actually created in the array that is the variable.

• The logical order of the variable's elements.

• Whether the variable's data is stored permanently or is only available for the session.

• The number of LOBs that Oracle OLAP creates for the variable's data.

You can also define local program variables using a VARIABLE command. These variables exist only as long as the program is running.

Syntax

DEFINE name [VARIABLE] datatype [<dims...>] [WITH NULLTRACKING] [WITH AGGCOUNT] -

[PERMANENT | TEMP ] -

     [ RANSPACE64] [(partition-instance...)] [WIDTH n] [AW workspace] [SESSION]

where:

• dims are the dimensions of the variable separated by commas. For a dimension of a variable you can specify a dimension object, a partition template object, a named uncompressed composite, a compressed composite, or an unnamed uncompressed composite using one of the following:

  dimension_name

  partition_template_name<dims>

  uncompressed_composite_name <[basedims...]>

  compressed_composite_name <[basedims...]>

  SPARSE <basedims...>

  Note:

  The order in which you list the dims of a variable is the default order of the dimensions and behavior of a variety of statements (such as REPORT and UNRAVEL) and affects how the data for the variable is stored (as discussed in "Effect of Dimension Order on Variable Storage and Statement Looping". Also, when you define more than one object with the same dimensions, most operations will work much more efficiently when you list the dimensions in the same order in each definition.

• partition-instance are the partitions of the variable separated by commas. Use the following syntax to specify a partition.

       PARTITION partition-name INTERNAL [TEMP | PERMANENT]

Arguments

name

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

VARIABLE

The object type when you are defining a variable. You do not have to include the word VARIABLE, because it is the default.

datatype

The data type of the data to be stored in the variable. The data types, their abbreviations, and the range of acceptable values are shown in Table 2-1, "Summary of OLAP DML Data Types".

dimension_name

The name of a simple, concat, conjoint, or alias dimension that you have previously defined using a DEFINE DIMENSION statement. In this case, you specify the name of the dimension.

RANSPACE64

When defining a TEXT, NTEXT, or RAW variable, specify this keyword to increase the maximum number of characters for the values of the variable from nearly 2**32 to nearly 2**64.

 partition-template-name<dims>

The name of a partition template object that you have previously defined using a DEFINE PARTITION TEMPLATE statement. For dims, specify the names of the dimensions of the partition template object. These dimensions must be the same dimensions as those used to define the partition template object.

uncompressed_composite_name <[basedims...]>

The name of an uncompressed composite previously defined using a DEFINE COMPOSITE statement. For the optional basedims argument, specify the names, separated by commas, of the dimensions used to define the composite.

compressed_composite_name <basedims...>>

The name of a compressed composite previously defined using a DEFINE COMPOSITE statement. For the optional basedims argument, specify the names, separated by commas, of the dimensions used to define the composite.

When defining a variable that is dimensioned by a compressed composite, keep the following points in mind:

• A compressed composite can dimension only one variable or one partition of a variable. A compressed composite cannot be a shared composite.

• The compressed composite must be the last dimension in the variable's dimension list of the DEFINE VARIABLE statement that defines the variable.

SPARSE <basedims...>

Indicates that you want Oracle OLAP to create an unnamed composite and use it when dimensioning the variable. For the basedims argument, specify the names of the dimensions, separated by commas, for which the unnamed composite is created.

WITH NULLTRACKING

When the variable is dimensioned by a composite, specifies that Oracle OLAP create NA2 bits for the cells of the variable.

See also:

For more information on:

• NA2 bits and null tracking, see "NA2 Bits and Null Tracking"

• Creating materialized views using Oracle OLAP, see Oracle OLAP User's Guide

WITH AGGCOUNT

Specifies that Oracle OLAP automatically creates an INTEGER variable in which it stores the non-NA counts of the number of leaf nodes that contributed to aggregate values calculated for RELATION statements that have an AVERAGE, HWAVERAGE, or WAVERAGE operator. You must include this phrase to calculate average aggregations for a variable dimensioned by a compressed composite. For more information on Aggcount variables, see "Aggcount Variables".

PERMANENT

TEMP

Specifies that a variable or a partition of a variable is either permanent or temporary. After you update and commit, the definition of both permanent and temporary variables and partitions is always saved between sessions. Specifying permanent or temporary determines whether or not the values of a variable or partition of a variable are saved or discarded, after you update and commit, when you leave end your session or switch to another workspace:

• Permanent variables and partitions­—Oracle OLAP saves the data values or a permanent variable or permanent partitions. When you start the workspace, the data values or a permanent variable or permanent partitions are the same as they were at the last commit.

• Temporary variables and partitions­—Oracle OLAP discards the data values of a temporary variable or temporary partition. Each time you start the workspace, the values of a temporary variable or temporary partition are NA.

Keep the following points in mind when specifying the PERMANENT and TEMP keywords:

• By default, a variable is permanent.

• Temporary variables can be dimensioned by partition template objects or by temporary dimensions.

• By default, a top-level partition of a variable has the same permanence as the variable that contains it. Specifically, a partition of a temporary variable is a temporary partition unless you use the PEMANENT keyword to make it a permanent partition, and a partition of a permanent variable is a permanent partition unless you use the TEMPORARY keyword to make it a temporary partition. To indicate different behavior, use either the PERMANENT or TEMP keyword.

• By default, a subpartition has the same permanence as its parent partition. To indicate different behavior, use either the PERMANENT or TEMP keyword.

WIDTH n

(You can abbreviate WIDTH as W.) The width, in bytes, of the storage area for each value of a variable. When you are using a multibyte character set, be sure to specify the number of bytes, not characters.

You specify fixed widths to create faster and more compact data storage formats. You can specify fixed widths for dimensioned TEXT, NTEXT, and INTEGER variables only, as described in the following list:

• For dimensioned TEXT and NTEXT variables, you can specify a width from 1 byte through 4000 bytes. Specify a fixed width for such variables only when you are certain that the values of a particular variable are of similar size. You cannot assign a width to a scalar variable.

• For dimensioned INTEGER variables, you can specify a width of 1 byte only. Define a fixed width INTEGER variable only when you are certain that all the values for that variable are between -128 and 127.

The default widths for variables are: 2 bytes for SHORTINTEGER, 4 bytes for DATE, INTEGER, and SHORTDECIMAL, and 8 bytes for DECIMAL and ID. TEXT and NTEXT variables that do not have fixed widths are stored on two sets of pages. The first set contains 4-byte cells, each of which points to the actual text value that is stored in the other set of pages. The default width of 4 bytes for TEXT and NTEXT variables is for these 4-byte cells.

PARTITION partition-name INTERNAL

Specifies a partition of the variable where partition-name is the name of the partition.

When defining the partitions of a variable dimensioned by a compressed composite, keep the following points in mind:

• A compressed composite can dimension only one partition.

• The partitions of a variable dimensioned by a compressed composite must respect the parent-child relationships of the hierarchical dimensions. When an AGGREGATE command executes, data cannot be aggregated across partitions. To check to see if a variable is partitioned correctly, use the PARTITIONCHECK function.

AW workspace

The name of an attached workspace in which you wish to define the variable. When the variable is dimensioned, it must be defined in the same workspace as its dimensions. 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 the session ends, the object no longer exists. This differs from the TEMP keyword, which specifies that the values are temporary but the object definition remains in the workspace in which you create it.

Notes

Aggcount Variables

When you include the WITH AGGCOUNT phrase in a DEFINE VARIABLE statement, Oracle OLAP automatically creates the variable specified in the DEFINE statement and a secondary variable (often called the Aggcount variable). The Aggcount variable is an INTEGER variable that Oracle OLAP uses when performing average aggregations for the defined variable. When resolving RELATION statements that have an AVERAGE, HAVERAGE, WAVERAGE, or HWAVERAGE operator and that do not have a COUNT NO phrase, Oracle OLAP stores the non-NA counts of the number of leaf nodes that contribute to the average aggregate values in the Aggcount variable.

Most statements that maintain a variable also automatically maintain an associated Aggcount variable. For example, an EXPORT statement exports both a variable and its associated Aggcount variable, and a CLEAR statement clears both the variable and the related portions of the associated Aggcount variable. Additionally, some OLAP DML statements are specific to the use of Aggcount objects. Table 9-6, "OLAP DML Statements for Aggcount Variables" lists these statements.

NA2 Bits and Null Tracking

Relational fact tables sometimes have null facts (that is, facts that have a null value). Typically, when OLAP DML creates a variable dimensioned by a composite, it does not create a composite tuple for an NA (or null) value. Given this typical behavior, OLAP DML variables would not correspond to their base relational fact table because the variables would eliminate the null facts.

To support OLAP DML composite-dimensioned variables that correspond to relational fact tables with null facts, OLAP has a special NA bit called an NA2 bit. These NA2 bits tracks whether or not each cell of the variable has null value because the underlying relational table has a null fact. When the corresponding fact table has a null fact, you want Oracle OLAP to intentionally include an NA value in the composite tuples for the variable and NA2 bits are used by Oracle OLAP to do just that. NA2 bits are used by Oracle OLAP when it populates variables using the SQL IMPORT command and the AGGREGATE command, as well as variables that were created as materialized views. It is also used by Oracle OLAP when it populates a relational table using the OLAP_TABLE SQL function. Additionally, Oracle OLAP recognizes NA2 values when evaulating expressions using arithmetic and Boolean operators.

The OLAP DML provides the following statements for working with variables that have NA2 bits:

• To create a variable with NA2 bits, use the DEFINE VARIABLE statement with the NULLTRACKING phrase.

• To add NA2 bits to a variable that does not have NA2 bits, use the CHGDFN statement with the NULLTRACKING phrase.

• To remove NA2 bits from a variable that has NA2 bits, use the CHGDFN statement with the DROP NULLTRACKING phrase.

• For testing and debugging purposes, use the NA2 function to set one or more of the NA2 bit of a variable to TRUE. Use the NAFLAG function to identify if one or more values of a variable are NA values and, if so, if the NA value is just the typical NA values that OLAP should ignore or both the typical NA value and also an NA2 value.

Defining Very Large Variables

Theoretically, a variable can contain up to 2**63 cells and a TEXT or NTEXT variable can contain up to 2 billion bytes. However, the page size determines if a variable can be stored entirely on a page or how many variables can be stored on a page. To calculate the maximum number of values for a variable of a given width that will fit on one page, use the VALSPERPAGE program.

Effect of Dimension Order on Variable Storage and Statement Looping

The order in which you list the dimensions of a variable definition determines the order in which the elements of the variable are stored and, consequently, how the data is accessed. The first dimension in the variable definition is the fastest-varying dimension, and the last dimension is the slowest-varying dimension.

For example, assume your analytic workspace has an opcosts variable that contains the operating costs, by month, of each city in which you have offices. In the following definition for the opcosts variable, month is the fastest-varying dimension and city is the slowest-varying dimension.

DEFINE opcosts VARIABLE DECIMAL <month city>

The data for a multidimensional variable is stored as a linear stream of values, in which the values of the fastest-varying dimension are clustered together. For example, for the opcosts variable, the values for Boston for all the months are stored in a sequence, and then it stores the values for Chicago for all the months in a sequence, and so on. Thus the month values vary fastest in the opcosts variable, as shown in the following table.

When you define variables and other dimensioned objects, and when you write programs that loop over multidimensional expressions in nested loops, you should always try to maximize performance by matching the fastest-varying dimension with the inner loop.

Unnamed Composites

Oracle OLAP automatically defines an unnamed composite when a DEFINE VARIABLE statement with a SPARSE <dimlist> phrase executes. An unnamed composite can have either a b-tree or hash index. The type of index is determined by the value of the SPARSEINDEX option at the time that Oracle OLAP defines an unnamed composite.

Once Oracle OLAP has created a definition for an unnamed composite for a certain dimension list, it uses that composite any time you define a variable with the same SPARSE <dimlist> phrase. Thus all variables that are defined with the same SPARSE <dimlist> phrase share the same unnamed composite. For more information on sharing composites, see "Shared Composites".

Variable Segments

Within a partition, variable data is stored in analytic workspace segments. An analytic workspace segment is a group of logically contiguous analytic workspace pages. By default, the segment sizes of a variable are automatically determined by Oracle OLAP. Each segment is the exactly the number of analytic workspace pages needed to store the values assigned by the one OLAP DML statement. You can explicitly specify a segment size for a variable using the SEGWIDTH keyword of the CHGDFN command. In this case, when you assign values to a variable, Oracle OLAP stores the data assigned by multiple OLAP DML statements into a segment until the segment is full.

Examples

DEFINE population INTEGER <city>
LD Population in each city
DESCRIBE population

DEFINE POPULATION VARIABLE INTEGER <CITY>
LD Population in each city

DEFINE newdata BOOLEAN
newdata = YES

DEFINE sales VARIABLE NUMBER (16,4) <product, geography>

DEFINE numvar VARIABLE NUMBER (5, 2)
numvar = 1234567
SHOW numvar
NA

DEFINE numnegvar VARIABLE NUMBER (5, -2)
numnegvar = 1234567
SHOW numnegvar
1,234,600.00

DEFINE GEOG_CITY DIMENSION TEXT
DEFINE GEOG_STATE DIMENSION TEXT
DEFINE GEOG_AREA DIMENSION TEXT
DEFINE GEOG_CONT DIMENSION TEXT
DEFINE GEOG DIMENSION CONCAT (GEOG_CITY GEOG_STATE GEOG_AREA GEOG_CONT)
DEFINE PROD_UPC DIMENSION TEXT
DEFINE PROD_FAMILY DIMENSION TEXT
DEFINE PROD_DIV DIMENSION TEXT
DEFINE PROD_TOP DIMENSION TEXT
DEFINE PROD DIMENSION CONCAT (PROD_UPC PROD_FAMILY PROD_DIV PROD_TOP)
DEFINE GEOGLEVEL DIMENSION TEXT
DEFINE PRODLEVEL DIMENSION TEXT
DEFINE GEOG.PARENT RELATION GEOG <GEOG>
DEFINE PROD.PARENT RELATION PROD <PROD>
DEFINE GEOG.LEVELREL RELATION GEOGLEVEL <GEOG>
DEFINE PROD.LEVELREL RELATION PRODLEVEL <PROD>
DEFINE GEOG.FAMILYREL RELATION GEOG <GEOG GEOGLEVEL>
DEFINE PROD.FAMILYREL RELATION PROD <PROD PRODLEVEL>
DEFINE SALES_DIMS_REG VARIABLE NUMBER (12,0) <PROD GEOG>
DEFINE SALES_AGGMAP AGGMAP
AGGMAP
  RELATION geog.parent
  RELATION prod.parent
END

PROD                             PROD.PARENT
------------------------- -------------------------
<PROD_UPC: ColorTV>       <PROD_FAMILY: TV>
<PROD_UPC: BWTV>          <PROD_FAMILY: TV>
<PROD_UPC: StndVCR>       <PROD_FAMILY: VCR>
<PROD_UPC: StrVCR>        <PROD_FAMILY: VCR>
<PROD_FAMILY: VCR>        <PROD_DIV: VideoDiv>
<PROD_FAMILY: TV>         <PROD_DIV: VideoDiv>
<PROD_DIV: VideoDiv>      <PROD_TOP: Total Prod>
<PROD_TOP: Total Prod>    NA
GEOG                             GEOG.PARENT
------------------------- -------------------------
<GEOG_CITY: Canberra>     <GEOG_STATE: ACT>
<GEOG_CITY: Sydney>       <GEOG_STATE: NSW>
<GEOG_CITY: Darwin>       <GEOG_STATE: NT>
<GEOG_CITY: Brisbane>     <GEOG_STATE: QLD>
<GEOG_CITY: Adelaide>     <GEOG_STATE: SA>
<GEOG_CITY: Hobart>       <GEOG_STATE: TAS>
<GEOG_CITY: Melbourne>    <GEOG_STATE: VIC>
<GEOG_CITY: Perth>        <GEOG_STATE: WA>
<GEOG_STATE: ACT>         <GEOG_AREA: Aust Terr>
<GEOG_STATE: NSW>         <GEOG_AREA: Aust State>
<GEOG_STATE: NT>          <GEOG_AREA: Aust Terr>
<GEOG_STATE: QLD>         <GEOG_AREA: Aust State>
<GEOG_STATE: SA>          <GEOG_AREA: Aust State>
<GEOG_STATE: TAS>         <GEOG_AREA: Aust State>
<GEOG_STATE: VIC>         <GEOG_AREA: Aust State>
<GEOG_STATE: WA>          <GEOG_AREA: Aust State>
<GEOG_AREA: Aust State>   <GEOG_CONT: Australia>
<GEOG_AREA: Aust Terr>    <GEOG_CONT: Australia>
<GEOG_CONT: Australia>    NA

REPORT sales_dims_reg->REPORT sales_dims_reg
 

----------------------------SALES_DIMS_REG-----------------------------
                          ---------------------------------PROD----------------------------------
                                                                                <PROD_DI
                          <PROD_UP          <PROD_UP <PROD_UP <PROD_FA <PROD_FA    V:    <PROD_TO
                             C:    <PROD_UP    C:       C:     MILY:    MILY:   VideoDiv P: Total
GEOG                      ColorTV> C: BWTV> StndVCR> StrVCR>    VCR>     TV>       >      Prod>
------------------------- -------- -------- -------- -------- -------- -------- -------- --------
<GEOG_CITY: Canberra>     11,592.0       NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_CITY: Sydney>             NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_CITY: Darwin>       24,868.0       NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_CITY: Brisbane>     49,556.0       NA 48,239.0 24,285.0 72,524.0 49,556.0  122,080  122,080
<GEOG_CITY: Adelaide>           NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_CITY: Hobart>       17,223.0       NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_CITY: Melbourne>          NA 22,000.0       NA       NA       NA 22,000.0 22,000.0 22,000.0
<GEOG_CITY: Perth>              NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_STATE: ACT>         11,592.0       NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_STATE: NSW>               NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_STATE: NT>          24,868.0       NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_STATE: QLD>         49,556.0       NA 48,239.0 24,285.0 72,524.0 49,556.0  122,080  122,080
<GEOG_STATE: SA>                NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_STATE: TAS>         17,223.0       NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_STATE: VIC>               NA 22,000.0       NA       NA       NA 22,000.0 22,000.0 22,000.0
<GEOG_STATE: WA>                NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_AREA: Aust State>   66,779.0 22,000.0 67,111.0 73,065.0  140,176 88,779.0  228,955  228,955
<GEOG_AREA: Aust Terr>    36,460.0       NA 60,460.0 36,111.0 96,571.0 36,460.0  133,031  133,031
<GEOG_CONT: Australia>     103,239 22,000.0  127,571  109,176  236,747  125,239  361,986  361,986

SHOW OBJ(NUMVALS 'sales_dims_reg')
152.00

DEFINE COMP_PROD_GEOG COMPOSITE <PROD GEOG>
DEFINE SALES_DIMS_COMPOSITE VARIABLE NUMBER (12,2) <COMP_PROD_GEOG <PROD GEOG>>

REPORT sales_dims_composite
 

-------------------------SALES_DIMS_COMPOSITE--------------------------
                          ---------------------------------PROD----------------------------------
                                                                                <PROD_DI
                          <PROD_UP          <PROD_UP <PROD_UP <PROD_FA <PROD_FA    V:    <PROD_TO
                             C:    <PROD_UP    C:       C:     MILY:    MILY:   VideoDiv P: Total
GEOG                      ColorTV> C: BWTV> StndVCR> StrVCR>    VCR>     TV>       >      Prod>
------------------------- -------- -------- -------- -------- -------- -------- -------- --------
<GEOG_CITY: Canberra>     11,592.0       NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_CITY: Sydney>             NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_CITY: Darwin>       24,868.0       NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_CITY: Brisbane>     49,556.0       NA 48,239.0 24,285.0 72,524.0 49,556.0  122,080  122,080
<GEOG_CITY: Adelaide>           NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_CITY: Hobart>       17,223.0       NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_CITY: Melbourne>          NA 22,000.0       NA       NA       NA 22,000.0 22,000.0 22,000.0
<GEOG_CITY: Perth>              NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_STATE: ACT>         11,592.0       NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_STATE: NSW>               NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_STATE: NT>          24,868.0       NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_STATE: QLD>         49,556.0       NA 48,239.0 24,285.0 72,524.0 49,556.0  122,080  122,080
<GEOG_STATE: SA>                NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_STATE: TAS>         17,223.0       NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_STATE: VIC>               NA 22,000.0       NA       NA       NA 22,000.0 22,000.0 22,000.0
<GEOG_STATE: WA>                NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_AREA: Aust State>   66,779.0 22,000.0 67,111.0 73,065.0  140,176 88,779.0  228,955  228,955
<GEOG_AREA: Aust Terr>    36,460.0       NA 60,460.0 36,111.0 96,571.0 36,460.0  133,031  133,031
<GEOG_CONT: Australia>     103,239 22,000.0  127,571  109,176  236,747  125,239  361,986  361,986

SHOW OBJ(NUMVALS 'sales_dims_composite')
87.00

DEFINE CC_COMP_PROD_GEOG COMPOSITE <PROD GEOG> COMPRESSED
DEFINE SALES_DIMS_COMP_COMPOSITE VARIABLE NUMBER (12,0) <CC_COMP_PROD_GEOG <PROD GEOG>>

REPORT sales_dims_comp_composite
 

-----------------------SALES_DIMS_COMP_COMPOSITE-----------------------
                          ---------------------------------PROD----------------------------------
                                                                                <PROD_DI
                          <PROD_UP          <PROD_UP <PROD_UP <PROD_FA <PROD_FA    V:    <PROD_TO
                             C:    <PROD_UP    C:       C:     MILY:    MILY:   VideoDiv P: Total
GEOG                      ColorTV> C: BWTV> StndVCR> StrVCR>    VCR>     TV>       >      Prod>
------------------------- -------- -------- -------- -------- -------- -------- -------- --------
<GEOG_CITY: Canberra>     11,592.0       NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_CITY: Sydney>             NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_CITY: Darwin>       24,868.0       NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_CITY: Brisbane>     49,556.0       NA 48,239.0 24,285.0 72,524.0 49,556.0  122,080  122,080
<GEOG_CITY: Adelaide>           NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_CITY: Hobart>       17,223.0       NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_CITY: Melbourne>          NA 22,000.0       NA       NA       NA 22,000.0 22,000.0 22,000.0
<GEOG_CITY: Perth>              NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_STATE: ACT>         11,592.0       NA 38,356.0 3,444.00 41,800.0 11,592.0 53,392.0 53,392.0
<GEOG_STATE: NSW>               NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_STATE: NT>          24,868.0       NA 22,104.0 32,667.0 54,771.0 24,868.0 79,639.0 79,639.0
<GEOG_STATE: QLD>         49,556.0       NA 48,239.0 24,285.0 72,524.0 49,556.0  122,080  122,080
<GEOG_STATE: SA>                NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_STATE: TAS>         17,223.0       NA 18,872.0 48,780.0 67,652.0 17,223.0 84,875.0 84,875.0
<GEOG_STATE: VIC>               NA 22,000.0       NA       NA       NA 22,000.0 22,000.0 22,000.0
<GEOG_STATE: WA>                NA       NA       NA       NA       NA       NA       NA       NA
<GEOG_AREA: Aust State>   66,779.0 22,000.0 67,111.0 73,065.0  140,176 88,779.0  228,955  228,955
<GEOG_AREA: Aust Terr>    36,460.0       NA 60,460.0 36,111.0 96,571.0 36,460.0  133,031  133,031
<GEOG_CONT: Australia>     103,239 22,000.0  127,571  109,176  236,747  125,239  361,986  361,986

SHOW OBJ(NUMVALS 'sales_dims_comp_composite')
38.00

DEFINE TIME DIMENSION TEXT
DEFINE PRODUCT DIMENSION TEXT
DEFINE TIME_PARENTREL RELATION TIME <TIME>

TIME
--------------
2003
2002
Dec2003
Jan2003
Dec2002
Jan2002
31Dec2003
01Dec2003
31Jan2003
01Jan2003
31Dec2002
01Dec2002
31Jan2002
01Jan2002

PRODUCT
-------
00001
00002

DEFINE lastname TEXT <employee> WIDTH 20 

DEFINE month DIMENSION TEXT
DEFINE product DIMENSION TEXT
DEFINE region DIMENSION TEXT

DEFINE proddist COMPOSITE <product region>
DEFINE promo VARIABLE INTEGER <month proddist <product district>>

PRODUCT
--------------
Tents
Skis
 
REGION
--------------
Northeast
Southwest
 
MONTH
--------------
Jan2003
Feb2003
Mar2003
Apr2003
May2003
Jun2003
Jul2003
Aug2003
Sep2003
Oct2003
Nov2003
Dec2003

DEFINE GEOG DIMENSION TEXT
LD A dimension with a simple hierarchy for geography

DEFINE geog_levellist DIMENSION TEXT
LD List of Levels in in the hierarchy of the geog dimension

DEFINE GEOG_PARENTREL RELATION GEOG <GEOG>
LD Self-relation for geog showing parents of each value in the hierarchy

DEFINE GEOG_LEVELREL RELATION GEOG_LEVELLIST <GEOG>
LD Level of each dimension member for geog

DEFINE product DIMENSION TEXT
LD A nonhierarchical dimension

DEFINE time DIMENSION TEXT
LD A hierarchical text dimension for time

DEFINE time_levellist DIMENSION TEXT
LD List of Levels in hierarchy of the time dimension

DEFINE time_parentrel RELATION time <time>
LD A self-relation for time show parents of each value in the hierarchy

DEFINE TIME_LEVELREL RELATION TIME_LEVELLIST <TIME>
LD Level of each dimension member for time

DEFINE prod_geog COMPOSITE <product geog> COMPRESSED

DEFINE sales VARIABLE DECIMAL <time prod_geog<product geog>> WITH NULLTRACKING

REPORT DOWN time ACROSS geog: sales
 
PRODUCT: TVs
               -----------------------SALES-----------------------
               -----------------------GEOG------------------------
TIME              Boston    Springfield    Hartford    All Places
-------------- ------------ ------------ ------------ ------------
2007                     NA           NA           NA           NA
2008                     NA           NA           NA           NA
All years                NA           NA           NA           NA
Jan07                    NA           NA           NA           NA
Feb07                    NA           NA           NA           NA
Mar07                    NA           NA           NA           NA
Apr07                    NA           NA           NA           NA
May07                    NA           NA           NA           NA
Jun07                    NA           NA           NA           NA
Jul07                    NA           NA           NA           NA
Aug07                    NA           NA           NA           NA
Sep07                    NA           NA           NA           NA
Oct07                    NA           NA           NA           NA
Nov07                    NA           NA           NA           NA
Dec07                    NA           NA           NA           NA
Jan08                    NA           NA           NA           NA
Feb08                    NA           NA           NA           NA
Mar08                    NA           NA           NA           NA
Apr08                    NA           NA           NA           NA
May08                    NA           NA           NA           NA
Jun08                    NA           NA           NA           NA
Jul08                    NA           NA           NA           NA
Aug08                    NA           NA           NA           NA
Sep08                    NA           NA           NA           NA
Oct08                    NA           NA           NA           NA
Nov08                    NA           NA           NA           NA
Dec08                    NA           NA           NA           NA
 
PRODUCT: Radios
               -----------------------SALES-----------------------
               -----------------------GEOG------------------------
TIME              Boston    Springfield    Hartford    All Places
-------------- ------------ ------------ ------------ ------------
2007                     NA           NA           NA           NA
2008                     NA           NA           NA           NA
All years                NA           NA           NA           NA
Jan07                 24.59        23.70        33.12        28.65
Feb07                 22.78        21.42        26.28        37.06
Mar07                 25.74        32.08        22.75        24.62
Apr07                 22.23        23.21        20.79        28.68
May07                 20.51        29.71        30.35        33.05
Jun07                 34.43        35.96        33.85        39.34
Jul07                 24.86        38.02        36.78        31.22
Aug07                 39.05        21.08        35.80        33.81
Sep07                 34.38        21.69        25.04        33.40
Oct07                 33.82        39.27        20.28        24.39
Nov07                 25.48        23.03        32.45        39.94
Dec07                 25.14        30.66        33.75        23.37
Jan08                    NA           NA           NA           NA
Feb08                    NA           NA           NA           NA
Mar08                    NA           NA           NA           NA
Apr08                    NA           NA           NA           NA
May08                    NA           NA           NA           NA
Jun08                    NA           NA           NA           NA
Jul08                    NA           NA           NA           NA
Aug08                    NA           NA           NA           NA
Sep08                    NA           NA           NA           NA
Oct08                    NA           NA           NA           NA
Nov08                    NA           NA           NA           NA
Dec08                    NA           NA           NA           NA

REPORT DOWN time ACROSS geog: NAFLAG(sales)
 
PRODUCT: TVs
               -------------------NAFLAG(SALES)-------------------
               -----------------------GEOG------------------------
TIME              Boston    Springfield    Hartford    All Places
-------------- ------------ ------------ ------------ ------------
2007                      1            1            1            1
2008                      1            1            1            1
All years                 1            1            1            1
Jan07                     1            1            1            1
Feb07                     1            1            1            1
Mar07                     1            1            1            1
Apr07                     1            1            1            1
May07                     1            1            1            1
Jun07                     1            1            1            1
Jul07                     1            1            1            1
Aug07                     1            1            1            1
Sep07                     1            1            1            1
Oct07                     1            1            1            1
Nov07                     1            1            1            1
Dec07                     1            1            1            1
Jan08                     1            1            1            1
Feb08                     1            1            1            1
Mar08                     1            1            1            1
Apr08                     1            1            1            1
May08                     1            1            1            1
Jun08                     1            1            1            1
Jul08                     1            1            1            1
Aug08                     1            1            1            1
Sep08                     1            1            1            1
Oct08                     1            1            1            1
Nov08                     1            1            1            1
Dec08                     1            1            1            1
 
PRODUCT: Radios
               -------------------NAFLAG(SALES)-------------------
               -----------------------GEOG------------------------
TIME              Boston    Springfield    Hartford    All Places
-------------- ------------ ------------ ------------ ------------
2007                      1            1            1            1
2008                      1            1            1            1
All years                 1            1            1            1
Jan07                     0            0            0            0
Feb07                     0            0            0            0
Mar07                     0            0            0            0
Apr07                     0            0            0            0
May07                     0            0            0            0
Jun07                     0            0            0            0
Jul07                     0            0            0            0
Aug07                     0            0            0            0
Sep07                     0            0            0            0
Oct07                     0            0            0            0
Nov07                     0            0            0            0
Dec07                     0            0            0            0
Jan08                     1            1            1            1
Feb08                     1            1            1            1
Mar08                     1            1            1            1
Apr08                     1            1            1            1
May08                     1            1            1            1
Jun08                     1            1            1            1
Jul08                     1            1            1            1
Aug08                     1            1            1            1
Sep08                     1            1            1            1
Oct08                     1            1            1            1
Nov08                     1            1            1            1
Dec08                     1            1            1            1

SHOW NA2
NA

LIMIT time TO 'Jan08' 'Feb08' 'Mar08' 'Apr08' 'May08' 'Jun08' 'Jul08' 'Aug08' 'Sep08' 'Oct08' 'Nov08' 'Dec08'
saleswithnull= NA2
 

LIMIT product to 'Radios'
LIMIT time TO 'Jan08' '2008'
LIMIT geog TO 'Boston' 'All Places'

REPORT DOWN time ACROSS geog: sales
 
PRODUCT: Radios
               ----------SALES----------
               ----------GEOG-----------
TIME              Boston     All Places
-------------- ------------ ------------
Jan08                    NA           NA
2008                     NA           NA

REPORT DOWN time ACROSS geog: NAFLAG(sales)
 

PRODUCT: Radios
               ------NAFLAG(SALES)------
               ----------GEOG-----------
TIME              Boston     All Places
-------------- ------------ ------------
Jan08                     2            2
2008                      1            1

CHGDFN sales DROP NULLTRACKING

DESCRIBE sales
 
DEFINE SALES VARIABLE DECIMAL <TIME PROD_GEOG <PRODUCT GEOG>>

REPORT DOWN time ACROSS geog: sales
 
PRODUCT: Radios
               ----------SALES----------
               ----------GEOG-----------
TIME              Boston     All Places
-------------- ------------ ------------
Jan08                    NA           NA
2008                     NA           NA

"Report on the type of NA values in the sales variable
REPORT DOWN time ACROSS geog: NAFLAG(sales)
 
PRODUCT: Radios
               ------NAFLAG(SALES)------
               ----------GEOG-----------
TIME              Boston     All Places
-------------- ------------ ------------
Jan08                     1            1
2008                      1            1

DEFINE WORKSHEET

The DEFINE command with the WORKSHEET keyword adds a new worksheet object to an analytic workspace. A worksheet, like a spreadsheet, is a two-dimensional object that is dimensioned by a worksheet row and a worksheet column. It can temporarily store data that you want to transfer between spreadsheet packages and workspace dimensions and variables.

When you first define a worksheet, it does not contain any values. You can populate a worksheet with values from an existing spreadsheet by using an IMPORT (spreadsheet) statement or add or delete values from worksheet row and a worksheet column dimensions with a MAINTAIN statement.

Syntax

DEFINE name WORKSHEET [<column-dim row-dim>] [TEMP] [AW workspace] [SESSION]

Arguments

name

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

WORKSHEET

The object type when you are defining a worksheet.

<column-dim row-dim>

The names of the dimensions of the worksheet. When you supply this argument, you must give the names of two INTEGER dimensions for column-dim and row-dim. When you omit this argument, the worksheet will be dimensioned automatically by WKSCOL and WKSROW. See "Worksheet Dimensions" for more information

TEMP

Indicates that the worksheet is only temporary. The worksheet is defined in the specified workspace and can contain values during the current session. However, when you update and commit, only the definition of the worksheet is saved. When you end your session or switch to another workspace, the data values are discarded.

AW workspace

The name of an attached workspace in which you wish to define the worksheet. The worksheet must be defined in the same workspace as its dimensions. 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 the session ends, the object no longer exists. This differs from the TEMP keyword, which specifies that the values are temporary but the object definition remains in the workspace in which you create it.

Notes

Worksheet Dimensions

A worksheet must always dimensioned by two dimensions that represent a worksheet row and a worksheet column. The worksheet row and a worksheet column dimensions can either be automatically created by Oracle OLAP or explicitly created by you:

• When you have not created worksheet row and a worksheet column dimensions and specified their names in the column-dim and row-dimt arguments of DEFINE WORKSHEET, Oracle OLAP automatically creates the following dimensions:

  • For the worksheet row, an INTEGER dimension named WKSROW with values from 1 to 63.

  • For the worksheet column, an INTEGER dimension named WKSROW with values from 1 to 63.

  Note:

  When WKSCOL and WKSROW already exist in any attached workspace, Oracle OLAP cannot create them in the current worksheet. In this case, the DEFINE WORKSHEET command will fail to create a worksheet with these default dimensions. (Note, also, that WKSCOL and WKSROW do not appear in a worksheet description generated using DESCRIBE.)

• You create worksheet row and a worksheet column dimensions the same way you create any other simple dimension by issuing the following statements:

  1. Create two simple INTEGER dimensions using a DEFINE DIMENSION (simple) statement. One dimension is for row numbers and the other for column numbers.

  2. Using MAINTAIN statements, populate one dimension with integers that represent row numbers and populate with integers that represent column numbers.

Examples

DEFINE itemsheet WORKSHEET
DEFINE columns INT DIMENSION
MAINTAIN columns ADD 5
DEFINE rows INT DIMENSION
MAINTAIN rows ADD 10
DEFINE travelexp WORKSHEET <columns rows> TEMPORARY

DEFINE itemsheet WORKSHEET
IMPORT itemsheet FROM dif FILE 'file name'
LIMIT WKSCOL TO FIRST 3
LIMIT WKSROW TO FIRST 10
items = UNRAVEL(itemsheet)