Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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:

Syntax

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

Parameters

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.

Caution:

Oracle OLAP does not warn you when you create an object that has the same name as an existing object in another attached workspace.
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 want 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.

Usage Notes

Triggering Program Execution When DEFINE Executes

Using a TRIGGER_DEFINE program, you can make the DEFINE command an event that automatically executes 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:


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:

Syntax

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

Parameters

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

Example 9-78 Creating an Aggmap for Aggregation

Suppose you define a sales variable with the following statement.

DEFINE sales VARIABLE <time, product, geography>

Assume also that you have defined an aggmap named sales.agg with the following definition and specification.

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

The sales.agg aggregation specification contains the preceding three RELATION statements and a CACHE statements. In this example, you are specifying that all of the data for the time.r hierarchy of the time dimension should be aggregated, except for any data that has a time dimension value of Year99. All of the data for the product.r hierarchy of the product dimension should be aggregated, except for any data that has the product dimension value of ALL. (In this example, the product dimension has a dimension value named ALL that represents all products in the hierarchy.) All geography dimension values are aggregated. The CACHE STORE statement specifies that any data that is rolled up on the fly should be calculated just once and stored in the cache for other access requests during t he same session.

Note that users should not have write access to the analytic workspace when CACHE STORE is set, because the data calculated during the session may be saved inadvertently.

In this example, any data value that dimensioned by a Year99 time value or an ALL product dimension value is calculated on the fly.

You can now use the sales.agg aggmap with an AGGREGATE command, such as the following.

AGGREGATE sales USING sales.agg

Example 9-79 Creating an Aggmap for Allocation

Suppose you have a sales variable that you defined with the following statement.

DEFINE sales VARIABLE <time, product, geography>

To allocate data from a source to cells in the sales variable that are specified by the time and product dimension hierarchies, you have created an ASCII disk file called salesalloc.txt, which contains the following aggmap definition and specification.

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

To include the sales.alloc aggmap in your workspace, execute the following statement.

INFILE 'salesalloc.txt'

The sales.alloc aggmap is now defined, and it contains the preceding two RELATION statements, the SOURCEVAL statement and the CHILDLOCK statement. You end the entry of statements into the aggmap with the END statement. In this example, you are specifying that the first allocation of source values occurs down the time dimension hierarchy and that the source value is divided evenly between the target cells at each level of the allocation. The second allocation occurs down the product dimension hierarchy, with the source value again divided evenly between the target cells at each level of the allocation, and when the allocation encounters a deadlock, the source values is divided evenly between the target cells of the hierarchy including cells that have a basis value of NA. With the SOURCEVAL statement you specify that after the allocation, ALLOCATE sets the value of each source cell to zero. With the CHILDLOCK statement you specify that ALLOCATE detects the existence of locks on both a parent and a child element of a dimension hierarchy.

You can now use the sales.alloc aggmap with an ALLOCATE command, such as the following.

ALLOCATE sales USING sales.alloc

The preceding statement does not specify a basis or a target object so ALLOCATE uses the sales variable as the source, the basis, and the target of the allocation.


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:

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. Consequently, 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.

Note:

Oracle OLAP also supports the use of unnamed composites as described in "Unnamed Composites".

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

Parameters

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 dimension 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 automatically creates any unnamed composites in the list for you.

AW workspace

The name of an attached workspace in which you want 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 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 is 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 multiple descendants. 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 multiple descendants, 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.

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

When the size of variables is important, or when you have variables that are sparse along the same dimensions but with 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-99, "Defining a Variable Dimensioned by an Uncompressed Composite" and Example 9-100, "Defining a Variable Dimensioned by a Compressed Composite".

Example 9-80 Creating a Named b-Tree Composite

Assume that the value of SPARSEINDEX is BTREE. The following statements define two objects: a named composite that has a b-tree index and base dimensions of market and a variable called expenses that is dimensioned by the month dimension and the market.product 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:

Note:

Defining a dimension in the analytic workspace merely adds the definition of the dimension to the analytic workspace; it does not populate the dimension. To populate dimensions using the OLAP DML, you can issue OLAP DML SQL, FILEREAD, or MAINTAIN statements.

DEFINE DIMENSION (simple)

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

Tip:

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

Syntax

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

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

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

Parameters

name

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

DIMENSION

The object type when you are defining a dimension.

TEXT

Specifies that the values of the dimension have the TEXT data type which is equivalent to the CHAR and VARCHAR2 data types in Oracle Database. This data type stores up to 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 is truncated.

INTEGER

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

NUMBER

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

precision

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

scale

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

DATETIME

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

TIMESTAMP

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

TIMESTAMP_TZ

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

TIMESTAMP_LTZ

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

truncation_code

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

TEMP

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

AW workspace

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

SESSION

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

Usage Notes

NA Values in Variables Dimensioned by Simple Dimensions

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

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

Examples

Example 9-81 Defining a Simple Dimension

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

The statements

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

produce the following definition.

DEFINE city DIMENSION ID
LD List of cities

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 and the format for showing them in output.

Note:

When you want to aggregate over time do not define the time dimension as a DWMQY dimension since you cannot aggregate over dimensions of this type. Instead, define the time dimension as a hierarchical dimension of type TEXT or NTEXT.

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 ] 

Parameters

name

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

DIMENSION

The object type when you are defining a dimension.

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 want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.

SESSION

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

Usage Notes

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 is the period beginning on December 22, 1996 and ending on January 18, 1997.

Examples

Example 9-82 Defining a YEAR Dimension

The following statement defines a dimension of type YEAR that holds values for fiscal years that end on June 30.

DEFINE fyear DIMENSION YEAR ENDING june

After defining the dimension, you can give it a description and a VNF (value name format). You can use a MAINTAIN statement to give values to the dimension.

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

Example 9-83 Using the Default Phrase for Date in an ENDING Phrase

This example illustrates how DEFINE automatically supplies a phase that begins with the date '31DEC1899' when you define a multiple-period dimension of type WEEK but you do not specify a BEGINNING phase or an ENDING phase argument. Assume that you issue the following statements

DEFINE twoweek DIMENSION 2 WEEK
DESCRIBE TWOWEEK

When you issue a DESCRIBE statement for twoweek, the following output is produced.

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:

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

Parameters

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 value 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 OLAP 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 want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.

SESSION

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

Usage Notes

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:

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:

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

Example 9-84 Defining a Conjoint Dimension

Assume that you have defined and populated the simple dimensions city, state, and region and that they have the following values.

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

To define a conjoint dimension named cityandstate and add values to it use the following OLAP DML statements.

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.

Tip:

The way that you specify the values of concat dimension varies depending on whether the concat dimension is a unique or nonunique concat dimension. See "Specifying a Value of a CONCAT Dimension" for more information.

Syntax

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

Parameters

name

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

DIMENSION 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 if 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 want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.

SESSION

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

Examples

Example 9-85 Defining a CONCAT Dimension

Assume that you have defined and populated the simple dimensions city, state, and region and that they have the following values.

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

You define a concat dimension based on these dimensions using the following OLAP DML statement.

DEFINE geog DIMENSION CONCAT(region cityandstate)

The values of geog are the following.

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

Parameters

name

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

DIMENSION 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 want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.

SESSION

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

Examples

Example 9-86 Defining an Alias Dimension

Assume that your department has multiple projects that employees participate in and that an employee may be a leader of one project and a participant in another. Assume also that you want to track the hours that each employee participates in a project as either a leader or a participant. To keep track of this information, you can design a variable that is dimensioned by the time you want to track by (in this example, year), project, and two dimensions for employee—one dimension named employee for employee as participant and another dimension named leader for employee as leader. The following definitions support this structure.

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>

The following statements populate all of the dimensions.

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

Note that you do not have to explicitly populate the alias dimension (that is, leader). When you populate the employee dimension, Oracle OLAP also populates its alias dimension leader.

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

You can limit a dimension without limiting its alias; or limit an alias without limiting the dimension for which it is an alias. For example, when you issue the following statements to limit employee to Adams for project ProjA in year 2001, a report displays all of the leaders of the projects that Adams participates in.

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

On the other hand, when you limit leader to Adams for project ProjA in year 2001, a report displays all of the employees of the projects that Adams leads.

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 run time each time it is requested.

Syntax

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

Parameters

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. If you do not specify a value, the data type is determined at run time.

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

Usage 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

Example 9-87 Defining a Formula

This example adds a formula named sales.diff to an analytic workspace. This formula calculates the percent difference between total sales for the current year and last year.

The statements

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

produce the following definition.

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.

Note:

Defining a model merely creates a model object in the analytic workspace. You must also code a specification for the model, as described in MODEL.

Syntax

DEFINE name MODEL [AW workspace] [SESSION]

Parameters

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

Example 9-88 Defining a Simple Model

This example shows a simple model named income.calc that calculates the line items in an income statement. The model equations are based on the line dimension in the demo workspace. First, define the model and give it an LD.

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

Then use a MODEL statement to enter the specification for the model. For this example, you can enter model lines such as the ones in the following model description.

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

To solve the model for the actual variable, enter data in actual for the input line items (Revenue, Cogs, Marketing, Selling, R.D, and Taxes). Then execute the following statement.

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:

Parameters

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 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 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-101, "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]

Parameters

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

Usage 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 and Oracle OLAP converts any return value to the data type required by the calling context which 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

Example 9-89 Basing Program Flow on Test Results

The saleseval program tests whether total sales for a month exceeds total planned sales for the month. The program executes different statements based on the results of the test.

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

When total sales for the month exceeds total planned sales for the month, the THEN statement lines are executed. The program calculates the percentage by which actual sales exceeds planned sales and places the result in a numeric variable called excess. The program then sends the results to the current outfile. The JOINCHARS function is used to combine the calculated expression excess with the text expression "Sales exceeded plan by" in the output.

When total sales does not exceed planned sales, the ELSE statement line is executed and a different message is produced.

After the THEN or ELSE statement lines are executed, control flows to the next line in the program, and a report of sales in excess of plan is produced.


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.

Note:

Defining a relation merely adds the definition of the relation to the analytic workspace; it does not populate the relation. To populate relations using the OLAP DML, you can issue OLAP DML SQL, FILEREAD, SET, or SET1 statements.

Syntax

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

Parameters

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 want 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. The behavior specified by the SESSION keyword differs from the behavior specified by the TEMP keyword which is that the values are temporary, but the object definition remains in the workspace in which you create it.

Examples

Example 9-90 Creating, Populating, and Totaling by a Relation

The following example defines a relation between division and product, stores the values of the relation, and then totals units by division, even though units is dimensioned by product. The following statement defines the div.prod relation.

DEFINE div.prod RELATION division <product>

The following statements store values of division in div.prod.

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

You can use a REPORT statement to see the values stored in div.prod.

report div.prod

This statement produces the following output.

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

The div.prod relation lets you look at division totals in a report, even though the data is dimensioned by product.

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.

Note:

Defining a surrogate merely adds the definition of the dimension surrogate to the analytic workspace; it does not populate the surrogate. To populate surrogates using the OLAP DML, you can issue OLAP DML SQL, FILEREAD, SET, or SET1 statements.

Syntax

DEFINE name SURROGATE targetname type [AW workspace] [SESSION]

where type has the following syntax:

     [TEXT|NTEXT] [WIDTH n]|ID|INTEGER|NUMBER (precision[, scale] | datatime-datatype)

Parameters

name

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

SURROGATE

The object type when you are defining a dimension surrogate.

targetname

The name of the dimension for which you are creating a surrogate. See "Restrictions on the Use of Surrogates" for points to keep in mind when determining the target.

TEXT
NTEXT
ID

The data type for a dimension surrogate with text values. When all the values of a dimension surrogate are eight single-byte characters or less, give it a data type of ID. When one or more dimension values has more than eight single-byte characters, you must give it a data type of TEXT or NTEXT. For greater efficiency and ease of use, give dimensions a data type of ID whenever possible.

WIDTH n

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

INTEGER

The data type for a dimension surrogate with values that are the ordinal positions (1, 2, and so on) of the values in its dimension. You might create an INTEGER type dimension surrogate for a NUMBER type dimension so that you can specify dimension values by position instead of by the value of the dimension. When you define an INTEGER type dimension surrogate, Oracle OLAP automatically assigns an INTEGER value to the surrogate for each of the positions in the dimension.

NUMBER

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

precision

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

scale

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

datetime_datatype

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

AW workspace

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

SESSION

Specifies that the object exists only in the current session. When you close the current session, the object no longer exists. Use this keyword when the definition of the targetname dimension includes SESSION.

Usage Notes

Restrictions on the Use of Surrogates

Keep the following restrictions in mind when determining a target for your surrogate:

You cannot specify a dimension surrogate as the dimension or related dimension argument when you define a concat dimension, a formula, a program, a relation, a valueset, or a variable. Additionally, in data loading you cannot create new dimension values using a dimension surrogate

Examples

Example 9-91 Creating an INTEGER Dimension Surrogate

The following statement creates an INTEGER type dimension surrogate for the store_id dimension.

DEFINE storepos SURROGATE store_id INTEGER

Example 9-92 Creating a NUMBER Dimension Surrogate

The following statement creates an NUMBER type dimension surrogate for the product dimension, which is a TEXT dimension that has product names as values. The precision argument to the NUMBER keyword specifies that a value in prodnum can have no more than seven characters and the scale argument specifies that no more than three characters can be to the right of the decimal point.

DEFINE prodnum SURROGATE product NUMBER(7, 3)

The following statement sets the first value of prodnum to 1083.375.

prodnum(product 1) = 1083.375

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.

Note:

Defining a valueset adds the definition of the valueset to the analytic workspace and sets all of its values to null (NA). To assign values to a valueset use the LIMIT command. You can also use a STATUS statement and the STATFIRST, INSTAT, and VALUES functions to work with a valueset.

Syntax

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

Parameters

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 want 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. The behavior specified by SESSION is different from the behavior specified by the TEMP keyword which is that the values are temporary but the object definition remains in the workspace in which you create it.

Examples

Example 9-93 Creating and Assigning Values to a Valueset

This example adds the valueset named lineset to the demonstration workspace. The lineset valueset is dimensioned by line, and therefore it can be limited by the current values of the line dimension. The LD statement attaches a description to the object.

The following statements 1) limit the line dimension and display the values in status, 2) create a valueset named lineset by defining valueset and limiting the valueset to those values currently in status for the line dimension, and 3) display the values of the lineset.

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 

Example 9-94 Creating and Assigning Values to a Multidimensional Valueset

Assume that your analytic workspace has the variables and dimensions with the following definitions.

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

Assume also that the analytic workspace contains the following dimensions whose values are the names of variables and dimensions within the workspace.

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

The following statements create and populate a valueset for the values of all_variables and all_dims, and then report the values of that valueset.

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
 

To create a multidimensional valueset that has the correct dimensions related to the variables that use them, you issue the following statement that uses a QDR to limit the all_dims values for the salestax value of all_variables.

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:

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

Note:

Defining a variable merely adds the definition of the variable to the analytic workspace; it does not populate the variable. To populate variables using the OLAP DML, you can issue OLAP DML SQL, FILEREAD, SET, or SET1 statements.

Syntax

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

[PERMANENT | TEMP ] -

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

where:

Parameters

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:
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 verify if a variable is partitioned correctly, use the PARTITIONCHECK function.

AW workspace

The name of an attached workspace in which you want 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. The behavior specified by SESSION is different than the be3havior specified by the TEMP keyword which is that the values are temporary but the object definition remains in the workspace in which you create it.

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

Table 9-6 OLAP DML Statements for Aggcount Variables

Statement Keywords Description

DEFINE VARIABLE

WITH AGGCOUNT

Defines a variable and an associated Aggcount variable.

AGGCOUNT

 

Retrieves the values of the Aggcount variable associated with the specified variable.

CHGDFN

ADD|DROP AGGCOUNT

Adds or drops an Aggcount variable for the specified variable.

OBJ

HASAGGCOUNT

Returns a BOOLEAN value that indicates whether or not a specified variable has an Aggcount variable associated with it.


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, the AGGREGATE command, and 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 evaluating expressions using arithmetic and Boolean operators.

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

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 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. 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, 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 when 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

Example 9-95 Defining an INTEGER Variable with One Regular Dimension

This example adds the variable population to an analytic workspace. It is dimensioned by city, which has already been defined in the workspace. The LD Statement attaches a description to the object. The statements

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

produce the following description.

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

Example 9-96 Defining a Single-Cell Variable

The following is a definition for a variable named newdata which is a single Boolean value. It has no dimensions. An application might set it to YES when new data is added to the workspace and to NO after a user views the data.

DEFINE newdata BOOLEAN
newdata = YES

Example 9-97 Defining NUMBER Variables

The following statement defines a NUMBER variable named sales and dimensioned by product and geography with a precision of 16 digits and a scale of 4 digits.

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

The following statements define a NUMBER variable named numvar with 5 significant digits and 2 decimal places. The number 1234567 is out of its range.

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

A negative scale defines a NUMBER variable named numnegvar with 5 significant digits and 2 rounded digits to the left of the decimal point. The number 1,234,567 is rounded up.

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

Example 9-98 Defining a Variable Dimensioned by Two Regular Dimensions

Assume that you have an analytic workspace that contains the following definitions for dimensions, relations, and aggmaps.

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

The two parent relations (prod.parent and geog.parent) have the following values.

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

Assume that you aggregate sales_dims_reg using sales_aggmap). Now assume that you issue the following REPORT statement for a report of the sales_dims_reg variable.

REPORT sales_dims_reg->REPORT sales_dims_reg
 

As you can see from the output of the REPORT statement, the sales_dims_reg variable is a sparsely populated variable with 152 cells, many of which contain NA values.

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

Because the sales_dims_reg variable is dimensioned by two regular dimensions (rather than by composites or concat dimensions), the values of all of its cells (even those with an NA value) are stored in variable. You can confirm the number of physical values stored in the workspace by issuing the following statement.

SHOW OBJ(NUMVALS 'sales_dims_reg')
152.00

The result of the statement is that the value 152.00 displays which indicates that every value in the 152 cells of the sales_dims_reg variable (even the NA values) are stored as part of the variable.

Example 9-99 Defining a Variable Dimensioned by an Uncompressed Composite

Assume that you have created an analytic workspace with the same dimensions, relations, and aggmap as those in Example 9-98, "Defining a Variable Dimensioned by Two Regular Dimensions". Now assume that you define a composite and a variable dimensioned by that composite by issuing the following statements.

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

Assume that you populate sales_dims_composite with the same base values as you did sales_dims_reg in Example 9-98, "Defining a Variable Dimensioned by Two Regular Dimensions", and that you aggregate sales_dims_composite using the same aggmap (that is, sales_aggmap) and issue the following. REPORT statement for the sales_dims_composite variable.

REPORT sales_dims_composite
 

A report for the sales_dims_composite variable displays the same 152 cells as the report for the sales_dims_reg variable.

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

However, because the sales_dims_comp variable is dimensioned by a composite, the 65 cells that display as NA values are not stored in variable. You can confirm the number of physical values stored in the workspace by issuing the following statement that calls the OBJ function with the NUMVALS keyword on sales_dims_composite.

SHOW OBJ(NUMVALS 'sales_dims_composite')
87.00

The result of the statement is that the value 87.00 displays which indicates that only the 87 non-NA values are stored as part of the sales_dims_composite variable.

Example 9-100 Defining a Variable Dimensioned by a Compressed Composite

Assume that you have created an analytic workspace with the same dimensions, relations, and aggmap as those in Example 9-98, "Defining a Variable Dimensioned by Two Regular Dimensions". Now assume that you define a composite and a variable dimensioned by that composite by issuing the following statements.

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

Assume that you populate sales_dims_composite with the same base values as you did sales_dims_reg in Example 9-98, "Defining a Variable Dimensioned by Two Regular Dimensions", and that you aggregate sales_dims_comp_composite using the same aggmap (that is, sales_aggmap). Now you issue the following statement.

REPORT sales_dims_comp_composite
 

A report for the sales_dims_comp_comp_composite variable displays the same 152 cells as the report for the sales_dims_reg variable.

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

However, because the sales_dims_comp_comp variable is dimensioned by a compressed composite not all of values in all of the cells are stored in the variable. The 65 cells that display as NA values are not stored in variable, Also, the values that are "passed up" the hierarchy are stored only once — at the lowest level of the hierarchy.

You can confirm the number of physical values stored in the workspace by issuing the following statement that calls the OBJ function with the NUMVALS keyword on sales_dims_comp_composite.

SHOW OBJ(NUMVALS 'sales_dims_comp_composite')
38.00

The result of the statement is that the value 38.00 displays which indicates that only 38 values are stored as part of the sales_dims_comp_composite variable. These values are shown in the following table.

GEOG PROD_UPC:ColorTV PROD_UPC:BWTV PROD_UPC:StandVCR PROD_UPC:StrVCR PROD_FAMILY: VCR PROD_FAMILY: TV PROD_DIV: VideoDiv
GEOG_CITY: Canberra 11,592.0   38,356.0 3,444.00 41,800.0   53,392.0
GEOG_CITY: Darwin 24,868.0   22,104.0 32,667.0 54,771.0   79,639.0
GEOG_CITY: Brisbane 49,556.0   48,239.0 24,285.0 72,524.0   122,080
GEOG_CITY: Hobart 17,223.0   18,872.0 48,780.0 67,652.0   84,875.0
GEOG_CITY: Melbourne   22,000.0          
GEOG_AREA: Aust State 66,779.0   67,111.0 73,065.0 140,176 88,779.0 228,955
GEOG_AREA: Aust Terr 36,460.0   60,460.0 36,111.0 96,571.0   133,031
GEOG_Cont: Australia 103,239   127,57 109,176 236,747 125,239 361,986

Example 9-101 Defining a Variable with Partitions

Assume that you want to define a sales variable that is dimensioned by product and time and that is partitioned so that each year's data is in a separate partition.

Assume that the analytic workspace contains a products dimension, a time dimension that is a simple hierarchical dimension with three levels of data (day, month, and year), and a time_parentrel relation that represents the child-parent relationships between the values of time.

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

For simplicity's sake, in this example the time and product dimensions are only partially populated and have only the following values.

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

PRODUCT
-------
00001
00002

To create the partitioned variable, take the following steps:

  1. Define a partition template that defines one partition for each year's data.

    DEFINE partition_sales_by_year PARTITION TEMPLATE <time product> -
    PARTITION BY LIST (time)(-
    PARTITION time_2003 VALUES ('2003', 'Dec2003', 'Jan2003', '31Dec2003', '01Dec2003', '31Jan2003', '01Jan2003') <time product>-
    PARTITION time_2002 VALUES ('2002', 'Dec2002', 'Jan2002', '31Dec2002', '01Dec2002', '31Jan2002', '01Jan2002') <time product>)
    

    (note that for simplicity's sake, only some of each year's dimension values are specified for each partition in this example. Typically, when you want to specify a large number of values for a partition, you do not do so within the DEFINE PARTITION STATEMENT statement. Instead, you define the partition without specifying any values, and then later specify the values using MAINTAIN ADD TO PARTITION or MAINTAIN MOVE TO PARTITION statements as illustrated in Example 10-53, "Specifying the Values of a Partition Using Valuesets".)

  2. Define a partitioned sales variable with the partitions defined by the partition template named partition_sales_by_year.

    DEFINE sales DECIMAL <partition_sales_by_year<time product>>
    
  3. After you populate sales with day values, you can issue the following REPORT statement to see which sales values are in which partition.

    REPORT DOWN PARTITION(partition_sales_by_year) time product sales
     
    PARTITION(PARTITION_SALES_BY_YEAR)     TIME     PRODUCT     SALES
    ----------------------------------- ---------- ---------- ----------
    TIME_2003                           2003       00001              NA
    TIME_2003                           Dec2003    00001              NA
    TIME_2003                           Jan2003    00001              NA
    TIME_2003                           31Dec2003  00001           14.78
    TIME_2003                           01Dec2003  00001           15.52
    TIME_2003                           31Jan2003  00001           13.61
    TIME_2003                           01Jan2003  00001           10.39
    TIME_2003                           2003       00002              NA
    TIME_2003                           Dec2003    00002              NA
    TIME_2003                           Jan2003    00002              NA
    TIME_2003                           31Dec2003  00002           16.05
    TIME_2003                           01Dec2003  00002           12.27
    TIME_2003                           31Jan2003  00002           10.83
    TIME_2003                           01Jan2003  00002           11.07
    TIME_2002                           2002       00001              NA
    TIME_2002                           Dec2002    00001              NA
    TIME_2002                           Jan2002    00001              NA
    TIME_2002                           31Dec2002  00001           18.80
    TIME_2002                           01Dec2002  00001           13.64
    TIME_2002                           31Jan2002  00001           12.41
    TIME_2002                           01Jan2002  00001           16.97
    TIME_2002                           2002       00002              NA
    TIME_2002                           Dec2002    00002              NA
    TIME_2002                           Jan2002    00002              NA
    TIME_2002                           31Dec2002  00002           17.47
    TIME_2002                           01Dec2002  00002           16.58
    TIME_2002                           31Jan2002  00002           18.94
    TIME_2002                           01Jan2002  00002           18.36
    

Example 9-102 Defining a Fixed-Width TEXT Variable

The following statement defines a TEXT variable named lastname dimensioned by employee. Values in lastname are limited to 20 characters, so that longer values are truncated.

DEFINE lastname TEXT <employee> WIDTH 20 

Example 9-103 Defining a Variable That Uses a Named B-Tree Composite

Assume that you have the following dimensions in your analytic workspace.

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

When your company does promotional marketing for certain products in some but not all regions, then your variable data is sparse along the product and region dimensions. Therefore, suppose you define a composite named proddist, whose base dimensions are product and region. There are dimension-value combinations in the composite only for those values that have data. For example, when you run a promotion for tents but not skis, then the composite includes the tents and region combinations, but not the skis and region combinations.

The following statement creates a b-tree composite named proddist whose base dimensions are product and district, and a variable called promo that is dimensioned by month and proddist.

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

For simplicity's sake assume that you have only stored the following dimension data in your analytic workspace.

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

You decide to run a promotional sales for skis in the Northeast region in the month of September, 2003 at a cost of $5,000. Once you populate promo with this, promo contains only 12 cells—each cell is dimensioned by a value of month and the composite tuple value of <'Skis' 'Northeast'> for proddist. The cell for September 2003 contains the value $5,000, and all of the other cells contain NA. No other NA values are stored in promo; there are no cells are created for any other values of product or region.

Example 9-104 Defining a Variable with Null Tracking

Assume that you have the following objects defined in your analytic workspace.

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

Now assume that you define a sales variable that you want to have dimensioned by time and the prod_geog composite. You want this variable to have null tracking because you eventually populate it using SQL IMPORT and you know that some facts in the fact table have null values. To do this you issue the following statement that includes the WITH NULLTRACKING phrase.

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

For testing purposes, you populate the variable using the RANDOM function. After you populate the variable in this way, you issue a report on it that shows the NA values in the variable.

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

For testing purposes, you also generate a report using the NAFLAG function to retrieve the type of NAs that are in the variable. As the following report shows, because it was populated using RANDOM, all of the NAs are the typical NA values; they are not NA2 values.

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

Again, for testing purposes, you use the NA function to set an NA2 bit on the variable cells dimensioned by the months of 2008. The following code shows the result of issuing a SHOW of the NA2 function and using that function to set the NA2 bit on the cells dimensioned by the months in 2008.

SHOW NA2
NA

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

For brevity's sake assume that your test now issues the following three LIMIT statements and then reports on the sales variable and the NAFLAG function against the sales variable. As the NAFLAG report illustrate, the value Jan08 which is a month to which an NA2 value was assigned returns the value of 2 for NAFLAG, while the NAFLAG report still returns the value of 1 for the year 2008.

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

Now assume that you issue the following code to remove the NA2 bits from the sales variable.

CHGDFN sales DROP NULLTRACKING

A DESCRIBE of the sales variable shows that it no longer has the WITH NULLTRACKING phrase in its definition while a report of the results of NAFLAG show that the NA values are now just the typical NA values without an NA2 bit.

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]

Parameters

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 is 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 want 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. The behavior specified by SESSION is different than the behavior specified by the TEMP keyword which is that the values are temporary but the object definition remains in the workspace in which you create it.

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

Examples

Example 9-105 Defining a Worksheet

These statements define a temporary worksheet named travelexp, which is dimensioned by columns and rows.

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

Example 9-106 Importing Spreadsheet Data

You can import data from a spreadsheet to a worksheet. When all the cells contain the same type of data, you can use UNRAVEL to transfer the data to a variable with one statement. You can also limit the worksheet dimensions to a smaller group of cells and use UNRAVEL to transfer each group to a separate variable. To transfer imported data from a worksheet named itemsheet to a variable named items, you might use the following statements.

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