| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
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]
Arguments
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 RESERVED.
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. |
The type of object being defined. The default is VARIABLE. The object types are discussed in the subsections for the DEFINE command.
Attributes are different for each type of object. The attributes are listed in the entry for each object type.
The name of an attached workspace in which you wish to define the object. You can also specify a noncurrent attached workspace using a qualified object name for name. Do not use this phrase when qualified object name for name.
Specifies that the object exists only in the current session. The object is created in the EXPRESS analytic workspace to which you have read-only access. When you close the current session, the object no longer exists.
Notes
To modify an existing object definition, use CHGDFN.
A DEFINE statement defines a basic object definition.You can extend that definition to include a calculation specification, a long definition, properties, permissions, format information, and triggers.
Use DESCRIBE to view the basic definition of an object, its calculation specification, a long definition, properties, permissions, format information. To view the complete definition of an object (including its properties and triggers), use FULLDSC.
A DEFINE statement to create a definition for an aggmap object, formula, model or program merely creates a definition for the object. It does not define the calculation specification for the object. After you define the object, you must explicitly add the calculation specification. You can add the specification using an Edit window in the OLAP Worksheet or using one of the following OLAP DML statements:
AGGMAP to add an aggregation specification to the definition of an aggmap object.
ALLOCMAP to add an allocation specification to the definition of an aggmap object.
EQ to add a calculation specification to the definition of a formula.
MODEL to add a calculation specification to the definition of a model.
PROGRAM to add a calculation specification to the definition of a program.
Use LD to add a long description to the definition of an object.
Use PROPERTY to add one or more properties to the definition of an object.
Use PERMIT to specify permissions for an object.
Use the TRIGGER command to specify triggers for an object.
Using a TRIGGER_DEFINE program, you can make the DEFINE command an event that will automatically execute an OLAP DML program. See "Trigger Programs" for more information.
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.
Objects created with the SESSION keyword are stored in the analytic workspace named EXPRESS instead of the current analytic workspace. Therefore, statements that operate against the current analytic workspace (such as LISTNAMES) do not list session objects unless you do one of the following:
Specify the EXPRESS analytic workspace in the statement (such as LISTNAMES AW EXPRESS)
Make the EXPRESS analytic workspace the current analytic workspace by issuing an AW ATTACH EXPRESS statement.
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.
|
Note: 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]
Arguments
The name of the object that you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining an aggmap.
(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.
The name of an attached workspace in which you wish to define the object. For more about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. For more information about this argument, see the main entry for the DEFINE command.
Notes
Most aggmap objects are defined to calculate variable values that are dimensioned by permanent dimension members (that is, dimension members that persist from one session to another). However, at runtime, users might wish to aggregate or allocate data for their own use for forecasting or what-if analysis, or just because they want to view the data in an unforeseen way. Adding temporary members to dimensions and aggregating or allocating data for those members is sometimes called creating temporary or custom aggregates or allocations. For an example of creating temporary aggregates, see Example 16-38, "Creating Calculated Dimension Members with Aggregated Values".
Examples
Example 10-7 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 command 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 the 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 10-8 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 commands, the SOURCEVAL command and the CHILDLOCK command. You end the entry of statements into the aggmap with the END command. 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 command you specify that after the allocation, ALLOCATE sets the value of each source cell to zero. With the CHILDLOCK command 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.
The DEFINE command with the COMPOSITE keyword adds a new named composite to an analytic workspace.
Conceptually, you can think of a composite consisting of two structures:
The composite object itself. The composite contains the dimension-value combinations (that is, a composite tuples) that Oracle OLAP uses to determine the structure of any variables dimensioned by the composite.
An index between the composite values and its base dimension values.
You define a variable using one or more composites when you want to reduce the amount of NA values stored in the variable. Reducing the sparsity of a variable in this way results in more efficient data storage as discussed in "B-Tree and Hash 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:
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a named composite.
The names of two or more dimensions or b-tree or hash composites that you want to be the base dimensions of the composite. When you specify COMPRESSED as the value of index-algorith , at least one of the dimensions must be a hierarchal dimension.
|
Note: A compressed composite cannot be a base dimension of another composite. You cannot specify a compressed composite as one of the dimensions in dims. |
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".
For compressed composites, specify the dimensions in order by hierarchy depth and the degree of aggregation—shallowest to deepest, least to most aggregated. When these requirements conflict, experiment to determine the most effective order. Use values returned by OBJ function with the PHYSVALS keyword to evaluate the results of your experimentation.
You must define all the dimensions and named composites used in the list before defining the composite. DEFINE will automatically create any unnamed composites in the list for you.
The name of an attached workspace in which you wish to define the object. For more information about this argument, see the main entry for the DEFINE command.
Specifies the creation of a b-tree index to relate composite values to base dimension values. BTREE is the standard indexing method for composites.
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 will be aggregated using the AGGREGATE command and when at least one hierarchical dimension is specified in dims. (See "Compressed Composites" for more information.)
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.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Notes
For a variable that is dimensioned by a b-tree or hash composite, Oracle OLAP creates variable cells only for those dimension values that are stored in the tuples of the composite; it does not create a cell for every value in the base dimensions. Data for the variable is stored in order, cell by cell, for each tuple in the composite. From the perspective of data storage, each combination of base dimension values in a composite is treated like the value of a regular dimension. This means that when you define a variable with one regular dimension and one composite, the data for the variable is stored as though it was a two-dimensional variable.
Before Oracle OLAP populates a cell in a variable dimensioned by a b-tree or hash composite, it first determines if the dimension-value combination for that variable cell is already in the composite (that is, if the composite tuple exists). When the composite tuple exists, Oracle OLAP stores the data in the variable using the existing composite structure. When the composite tuple does not exist, then Oracle OLAP populates the composite and the composite index before it stores the data in the variable. For an example of populating a variable with a composite, see Example 10-29, "Defining a Variable That Uses a Named B-Tree Composite".
The actual sparsity of a variable dimensioned by a composite varies depending on whether or not the composite is an unshared composite or a shared composite:
An unshared composite is a b-tree, hash, or compressed composite that is used to dimension only one variable. An unshared composite is populated only when the variable that uses it is populated. Consequently, an unshared composite perfectly reflects the sparsity of the variable that it is used to dimension. It only has the dimension value combinations for each non-NA value in that variable.
A shared composite is a b-tree or hash composite that is used to dimension more than one variable. (A compressed composite can dimension only one variable or one partition of a variable. A compressed composite cannot be a shared composite.) A shared composite is populated when any of the variables that use it are populated. A shared composite has all of the dimension value combinations for non-NA values for all of the variables that it dimensions. A shared composite reflects the sparsity of all of the variable that it is used to dimension. Typically, therefore, variables dimensioned by shared composites are not perfectly sparse variables.
When the size of variables is important, when you have variables that will be sparse along the same dimensions, but will have significantly different patterns of sparsity, define different composites for the different variables.
In some cases, when you aggregate data in a variable dimensioned by a composite defined with one or more hierarchical dimension, one parent node may have only one descendant node — and so on all the way up to the top level. When a variable has a good deal of this type of sparsity, use a compressed composite as the dimension of the variable. Dimensioning this type of variable with a compressed composite creates the smallest possible variable, composite, and composite index—much smaller than if you dimension a variable with a b-tree or hash composite.
This reduction in size does not occur at the detail level. Oracle OLAP creates composite values for detail level the same way for all composites. A composite contains one composite tuple for each set of base dimension values that identifies non-NA detail data in the variables that use it.
The reduction in size occurs for those sets of base dimension values that identify non-NA data at higher levels of hierarchical dimensions. Oracle OLAP populates these higher-level values differently depending on whether a variable is dimensioned by a b-tree, hash, or compressed composite:
For variables dimensioned by b-tree and hash composites, Oracle OLAP creates composite tuples for non-NA data at higher levels the same way that it does for non-NA data at the detail level. There is one composite tuple (with its own physical position) for each set of base dimension values that identifies non-NA data. The composite index contains all of the index entries needed to relate the composite tuple to the base dimension values.
For variables dimensioned by compressed composites, Oracle OLAP reduces redundancy in the variable, composite, and composite index by using the"intelligence" of the AGGREGATE command that populates the variable. For sets of base dimension values that represent parent nodes, Oracle OLAP creates a physical position in the composite only for those tuples that represent a parent with more than one descendant. Oracle OLAP then creates an index between this composite structure and the base dimensions and uses this composite structure as the dimension of the variable. Since the actual structure of a compressed composite is smaller than that of a b-tree or hash composite, a variable dimensioned by a compressed composite is also smaller than a variable dimensioned by a b-tree or hash composite. Also, since the index for a compressed composite only has nodes for parents with more than one descendant, the index of a compressed composite has fewer levels and is smaller than the index of a b-tree composite.
Although performance varies depending on the depth of the hierarchies and the order of the dimensions in the composite, aggregating variables defined with compressed composites is typically much faster than aggregating variables defined with b-tree or hash composites.
Oracle OLAP automatically defines an unnamed composite when a DEFINE VARIABLE statement with a SPARSE <dimlist> phrase executes. An unnamed composite can have either a b-tree or hash index. The type of index is determined by the value of the SPARSEINDEX option at the time that Oracle OLAP defines an unnamed composite.
Once Oracle OLAP has created a definition for an unnamed composite for a certain dimension list, it uses that composite any time you define a variable with the same SPARSE <dimlist> phrase. Thus all variables that are defined with the same SPARSE <dimlist> phrase share the same unnamed composite. For more information on sharing composites, see "Unshared and Shared Composites".
Examples
Example 10-9 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>>
The DEFINE command with the DIMENSION keyword adds a new dimension object to an analytic workspace. A dimension is a list of values that provides an index to the data.
Because the syntax of the DEFINE DIMENSION command is different depending on the type of the dimension that you are defining, four separate entries are provided:
DEFINE DIMENSION (simple) for defining a dimension with unique values of the same data type.
DEFINE DIMENSION (DWMQY) for defining a non-hierarchical dimension whose values represent a time period (day, week, month, quarter, or year).
DEFINE DIMENSION (conjoint) for defining a dimension over two or more other base dimensions when the base dimesnions do not contain duplicate values or have different data types and when you want to explicitly specify the dimension value combinations.
DEFINE DIMENSION CONCAT for defining a dimension over two or more other base dimension when the base dimensions contain duplicate values or different data types or when you want Oracle OLAP to automatically populate the dimension value combinations.
DEFINE DIMENSION ALIASOF for defining an alias for a simple dimension.
The DEFINE DIMENSION (simple) command defines a simple dimension. A simple dimension is a list of 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])
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a dimension.
Specifies that the values of the dimension have the TEXT data type which is equivalent to the CHAR and VARCHAR2 data types in the Oracle database. This data type stores up to 4000 bytes for each line in the database character set.
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.
Specifies a special text data type that stores up to 8 single-byte characters for each line in the database character set.
For TEXT or NTEXT dimensions, the width, in bytes, of the storage area of each value of an object. Valid width values are 1 through 4000. Specify a fixed width only when you are certain that the values of a particular dimension are of similar size. When a value exceeds the specified width, it will be truncated.
Specifies that the values of the dimension have the INTEGER data type. The data type for a dimension with values that are identified by their numeric position (1, 2, and so on). A data type of INTEGER means that the dimension has no character values. For ease of use, you should use a text or time period data type, when possible.
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.
The total number of digits a value of type NUMBER can have.
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 do not specify a scale value, then the scale is 0.
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.
The name of an attached analytic workspace in which you wish to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Examples
This example adds the dimension city to a workspace. You can attach a description to the object immediately after defining it. (You can also add the description later when you use the CONSIDER and LD commands.) After defining the dimension city, you can give it values with the MAINTAIN command.
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
The DEFINE DIMENSION (DWMQY) command defines a special type of dimension whose values represent time periods.
|
Note: After defining a DWMQY dimension, you can use the VNF command to add a value name format to the dimension's definition. The VNF command controls the format for entering dimension values as well as the format for showing them in output. |
|
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 typeTEXT 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]
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a dimension.
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.
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'. |
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.
The name of an attached analytic workspace in which you wish to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Notes
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.
For dimensions of type MONTH, QUARTER, and YEAR, the BEGINNING phase or ENDING phase argument is especially useful for data organized on a fiscal-year calendar.
By specifying a phase for a dimension of type MONTH or QUARTER, you identify the time period that is the first or last period within a year. For example, when you define a dimension of type MONTH with an ending phase of June, then June is identified as the twelfth month of the year. When a dimension of type QUARTER has an ending phase of June, the quarter ending in June is identified as the fourth quarter of the year. When you give a dimension a VNF that includes a period code, you can enter or report dimension values according to their period within the year.
By default, the single or multiple weeks in a dimension of type WEEK end on Saturday. The BEGINNING phase or ENDING phase argument lets you specify the day of the week on which each period begins or ends. For multiple-week periods, the phase argument also controls the starting or ending date for grouping the weeks into periods. By default, the starting point for grouping multiple weeks is December 31, 1899 (a Sunday).
However, the phase argument does not determine the period that is counted as the first period within a year. For dimensions of type WEEK, Period 1 in a given calendar year is always the first period that ends in that year. For example, suppose you specify a dimension of type WEEK with a four-week period ending on June 7, 1997. DEFINE works backward and forward from this date, forming weeks into four-week periods. For 1997, Period 1 will be the period beginning on December 22, 1996 and ending on January 18, 1997.
Examples
Example 10-10 Defining a YEAR Dimension
The following statement defines a dimension of type YEAR that will hold 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 the MAINTAIN command to give values to the dimension.
LD Fiscal years ending June 30 VNF 'FY<ff>' MAINTAIN fyear ADD 'FY97' 'FY00'
Example 10-11 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'
The DEFINE DIMENSION (conjoint) command defines a conjoint dimension.
Conceptually, you can think of a conjoint dimension consisting of two structures:
The dimension object itself. The values of the dimension are combinations of values of two or more other dimensions (that is, a conjoint tuples) that Oracle OLAP uses to determine the structure of any variables dimensioned by the conjoint dimension.
An index between the conjoint dimension values and its base dimension values.
Composites are another object that you can use to dimension a variable using a list of dimension value combinations. See "Differences Between Conjoint Dimensions and Composites" for a discussion of the major differences between composites and conjoint dimensions.
Syntax
DEFINE name DIMENSION <dims. . .> index-algorithm [AW workspace] [SESSION]
where:
index-algorithm specifies the algorithm that Oracle OLAP uses to create the index into the conjoint dimension. Valid values for index-algorithm are:
Arguments
The name of the conjoint dimension you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a conjoint dimension.
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". 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. You cannot have as base dimensions a simple dimension and a conjoint or concat dimension that has same simple dimension as one of its bases. 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>
However, the following definition is not permitted because the same simple dimension, simpledim.a, is a base dimension of conjointdim.d and a component of concatdim.a.
DEFINE conjointdim.d DIMENSION <simpledim.a, concatdim.a>
The following definition is not permitted because the same simple dimension is a base dimension of conjointdim.e and a base dimension of conjointdim.a.
DEFINE conjointdim.e DIMENSION <simpledim.a, conjointdim.b>
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.
|
Note: When you are unsure whether to specify BTREE or NOHASH, use NOHASH, since you can always use the CHGDFN command to change a NOHASH conjoint into a BTREE conjoint, while you can use the CHGDFN command to change a BTREE conjoint into a NOHASH conjoint only when the conjoint was originally defined as a NOHASH conjoint. |
Specifies that Oracle OALP does not create an index for the conjoint dimension, but instead uses internal structures to relate conjoint values to base dimension values.
|
Note: 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. |
(Not recommended.) Specifies the creation of a has index to relate conjoint values to base dimension values. (Default)
|
Important: 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. |
The name of an attached analytic workspace in which you wish to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Notes
You can use either a composite or a conjoint dimension to dimension a variable with a list of dimension value combinations. Keep the following points in mind when deciding on which type of object to use:
Object population maintenance—Conjoint dimensions offer the most control, while composites provide the greatest ease of use:
Oracle OLAP determines the dimension value combinations stored in a composite. Oracle OLAP populates a composite automatically when a variable dimensioned by composite is populated.
You determine the dimension value combinations that are stored in a composite. You must explicitly populate and maintain a conjoint dimension using MAINTAIN statements the same way you populate and maintain other dimensions.
Dimension operations —You can perform dimension operations on conjoint dimensions, but not composites; however, you can only perform dimension operations on the base dimensions of composites. For example, you can LIMIT conjoint dimensions, but you must limit the base dimensions of a composite to limit your view to a subset of composite values; and you can define relations using conjoint dimensions, but not composites.
For more information on composites, see DEFINE COMPOSITE.
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.
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.
To refer to the value of a conjoint dimension in an expression, specify the value following these guidelines:
Enclose the entire dimension value specification in angle brackets and then enclose this entire specification in single quotes; do not enclose the individual values in single quotes.
Use the exact upper- and lowercase spellings for the base dimension values.
When the specification includes a text value with an embedded blank, you must separate the dimension values with commas.
For example, when item.org is a conjoint dimension with base dimensions item and org, use the following format to refer to values of item.org.
'<Expenses, Direct Sales>'
Examples
Example 10-12 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'>
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.
|
Note: The way that you specify the values of concat dimension varies depending on whether or not the concat dimension is a unique or nonunique concat dimension. See Values of CONCAT Dimensions for more information. |
Syntax
DEFINE name DIMENSION CONCAT(basedimlist. . .)[UNIQUE] [TEMP] [AW workspace] [SESSION]
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a concat dimension.
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". You must enclose the dimension list in parenthesis.
The types of dimensions that can be base dimensions varies depending on whether you are defining a unique or nonunique concat dimension:
When defining a non-unique concat dimension, a base dimension can be a simple dimension of any data type, a conjoint dimension, or another concat dimension.
When defining a unique concat dimension, a base dimension can be a simple dimension of type TEXT or ID, or another unique concat dimension as long as the data values of all of the base dimensions are unique and not duplicated in any of the base dimensions.
A composite cannot be the base dimension of a concat dimension.
Simple dimensions and conjoint dimensions are the bottom-level components of a concat dimension. When you specify a concat dimension as a base dimension when defining a concat, then the base dimensions of that inner concat are component dimensions of the outer concat.
The same dimension cannot appear more than once in the component dimensions of a concat dimension. However, in a concat, a conjoint dimension is an indivisible unit and Oracle OLAP does not consider the base dimensions of a conjoint in the definition of the concat. Therefore, a simple dimension can be a base dimension of a conjoint and that conjoint and the same simple dimension can be base dimensions (or components) of a concat dimension.
For example, the following definitions are permissible.
DEFINE conjointdim.a DIMENSION <simpledim.b, simpledim.c> DEFINE conjointdim.b DIMENSION <simpledim.a, simpledim.b> DEFINE conjointdim.c DIMENSION <simpledim.a, conjointdim.a> DEFINE concatdim.a DIMENSION CONCAT (simpledim.a, conjointdim.a) DEFINE concatdim.b DIMENSION CONCAT (simpledim.a, conjointdim.b) DEFINE concatdim.c DIMENSION CONCAT (simpledim.b, conjointdim.b) DEFINE concatdim.d DIMENSION CONCAT (simpledim.a, concatdim.c)
In the definition of concatdim.a, the base dimensions are simpledim.a and conjointdim.a. In the definition of concatdim.d, the base dimensions are simpledim.a and concatdim.c. The component dimensions of concatdim.d are simpledim.a, simpledim.b, and conjointdim.b. simpledim.a and simpledim.b appear only once as component dimensions even though they are the base dimensions of conjointdim.b because the base dimensions of a conjoint are not component dimensions of a concat.
However, the following definition is not permitted because the same simple dimension is a base dimension of concatdim.e and a component of concatdim.e because it is a base dimension of concatdim.b.
DEFINE concatdim.e DIMENSION CONCAT (simpledim.a, concatdim.b)
|
Note: The simple dimensions in the basedimlist argument, and the simple dimensions that are base dimensions of any conjoint dimensions or concat dimensions in basedimlist, cannot have an INTEGER data type. |
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.
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.
The name of an attached analytic workspace in which you wish to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Notes
Once you have defined a unique CONCAT dimension, you can refer to its values simply by specifying the values of the base dimensions.
However, you must specify the values of a nonunique CONCAT dimension as a concatonation of the name of the base dimensions and the base dimension values separated by a colon (:) and a space and enclosed in angle brackets(<>). In an expression, use the following format.
<BASE_DIMENSION_NAME: base_dimension value>
For example, assume that you have defined the base dimensions named city and state and, a CONCAT dimension for them named geog. When you report on the geog dimension, the values of geog include the names of the base dimensions along with the values.
DEFINE city DIMENSION TEXT DEFINE state DIMENSION TEXT DEFINE geog DIMENSION CONCAT(city state) MAINTAIN city ADD 'New York' MAINTAIN state ADD 'New York' REPORT geog GEOG ----------------------------------- <CITY: New York> <STATE: New York>
Examples
Example 10-13 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>>
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 the LIMIT command to limit alias dimensions and define variables and relations using an alias dimension. However, you cannot maintain an alias dimension directly; instead you maintain its base dimension using MAINTAIN.
Syntax
DEFINE name DIMENSION ALIASOF dimension [TEMP] [AW workspace] [SESSION]
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a dimension. Indicates that the dimension being defined is an alias for another 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.
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.
The name of an attached analytic workspace in which you wish to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Examples
Example 10-14 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. In order 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, it's alias dimension leader, is also populate as you can see when you issue REPORT statements for all four dimensions.
YEAR -------------- 2001 2002 2003 PROJECT -------------- projA projB 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
The DEFINE command with the FORMULA keyword adds a new formula object to an analytic workspace. You define a formula to save an expression. A formula can take the place of an expression you use repeatedly. The name of the formula takes the place of the text of the expression. Oracle OLAP does not store the data for a formula in a variable; instead it is calculated at runtime each time it is requested.
Syntax
DEFINE name FORMULA {expression|datatype [<dimensions...>]} [AW workspace] [SESSION]
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a formula.
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 3, " 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. |
The intended data type for the formula. You can use any of the data types that apply to variables. The datatype argument is optional. When you include an expression in the formula definition, you do not specify a value. DEFINE automatically determines the data type.
However, when you do not include an expression in the definition, you must specify the data type. When you add the expression later 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.
|
Tip: You can determine the data type of an expression before adding it to a formula by using the PARSE command and INFO (PARSE) function. |
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. |
The name of an attached workspace in which you wish to define the formula. When the formula is dimensioned, it must be defined in the same workspace as its dimensions. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Notes
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.
To define a very complex calculation, you can define a program that uses a RETURN command to return a value. You can then use the program as a function wherever you would use an expression or formula.
Examples
Example 10-15 Defining a Formula
This example adds a formula named sales.diff to a 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)
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]
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a model.
The name of an attached workspace in which you wish to define the object. For more information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Examples
Example 10-16 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 the MODEL command 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
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. You must define a partition template object before you can define a partitioned variable.
Syntax
DEFINE name PARTITION TEMPLATE <dimlist> PARTITION BY
{RANGE|LIST|CONCAT} (dims_partitioned_by) ([partition_definition_statement...]) [AW workspace]
where:
partition_definition_statement defines a partition. The syntax varies depending on whether you specify RANGE, LIST, or CONCAT:
When you specify RANGE, the syntax for partition_definition_statements is:
PARTITION partition-name VALUES LESS THAN const-exp <partition-dimlist>
When you specify LIST, the syntax for partition_definition_statements is:
PARTITION partition-name VALUES ([valuelist)] <partition-dimlist>
When you specify CONCAT, the syntax for partition_definition_statements is:
PARTITION partition-name <partition_basedimlist>
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
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.
Indicates partitioning by values within a specified range. The syntax for partition_definition_statements is:
PARTITION partition-name VALUES LESS THAN const-exp <partition-dimlist>
Indicates partitioning by listed values. The syntax for partition_definition_statements is:
PARTITION partition-name VALUES [(valuelist)] <partition-dimlist>
Indicates partitioning by base dimensions of a concat dimension. The syntax for partition_definition_statements is:
PARTITION partition-name <partition_basedimlist>
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.
|
Note: You cannot partition a variable along anINTEGER dimension. |
The name of the partition.
Indicates that you are specifying a RANGE partition by comparing values.
A constant expression that has the same data type as the data type of the dimension specified for dims_partitioned_by.
A list of all of the of dimensions of the partition template object (although the dimensions may be members of a composite). You must enclose the names of the dimensions in a single set of angle brackets (< >). Use this argument to specify the composite (if any) used to dimension the partitions that correspond to partition-name. When you do not specify a value then the partition is dimensioned densely by all of the of dimensions of the partition template object.
Indicates that you are specify a LIST partition by specifying values.
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 MOVE TO PARTITION statement to specify values for the partition. |
A list of dimensions for the partition enclosed in a single set of angle brackets (< >). For every dimension of the partition template, basepartition-dimlist must include either that dimension, a base of that dimension, a concat of the base dimensions of that dimension, or a composite that includes that dimension, its base, or concat dimension of its base dimensions. In other words, basepartition-dimlist must have the same number of logical dimensions as the partition template itself (that is, the dimensions in dimlist).
Each listed dimension must be one of the following:
A partition template object with the appropriate dimensionality specified for its dimlist parameter.
A base dimension for one of the dimensions listed in dims_partitioned_by.
A dimension of the partition template that is not in dims_partitioned_by.
A composite consisting of dimensions of partition template object with the appropriate dimensionality specified for its dimlist parameter, a base dimension for one of the dimensions listed in dims_partitioned_by, or a dimension in dimlist that is not in dims_partitioned_by.
Examples
|
See: Examples of defining partition template objects are integrated into the following examples of defining partitioned variables: |
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.
|
Note: Defining a program merely creates a program object in the analytic workspace. You must also code the actual lines of the program, beginning with PROGRAM command. |
Syntax
DEFINE name PROGRAM [datatype|dimension] [AW workspace] [SESSION]
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a program.
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.
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.
The name of an attached workspace in which you wish to define the program. When the program returns a dimension, the program must be defined in the same workspace as the dimension. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.
Notes
Use a RETURN command in a program when you want it to return a value. The argument to the RETURN command is an expression that specifies the value to return. When the expression does not match the declared data type or dimension, the value is converted (if possible) to the declared data type or dimension value.
When you do not specify a data type or dimension in the definition of a program, its return value is treated as worksheet data. This means Oracle OLAP converts any return value to the data type required by the calling context. This may lead to unexpected results.
For a program to return a value, you must call the program as a function. That is, you must use it as an expression in a command. 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.
When you call the program as a function, but it does not use the RETURN command to provide a return value, the program returns NA.
Examples
Example 10-17 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 command 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 command line is executed and a different message is produced.
After the THEN or ELSE command lines are executed, control flows to the next line in the program, and a report of sales in excess of plan is produced.
The DEFINE command with the RELATION keyword adds a new relation object to an analytic workspace. A relation describes a correspondence between the values of two or more dimensions. It can have dimensions, just like a variable, but the values of the relation must be values from the related dimension.
Syntax
DEFINE name RELATION related-dim [<dimensions...>] [TEMP] [AW workspace] [SESSION]
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a relation.
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).
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. |
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.
The name of an attached workspace in which you wish to define the relation. The relation must be defined in the same workspace as its dimensions. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When the session ends, the object no longer exists. This differs from the TEMP keyword, which specifies that the values are temporary but the object definition remains in the workspace in which you create it.
Notes
When you define two or more dimension 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 dimension of type YEAR.
Examples
Example 10-18 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 command 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)
The DEFINE command with the SURROGATE keyword adds a a new 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: 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. |
Syntax
DEFINE name SURROGATE targetname type [AW workspace] [SESSION]
where:
type has the following syntax:
[TEXT|NTEXT] [WIDTH n]|ID|INTEGER|NUMBER (precision[, scale])
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a dimension surrogate.
The name of the dimension for which you are creating a surrogate.
|
Note: Keep the following restrictions in mind when determining a target for your surrogate:
|
The data type for a dimension surrogate with text values. When all the values of a dimension surrogate are eight single-byte characters or less, give it a data type of ID. When one or more dimension values has more than eight single-byte characters, you must give it a data type of TEXT or NTEXT. For greater efficiency and ease of use, you should give dimensions a data type of ID whenever possible.
For TEXT or NTEXT dimension surrogate, the width, in bytes, of the storage area of each value of an object. Valid width values are 1 through 4000. Specify a fixed width only when you are certain that the values of a particular dimension surrogate are of similar size. When a value exceeds the specified width, Oracle OLAP truncates it.
The data type for a dimension surrogate with values that are the ordinal positions (1, 2, and so on) of the values in its dimension. You might create an INTEGER type dimension surrogate for a NUMBER type dimension so that you can specify dimension values by position instead of by the value of the dimension. When you define an INTEGER type dimension surrogate, Oracle OLAP automatically assigns an integer value to the surrogate for each of the positions in the dimension.
Specifies that the dimension surrogate has a data type of NUMBER.
Specifies the total number of characters in the value of a dimension surrogate of type NUMBER.
Specifies the number of characters that can be to the right of a decimal point of a dimension surrogate of type NUMBER.
The name of an attached workspace in which you wish to define the dimension surrogate. The dimension for which you define the surrogate must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.
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.
Examples
Example 10-19 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 10-20 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
The DEFINE command with the VALUESET keyword adds a new valueset object to an analytic workspace. A valueset contains a list of dimension values for a dimension. The values in a valueset can be saved across sessions. When you begin a new session or start up a workspace, each dimension has all values in the status. You can then limit a dimension to the values stored in the valueset for that dimension.
|
Note: When you first define a valueset, its value is null. You must eplicitly assign values to the valueset as described in "Assigning Values to a Valueset". |
Syntax
DEFINE name VALUESET dimension [<dims...>] [TEMP] [AW workspace] [SESSION]
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a valueset.
The name of the dimension whose values you want to store in the valueset.
The names of the dimensions, if any, of the valueset. You must define a dimension before you include it in the definition of a valueset.
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.
The name of an attached workspace in which you wish to define the valueset. The valueset must be defined in the same workspace as its dimensions. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When the session ends, the object no longer exists. This differs from the TEMP keyword, which specifies that the values are temporary but the object definition remains in the workspace in which you create it.
Notes
When you first define a valueset, its value is null. Use the LIMIT command to assign values to the valueset or to change its values. Use the STATUS command and functions such as STATFIRST, INSTAT, and VALUES to work with a valueset.
Examples
Example 10-21 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 command attaches a description to the object.
The statements
LIMIT line TO FIRST 2 STATUS line
produce the following output.
The current status of LINE is: REVENUE, COGS
The statements
DEFINE lineset VALUESET line LD Valueset for LINE dimension values LIMIT lineset TO line SHOW VALUES(lineset)
produce the following output.
Revenue Cogs
Example 10-22 Creating and Assigning Values to a Multidmensional 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 statement creates a valueset for the values of all_variables and all_dims.
DEFINE variables_dims VALUESET all_dims <all_variables>
REPORT values(variables_dims)
ALL_VARIABLES VALUES(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 values(variables_dims)ALL_VARIABLES VALUES(VARIABLES_DIMS)---------------- ------------------------------sales geography productsalestax geography
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:
Dimensionality
Permanent or temporary
Unpartitioned or partitioned
You can also define local variables in programs using a VARIABLE statement. These variables exist only as long as the program is running.
Syntax
DEFINE name [VARIABLE] datatype [<dims...>] [PERMANENT | TEMP ] -
[(partition-instance...)] [WIDTH n] [AW workspace] [SESSION]
where:
partition-instance has the following syntax.
PARTITION partition-name [ {INTERNAL [TEMP | PERMANENT] } | {EXTERNAL target} ]
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a variable. You do not have to include the word VARIABLE, because it is the default.
The kind of data to be stored in the variable. The data types, their abbreviations, and the range of acceptable values are shown in Table 10-1, "Valid Data Types for Variables".
Table 10-1 Valid Data Types for Variables
| Data Type | Abbreviation | Values |
|---|---|---|
| INTEGER | INT | Whole numbers in the range of (-2**31) to (2**31)-1 |
| LONGINTEGER | LONGINT | Whole numbers in the range of (-2**63) to (2**63)-1 |
| SHORTINTEGER | SHORTINT | Whole numbers in the range of (-2**15) to (2**15)-1 |
| NUMBER [(p,[s])] | None | Decimal numbers with up to 38 significant digits, as defined by the precision and scale where precision (p) is a whole number between 1 and 38 and scale (s) is a whole number between -84 and 127 |
| DECIMAL | DEC | Decimal numbers with up to 15 significant digits |
| SHORTDECIMAL | SHORT | Decimal numbers with up to seven significant digits |
| TEXT | None | TEXT data type values (with no WIDTH setting) of one or more lines with no more than 4000 bytes for each line. Text values, with WIDTH set, of 1 line with no more than 4000 bytes (See "Values on Each Page".) The TEXT data type corresponds to CHAR and VARCHAR2 data types in the Oracle relational database. TEXT characters are encoded in the database character set |
| NTEXT | None | NTEXT data type values with the same restrictions as TEXT data type values. The NTEXT data type corresponds to NCHAR and NVARCHAR2 data types in the Oracle relational database. However, an NTEXT character is always encoded in UTF8 Unicode. This encoding might be different from the NCHAR character set of the database, which can be UTF16. (See "Text Data Types".) |
| ID | None | ID data type values of one line with no more than eight characters |
| BOOLEAN | BOOL | Logical YES/NO values (valid synonyms are ON/OFF and TRUE/FALSE) |
| DATE | None | Dates between Jan 1, 1000 A.D. and Dec 31, 9999 A.D. |
| DATETIME | None | Dates between Jan 1, 4712 B.C. and Dec 31, 9999 A.D., and times in hours, minutes, and seconds |
The dimensions of the variable. A dimension may be one of the following:
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.
|
Note: The order in which you list the dims of a variable is the default order of the dimensions and behavior of a variety of statements (such as REPORT, and UNRAVEL) and effects how the data for the variable is stored (as discussed in "Effect of Dimension Order on Variable Storage". Also, When you define more than one object with the same dimensions, most operations will work much more efficiently when you list the dimensions in the same order in each definition. |
A partition template object that you have previously defined using a DEFINE PARTITION TEMPLATE statement. In this case, you specify the value using the following syntax.
<partition-template-name<dims>>
The dimensions that you specify for dims must be the same dimensions as those of partition_template.
A named or unnamed composite. In this case, you specify the value using the following syntax.
{SPARSE|composite-name} <sparsedims...>
where:
SPARSE indicates that you want Oracle OLAP to create an unnamed composite and use it when dimensioning the variable. For a discussion of unnamed composites, see "Unnamed Composites".
composite-name is the name of a named composite previously defined using a DEFINE COMPOSITE statement.
sparsedims are the names, separated by commas, of the dimensions for which the named or unnamed composite is created. You must enclose the names of the dimensions in a single set of angle brackets (< >).
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.
External partitions of a variable have the permanence of the variable that they represent.
By default, a top-level internal partition of a variable has the same permanence as the variable that contains it. Specifically, an internal partition of a temporary variable is a temporary partition unless you use the PEMANENT keyword to make it a permanent partition, and an internal 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, an internal subpartition has the same permanence as its parent partition. To indicate different behavior, use either the PERMANENT or TEMP keyword.
(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 as follows: 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.
The name of the partition.
(Default) Indicates that this partition is not a previously defined variable.
Indicates that this partition is a previously defined variable that is a base dimension of concat dimension by which the variable is partitioned.
|
Note: You can only use this keyword when variable is dimensioned by a partition template object that was defined using a DEFINE PARTITION TEMPLATE statement that included the PARTITION BY CONCAT clause. |
The name of the variable that is the external partition.
The name of an attached workspace in which you wish to define the variable. When the variable is dimensioned, it must be defined in the same workspace as its dimensions. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When the session ends, the object no longer exists. This differs from the TEMP keyword, which specifies that the values are temporary but the object definition remains in the workspace in which you create it.
Notes
Theoretically, a variable can contain up to 2**63 cells and a TEXT or NTEXT variable can contain up to 2 billion bytes. However, certain considerations apply when defining large variables, as described in "Values on Each Page".
As with other types of objects, you can set properties on variables with the PROPERTY command. For dimensioned variables the $NATRIGGER and $STORETRIGGERVAL properties have special meaning.
How variable data is stored in an analytic workspace is determined by the following:
Whether or not the variable is dimensioned by a composite or a conjoint dimension.
The order in which the dimensions for the variable are defined.
Whether or not the variable is defined as a partitioned variable.
Whether or not the variable is stored in segments of default size or of an explicit size.
When a variable is dimensioned by regular dimensions, Oracle OLAP creates a cell in the variable for each set of its dimension values. When a cell is empty, then the cell 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 cells are empty. There are two types of sparsity:
Controlled sparsity occurs when a range of one or more dimensions has no data; for example, a new variable dimensioned by month for which you do not have data for past months.
Random sparsity occurs when some combinations of dimension values never have any data. For example, a district might only sell certain products and never have data for other products. Other districts might sell some of those products and other ones, too.
You can reduce the number of empty cells by dimensioning a variable with one or more composites or conjoint dimensions. Composites and conjoint dimensions are lists of dimension value combinations. For variables dimensioned by these objects Oracle OLAP does not create a variable cell for every value in the base dimensions. Instead, it creates cells only for those dimension values that are stored in the list of dimension value combinations of the composite or conjoint dimension. See DEFINE COMPOSITE and DEFINE DIMENSION (conjoint) for more information.
|
Note: Special considerations apply when you dimension a variable by a compressed composite, see "Defining Variables with Compressed Composites" for more information. |
The order in which you list the dimensions in an unpartitioned variable definition determines how the data of that variable is stored and accessed. The first dimension in the variable definition is the fastest-varying dimension, and the last dimension is the slowest-varying dimension.
For example, assume your analytic workspace has an opcosts variable that contains the operating costs, by month, of each city in which you have offices. In the following definition for the opcosts variable, month is the fastest-varying dimension and city is the slowest-varying dimension.
DEFINE opcosts VARIABLE DECIMAL <month city>
The data for a multidimensional variable is stored as a linear stream of values, in which the values of the fastest-varying dimension are clustered together. For example, for the opcosts variable, the values for Boston for all the months are stored in a sequence, and then it stores the values for Chicago for all the months in a sequence, and so on. Thus the month values vary fastest in the opcosts variable, as shown in the following table.
When you define variables and other dimensioned objects, and when you write programs that loop over multidimensional expressions in nested loops, you should always try to maximize performance by matching the fastest-varying dimension with the inner loop.
Each unpartitioned data object is a single row in the table that is an analytic workspace. Variables defined as partitioned variables are stored as multiple rows in the table. Each partition is a single row. Within a partition, the way that the variable's data is stored is determined by the order in which the dimensions for the variable are defined and the type of segments used by the variable.
Within a partition, variable data is stored in segments. A segment is continuous disk space. By default, the segment sizes of a variable are automatically determined by Oracle OLAP. Each segment is the exactly the amount of continuous disk space needed to store all of the values assigned by a single 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.
Pages are the units of storage in a analytic workspace. To calculate the maximum number of values for a variable of a given width that will fit on one page, use the VALSPERPAGE program.
Keep the following points in mind when defining a variable that is dimensioned by a compressed composite:
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.
The partitions of a variable dimensioned by a compressed composite must respect the parent-child relationships of the hierarchical dimensions. When an AGGREGATE command executes, data cannot be aggregated across partitions. To check to see if a variable is partitioned correctly, use the PARTITIONCHECK function.
Examples
Example 10-23 Defining a Variable
This example adds the variable population to a workspace. It is dimensioned by city, which has already been defined in the workspace. The LD Command 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 10-24 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 10-25 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 10-26 Defining a Variable with Internal 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 detail (day) data is in a separate partition and the summary (month and year) data is in yet another 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:
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')-
PARTITION TIME_2002 VALUES -
('2002','Dec2002','Jan2002', 31Dec2002',01Dec2002','31Jan2002','01Jan2002'))
(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 16-50, "Specifying the Values of a Partition Using Valuesets".)
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>>
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 10-27 Defining a Variable with External Partitions
Assume you have an analytic workspace that contains individual sales variables for each years data.
DEFINE year_2003 DIMENSION TEXT DEFINE year_2002 DIMENSION TEXT DEFINE year_2003_PARENTREL RELATION year_2003 <year_2003> DEFINE year_2002_PARENTREL RELATION year_2002 <year_2002> DEFINE sales_2003 VARIABLE DECIMAL <year_2003 product> DEFINE sales_2002 VARIABLE DECIMAL <year_2002 product>
Assume also that you want to logically combine the sales data into a single variable that has sales data for all years. To do this you add the following definitions to the analytic workspace:
A definition for a concat dimension that has the time-related dimensions of sales_2002 and sales_2003 as base dimensions.
DEFINE time DIMENSION CONCAT (year_2003 Year_2002) UNIQUE
A definition for the relation that specifies the child-parent relationship of the values of the time hierarchy.
DEFINE time_parentrel RELATION time <time>
A partition template object that defines the partitions for each year's sales data (that is, sales_2002 and sales_2003).
DEFINE part_temp_sales_by_year PARTITION TEMPLATE <time product> - PARTITION BY CONCAT (time)- (PARTITION partition_2002 <year_2002 product>, - PARTITION partition_2003 <year_2003 product>)
A sales variable with external partitions for sales_2002 and sales_2003.
DEFINE sales DECIMAL <part_temp_sales_by_year<time product>> - (PARTITION partition_2002 EXTERNAL sales_2002,- PARTITION partition_2003 EXTERNAL sales_2003)
When you issue the following REPORT statement you can see the values in the partitions of sales.
REPORT DOWN PARTITION(part_temp_sales_by_year) time product sales PARTITION(PART_TEMP_SALES_BY_YEAR) TIME PRODUCT SALES ----------------------------------- ---------- ---------- ---------- PARTITION_2002 01Jan2002 00001 14.44 PARTITION_2002 31Jan2002 00001 15.55 PARTITION_2002 01Dec2002 00001 11.39 PARTITION_2002 31Dec2002 00001 10.53 PARTITION_2002 Jan2002 00001 29.99 PARTITION_2002 Dec2002 00001 21.92 PARTITION_2002 2002 00001 51.91 PARTITION_2002 01Jan2002 00002 11.03 PARTITION_2002 31Jan2002 00002 12.20 PARTITION_2002 01Dec2002 00002 12.80 PARTITION_2002 31Dec2002 00002 13.77 PARTITION_2002 Jan2002 00002 23.23 PARTITION_2002 Dec2002 00002 26.57 PARTITION_2002 2002 00002 49.80 PARTITION_2003 01Jan2003 00001 10.00 PARTITION_2003 31Jan2003 00001 10.88 PARTITION_2003 01Dec2003 00001 NA PARTITION_2003 31Dec2003 00001 NA PARTITION_2003 Jan2003 00001 20.88 PARTITION_2003 Dec2003 00001 NA PARTITION_2003 2003 00001 NA PARTITION_2003 01Jan2003 00002 15.21 PARTITION_2003 31Jan2003 00002 13.37 PARTITION_2003 01Dec2003 00002 NA PARTITION_2003 31Dec2003 00002 NA PARTITION_2003 Jan2003 00002 28.58 PARTITION_2003 Dec2003 00002 NA PARTITION_2003 2003 00002 NA
Example 10-28 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 10-29 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 will be 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.
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.
Syntax
DEFINE name WORKSHEET [<column-dim row-dim>] [TEMP] [AW workspace] [SESSION]
Arguments
The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.
The object type when you are defining a worksheet.
The names of the dimensions of the worksheet. When you supply this argument, you must give the names of two integer dimensions for column-dim and row-dim. When you omit this argument, the worksheet will be dimensioned automatically by WKSCOL and WKSROW. See "Worksheet Dimensions" for more information
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.
The name of an attached workspace in which you wish to define the worksheet. The worksheet must be defined in the same workspace as its dimensions. For general information about this argument, see the main entry for the DEFINE command.
Specifies that the object exists only in the current session. When the session ends, the object no longer exists. This differs from the TEMP keyword, which specifies that the values are temporary but the object definition remains in the workspace in which you create it.
Notes
A worksheet is always dimensioned by two dimensions that represent a worksheet row and a worksheet column. The worksheet row and a worksheet column dimensions can either be automatically created by Oracle OLAP or explicitly created by you.
When you have not created worksheet row and a worksheet column dimensions and specified their names in the column-dim and row-dimt arguments of DEFINE WORKSHEET, Oracle OLAP automatically creates the following dimensions:
For the worksheet row, an INTEGER dimension named WKSROW with values from 1 to 63.
For the worksheet column, an INTEGER dimension named WKSROW with values from 1 to 63.
|
Note: WhenWKSCOL and WKSROW already exist in any attached workspace, Oracle OLAP cannot create them in the current worksheet. In this case, the DEFINE WORKSHEET command will fail to create a worksheet with these default dimensions. |
WKSCOL and WKSROW do not appear in the worksheet description. (For information on how to create a worksheet description, see DESCRIBE.)
You create worksheet row and a worksheet column dimensions the same way you create any other simple dimension by issuing the following statements:
Create two simple INTEGER dimensions using a DEFINE DIMENSION (simple) statement.
Populate the dimensions with the number of rows and columns that you want in your worksheet using a MAINTAIN statement.
When you import a file that requires more cells than are available, the worksheet dimensions are maintained automatically. For this reason, you should avoid using the worksheet dimensions for other types of objects.
You can also add or delete values from worksheet row and a worksheet column dimensions with the MAINTAIN command, which changes the number of cells in the worksheet.
Examples
Example 10-30 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 10-31 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)
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|