| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-03 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-03 |
|
|
View PDF |
This section identifies the new features of the Oracle Database 10g that relate to the OLAP DML and lists the Oracle OLAP DML statements that were added, changed, renamed, or deleted in Oracle9i and Oracle10g.
In Oracle10g, the following changes were made to the Oracle OLAP DML:
See also:
"OLAP DML Statement Changes for Oracle10g" and "OLAP DML Statement Changes for Oracle9i" identify, by name, the OLAP DML statements that were added, changed, renamed, or deleted in various releases of Oracle9i and Oracle10g.In Oracle10g, you can attach an analytic workspace in multiwriter access mode. A workspace that is attached in multiwriter mode can be accessed simultaneously by several sessions and users can simultaneously modify the same analytic workspace in a controlled manner by specifying the attachment mode for individual analytic workspace objects.
In Oracle10g, new features have been added, some default values have changed, and old functionality has been deprecated.
In Oracle10g, you can use compressed composites to create variables with the fewest stored aggregated values. Using compressed composites improves performance and decreases the space needed to store aggregate values. A compressed composite contains one composite tuple for each set of base dimension values that identifies non-NA detail data in the variables that use it. Additionally, for variables dimensioned by compressed composite Oracle OLAP reduces redundancy in the variable, composite, and composite index by creating a physical position in the composite only for those tuples that represent a parent with more than one descendant
Oracle OLAP compresses the data in variables dimensioned by compressed composites using the "intelligence" of the AGGREGATE command or AGGREGATE function. In Oracle10g, though there are still some special considerations that apply when aggregating a variable dimensioned by one or more compressed composites, these considerations are less restrictive than in earlier releases.
In Oracle10g, within an aggregation specification, you can access special Oracle OLAP functionality called the Aggregate Advisor which is described in detal in the Oracle OLAP Reference. Within an OLAP DML aggregation specification, you can request that Oracle OLAP use the Aggregate Advisor to determine the values that are aggregated as a database maintenance procedure and those aggregated on-the-fly to give the best performance and storage.
In Oracle10g, the new features have also improved the aggregation capabilities of Oracle OLAP:
You can perform hierarchical and non-hierarchical aggregation across data objects.
You can specify computation based on the position of dimension values and hierarchical level.
You can specify default an aggregation specification for a variables.
See:
$AGGMAP propertyThe default values for the CACHE statement in an aggregation specification has changed.
The the following aggregation statements and parameters were deprecated:
The ROLLUP statement was deprecated as of Oracle10g Release 1 (10.1.0.3). Although the ROLLUP statement offered a way of performing simple additive aggregation through a single OLAP DML statement. However, ROLLUP was not compatible with many new features (for example, aggmap objects and compressed composites). Beginning with Oracle10g Release 1, define your aggregations using aggmap objects as discussed in Chapter 4, "Aggregations".
For the AGGREGATE command and the AGGREGATE function:
The WAGG and WNOAGG weighted operators have been deprecated. WPREAGG is now the default for all weighted operators.
The WAGG and WNOAGG weighted operators have been deprecated. WPREAGG is now the default for all weighted operators.
Arbitrary LIMIT syntax in PRECOMPUTE statements has been deprecated.
For example, the following syntax is not supported:
PRECOMPUTE(limit(b to first 5))
Instead, (1) define a valueset, (2) define an aggmap object that uses the valueset that you defined in step 1, (3) assign values to the valueset that you defined in step 1, and then (3) execute an AGGREGATE statement using the aggmap that you defined in step 2. For example:
DEFINE time.precomp VALUESET time
DEFINE myaggmap AGGMAP
AGGMAP
RELATION time.parentrel PRECOMPUTE(time.precomp)
END
LIMIT time.precomp TO FIRST 5
AGGREGATE myvar USING myaggmap
In previous releases, the syntax for the ANY, AVERAGE, COUNT, EVERY, LARGEST, MEDIAN, NONE, SMALLEST, STDDEV, TALLY, and TOTAL functions included a STATUS keyword that specified the status of a temporary variable sometimes used by these functions. Improvements negated the need for a temporary variable and, consequently, the STATUS keyword in these functions has been deprecated.
In Oracle10g, there are new features that improve the allocation capabilities or Oracle OLAP:
You can allocate data to variable dimensioned by a non-hierarchical dimension such as a measure or line dimension.
See:
VALUESETYou can specify a default allocation specification for a variable.
See:
$ALLOCMAPAn analytic workspace is a table of LOBs with each analytic workspace object one or more rows in the table. In Oracle10g, you can explicitly specify which parts of a variable you want to be in a row or LOB by defining a partitioned variable—each partition is a row or LOB. Once you have defined the partitions of a variable, you can maintain these partitions explicitly.
See:
DEFINE PARTITION TEMPLATE, DEFINE VARIABLE, MAINTAIN ADD TO PARTITION, MAINTAIN MOVE TO PARTITION, PARTITIONCHECKIn Oracle10g, you can add and delete one or more temporary calculated members (sometimes called custom members) to a dimension and apply those members to a variable; or apply a previously-defined calculated member to the a variable. You can test to see if a dimension value is a custom member. Also, you can limit a dimension to custom member values.
See:
MAINTAIN ADD SESSION, ISSESSION, the SESSION keyword of the LIMIT command (using values) and MAINTAIN DELETE dimensionIn Oracle10g, enhancements have been made to allow you to create applications in multiple languages using a language dimension.
See:
"Working with Language Dimensions", $DEFAULT_LANGUAGE, LOCK_LANGUAGE_DIMS, SESSION_NLS_LANGUAGE, and STATIC_SESSION_LANGUAGEIn Oracle10g, enhancements have been made for working with related dimensions and relations:
When two dimensions share more than one relation, you can explicitly specify a default relation that Oracle OLAP uses when performing calculations based on related dimensions.
When limiting based on the values of a related dimension, you can explicitly specify the relation by which to perform the limit; and, if the relation is a multidimensional relation, you can specify what values of the relation to use.
In Oracle10g, you can specify that the DEFINE, MAINTAIN, PROPERTY, SET (=) UPDATE, and AW commands are events that trigger the execution of previously-created OLAP DML programs.
See:
"Trigger Programs"In Oracle10g, the following changes have been made for managing dimension status:
You can order the results of a LIMIT based on the order of the selection arguments rather than the current status order. Additionally, you can LIMIT to values of custom members; and, when you limit using a parent relation, you can specify that you only want the top or the bottom members of a hierarchy in status.
There is additional support for using CHGDIMS to manage dimension status.
There is support for specifying dimension status within the syntax of the RANK function.
There is support for nonexistent values in LIMIT statements and QDRs.
See:
LIMITSTRICTYou can set the current status list of one or more base dimensions of a composite, conjoint dimension, concat dimension, or a partition template based on the selected values of that object. You can also assign a value to one or more valuesets for the base dimensions.
See::
LIMIT BASEDIMSYou can identify how many times the status of a dimension has changed and explicitly identify a previous status list for which you want to retrieve values.
You can compare the status lists of dimensions, valuesets, or LIMIT and SORT functions for equality..
See::
STATEQUAL functionIn Oracle10g, the LIMIT and SORT commands were modified to allow you to sort by hierarchy and to specify whether NA values appear first or list in the sorted list. A SORT function was added with which you can retrieve the sorted values. Additionally, the performance of the RANK function has been enhanced and various options have been added to let you monitor its performance.
See:
SORT command, SORT function, LIMIT command (with SORT keyword), RANK, RANK_CALLS, RANK_CELLS, and RANK_SORTSOracle10g also includes the following OLAP DML features:
New attachment program
In Oracle10g, you can create a new attachment program named ONATTACH that will execute before any other attachment program. Frequently, this program is used to specify the attachment mode for individual analytic workspace objects when an analytic workspace is attached in multiwriter mode.
See:
ONATTACHImproved error handling when reading files
In Oracle10g, you can specify the behavior of Oracle OLAP when an error is reached when reading from a file using an INFILE statement.
See:
INF_STOP_ON_ERRORAdditional support for the OLAP_TABLE function used in SQL
In Oracle10g, you can set an upper limit on the size of a data block generated by a FETCH statement specified in the OLAP_command parameter of the OLAP_TABLE function in SQL.
See:
MAXFETCHExtended support for embedded SQL
In Oracle10g, you can select data from relational tables into analytic workspace objects without using an explicit cursor.
See:
SQL SELECTNew SQL-like text and numerical functions
In Oracle10g, a number of functions that are familiar to SQL programmers were added to the OLAP DML.
Change in default display of long error messages.
Beginning in 10.2, by default, long error messages are not wrapped when displayed. When you want the previous default behavior which is to display long error messages as multiple lines with each line being 72 characters in length, set the WRAPERRORS option to YES.
Additional keywords in the AW function and OBJ function that let you retrieve more information about Oracle OLAP and your analytic workspace.
Additional support for using the current status list rather than the default status list.
By including the new STATUS keyword you can compute the depreciation expenses for a series of assets, the interest portion of the payments on a series of loans, or a payment schedule for paying off a series of installment loans using based on the current status list.
This section contains listings of the OLAP DML statements that were added, changed, renamed, or deleted in Oracle10g.
The following statements have been added to the OLAP DML in Oracle10g. The number in parentheses indicates the specific release in which the statement was added.
The following statements have been deleted from the OLAP DML in Oracle10g. The number in parentheses indicates the specific release in which the statement was deleted.
The following OLAP DML statements were significantly changed in Oracle10g. Examples of significant changes are the addition of a new keyword or a change in a default value. The number in parentheses indicates the last release in which the statement was significantly changed.
No OLAP DML statements have been renamed in Oracle10g.
This section contains listings of the OLAP DML statement changes in Oracle9i.
The following statements were added to the OLAP DML in Oracle9i. The number in parentheses indicates the specific release in which the statement was added.
The following statements have been deleted from the OLAP DML in Oracle9i. The number in parentheses indicates the specific release in which the statement was deleted.
The following OLAP DML statements were significantly changed in Oracle9i and have not changed since then. Examples of significant changes are the addition of a new keyword or a change in a default value. The number in parentheses indicates the last release in which the statement was significantly changed. See also "Statements Renamed in Oracle9i" for a list of renamed statements.
The following OLAP DML statements were renamed in Oracle9i. The number in parentheses indicates the specific release in which the statement was renamed.
