Previous
Previous
 
Next
Next


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:

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: Defining a Variable Dimensioned by an Uncompressed Composite and Example: Defining a Variable Dimensioned by a Compressed Composite.

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