| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
|
|
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 Oracle 9i and Oracle 10g.
In Oracle 10g, the following changes were made to the Oracle OLAP DML:
|
See also: "OLAP DML Statement Changes for Oracle 10g" and "OLAP DML Statement Changes for Oracle 9i" identify, by name, the OLAP DML statements that were added, changed, renamed, or deleted in various releases of Oracle 9i and Oracle 10g. |
In Oracle 10g, 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 Oracle 10g, new features have been added, some default values have changed, and old functionality has been deprecated.
In Oracle 10g, 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 Oracle 10g, 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 Oracle 10g, within an aggregation specification, you can access special Oracle OLAP functionality called the Aggregate Advisor. Using the Aggregate Advisor you can request that Oracle OLAP 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 Oracle 10g, 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.
The 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 Oracle 10g 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 Oracle 10g Release 1, define your aggregations using aggmap objects as discussed in "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 Oracle 10g, 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.
You can specify a default allocation specification for a variable.
An analytic workspace is a table of LOBs with each analytic workspace object one or more rows in the table. In Oracle 10g, 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, PARTITIONCHECK |
In Oracle 10g, 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 dimension |
In Oracle 10g, enhancements have been made to allow you to create applications in multiple languages using a language dimension.
In Oracle 10g, 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 Oracle 10g, 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.
In Oracle 10g, 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.
You 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.
You 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.
In Oracle 10g, 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_SORTS |
Oracle 10g also includes the following OLAP DML features:
New attachment program
In Oracle 10g, 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.
Improved error handling when reading files
In Oracle 10g, you can specify the behavior of Oracle OLAP when an error is reached when reading from a file using an INFILE statement.
Additional support for the OLAP_TABLE function used in SQL
In Oracle 10g, 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.
Extended support for embedded SQL
In Oracle 10g, you can select data from relational tables into analytic workspace objects without using an explicit cursor.
New SQL-like text and numerical functions
In Oracle 10g, 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 Oracle 10g.
The following statements have been added to the OLAP DML in Oracle 10g. 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 Oracle 10g. The number in parentheses indicates the specific release in which the statement was deleted.
The following OLAP DML statements were significantly changed in Oracle 10g. 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 Oracle 10g.
This section contains listings of the OLAP DML statement changes in Oracle 9i.
The following statements were added to the OLAP DML in Oracle 9i. 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 Oracle 9i. The number in parentheses indicates the specific release in which the statement was deleted.
The following OLAP DML statements were significantly changed in Oracle 9i 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 Oracle 9i" for a list of renamed statements.
The following OLAP DML statements were renamed in Oracle 9i. The number in parentheses indicates the specific release in which the statement was renamed.
