Overview
Oracle OLAP Option is the multidimensional engine embedded in Oracle Database. It includes a powerful dimensional data model that is based on the industry leading OLAP technology, Express. This powerful environment It is known for its rich anallytic capabilities and its excellent query response times – and all Oracle OLAP data is accessible through SQL. Because the Oracle OLAP Option is a native part of Oracle Database, it leverage's the scalable, secure and resilient platform of the Oracle Database. If you have the Enterprise Edition then you have the OLAP option available.

Note : This is a paid option. Verify with your Oracle representative whether or not you can legally use it.

This page covers the following product versions:

• Oracle Database OLAP Option
  • 11g
  • 10gR1 and 10gR2
  • 9iR2
  • 9iR1
• Express Server
  • 6.3
  • 6.2
  • 5.0
  • 4.8

Versions

11gR1 - Key Features

The OLAP Option to Oracle Database 11g continues the development trends of Oracle9i and Oracle Database 10g, especially in deepening integration with the database and enhancing SQL access to cubes, security, and metadata. The power of OLAP is easily accessible to SQL applications. Oracle Database 11g also introduces the cube as a summary management solution for relational OLAP (ROLAP) implementations.

OLAP Metadata Integration
All metadata for cubes and dimensions is stored in the Oracle database and revealed in the data dictionary views, so that you can query the entire business model in SQL. Use of the data dictionary to store the metadata officially codifies the dimensional model in the database, provides significant improvements for metadata queries, and supports other new features such as SQL object security for cubes and dimensions.

Automatic Maintenance of Cube and Dimension Views
Oracle Database 11g automatically creates and maintains relational views for every cube, dimension, and hierarchy in the database. If you modify a dimensional object, such as adding a calculated measure to a cube, the view is immediately re-created to reflect the change. Oracle Database defines these views using the new CUBE_TABLE function, which enables the SQL Optimizer enhancements.

Cube Scripts
A cube script is an ordered list of commands that prepare a cube for querying, such as Clear Data, Load Data, Aggregate, Execute PL/SQL, and Execute OLAP DML. For many applications, cube scripts will eliminate the need to use procedural programs for processing cubes.

Cost-Based Aggregation
Fast updates and uniform querying performance are two hallmarks of the OLAP option. Cost-based aggregation enhances performance in both areas by executing a fine-grained pre-aggregation strategy and storing sparse data sets very efficiently.

Calculation Expression Syntax
OLAP calculation expressions extend the syntax of the SQL analytic functions. This syntax is already familiar to SQL developers and DBAs, so that it is easier for them to adopt than proprietary OLAP languages and APIs. This syntax is used to define calculations that are embedded in the cube, such as dynamically calculated facts or measures.

Cube Materialized Views
Cube materialized views are cubes that have been enhanced to use the automatic refresh and query rewrite features of Oracle Database.

Cube materialized views bring the fast update and fast query capabilities of the OLAP option to applications that query detail relational tables. Summary data is generated and stored in a cube, and query rewrite automatically redirects queries to the cube materialized views. Applications experience excellent query performance.

Object and Data Security
Oracle Database 11g introduces both object security and data security to OLAP cubes and dimensions. Both types of security are granted to database users and roles. Object security controls access to analytic workspaces, cubes, and dimensions using standard SQL GRANT and REVOKE syntax. Data security controls access to the data in a cube or a dimension. You can grant SELECT, INSERT, UPDATE, and DELETE privileges to dimension members (keys) either globally or in the context of a particular cube to control access to the data in a cube.

10gR1/10gR2 - Key Features

Oracle Database offers the industry's first and only embedded OLAP server. Oracle OLAP provides native multidimensional storage and speed-of-thought response times when analyzing data across multiple dimensions. The database provides rich support for analytics such as time series calculations, forecasting, advanced aggregation with additive and nonadditive operators, and allocation operators. These capabilities make the Oracle database a complete analytical platform, capable of supporting the entire spectrum of business intelligence and advanced analytical applications.

Enhanced Support for Very Large Multi-dimensional datasets
Oracle Database 10g brings cube compression technology, parallelism and and partitioning to multidimensional data sets. Cube compression technology optimises aggregation and storage of multidimensional data types. Partitioning and parallelism allow for more efficient management of warehouses with large multidimensional data sets.

Compress cube technology
Oracle database 10gR1 introduced patented compressed cube technology to optimise aggregation, storage and query of sparse data sets, which are typical of OLAP implementations. A sparse data set is one where relatively few data points in the dimensional model have non-null values as compared to the model as a whole. Results of cube compression can be dramatic. For one customer data set, which is reasonably typical of a sales and marketing data set, both the amount of time required to prepare the data set for query and the size of the cube were significantly reduced.

Comparison of time take to full aggregate a cube with 9i and 10g

Comparison of cube size between 9i and 10g

In 10gR2 compressed cubes are further enhanced with dynamic aggregation and support for additional aggregation methods. From the previous example, the move to 10gR2 produced a 33% improvement in aggregation times, as shown below

Partitioned cubes
Automatic partitioning and parallel processing of data loads and aggregations were introduced in 10gR1. Partitioning for uncompressed cubes was a key new feature for 10gR1. The partition template defined the partition strategy within the multidimensional engine. The most commonly used partitioning strategies are list and range partitioning.

In 10gR2 compressed partitioned cubes were supported.


Partitioned AW$ table
Oracle 10gR1 introduced the ability to partition the AW$ table used to store the analytic workspace.

Parallel processing
Parallel processing of cubes is available both within the multidimensional engine and the Java API for Analytic Workspaces. AWM allows for the specification of the number of parallel processes that may be used within a maintenance procedure.

Incremental Updates
Both the Java API for Analytic Workspaces and the multidimensional engine each provide support for incremental updates of cubes and measures. All support for incremental update is automatic. In 10gR2 certain restrictions regarding the use of this feature were lifted and incremental updates could be applied to both compressed and non-compressed cubes, regardless of whether the cube is partitioned.

Enhanced APIs and Management Tools
Both Warehouse Builder and Analytic Workspace Manager are enhanced to provide UI to support the new features in 10gR2. Warehouse Builder is able to support the direct design and deployment of multidimensional models.

SQL Interface to multi-dimensional data types
The OLAP Option for 10gR2 provides both an OLAP API and a SQL interface for query access to multidimensional data types. The purpose of the OLAP API is to provide a dimensionally aware context based API that is supported by Discoverer, BI Spreadsheet Addin, OLAP Plugin for Reports and Business Intelligence Beans.

The SQL interface provides a SQL based applications with query access to data stored and calculated in an analytic workspace. The SQL based application might have no awareness of the dimensional model.

Multiwriter Support
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.

Aggregation Enhancements
In Oracle10g, new features have been added, some default values have changed, and old functionality has been deprecated.

Compressed Composites
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.

Aggregation Advisor
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.

Additional Aggregation Enhancements
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.

Allocation Enhancements
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.
• You can specify a default allocation specification for a variable.

Partitioned Variable Support
An 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.

Custom Member Enhancements
In 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.

Enhanced Language Dimensions
In Oracle10g, enhancements have been made to allow you to create applications in multiple languages using a language dimension.

Related Dimensions and Relations
In 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.

Object Event Support
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.

LIMIT and QDR Enhancements
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.

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.

You can compare the status lists of dimensions, valuesets, or LIMIT and SORT functions for equality..

Enhanced Sorting and Ranking Functionality
In 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.

Additional Features
Oracle10g 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.

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

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.

In Oracle10g, you can select data from relational tables into analytic workspace objects without using an explicit cursor.

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.

9iR2 - Key Features
Oracle9i Release 2 provides multidimensional analysis within the Oracle database. Oracle OLAP is the next generation of analytical engines and related software, providing an upgrade path from Oracle Express Server release 6.3.

• The OLAP engine runs in the Oracle kernel, and analytic workspaces are stored as LOBs in relational tables. See Also: "The Oracle9i Integrated Relational-Multidimensional Database"
• Oracle OLAP management tools are integrated with Oracle
  • The Oracle DBA uses one set of management tools for both the Oracle database and Oracle OLAP. See Also:Chapter 6, "Administering Oracle OLAP"
• SQL applications can access multidimensional data
  • SQL applications can use the database table functions to access and manipulate data directly in the multidimensional OLAP data cache. Alternatively, relational views can be created for multidimensional data, which provides access to standard SQL. See Also: Chapter 3, "Developing OLAP Applications"
• Tools simplify creation of analytic workspaces and related views
  • Tools are available to help move data from relational tables into multidimensional objects in an analytic workspace, and to generate views of these objects so that applications can access workspace data using standard SQL. See Also: Chapter 9, "Creating an Analytic Workspace From Relational Tables" and Chapter 15, "CWM2_OLAP_AW_ACCESS"
• Applications can use OCI or JDBC to connect to Oracle OLAP
  • OLAP applications that used SNAPI communications in Express Server 6.3 and earlier can upgrade to Oracle OLAP without substantially changing the application's Express language-based architecture. See Also: Chapter 1, "Overview"
• OLAP API is available for developing Java applications
  • The Oracle OLAP API is an all-Java application programming interface that is designed specifically to support multidimensional analysis. See Also: Chapter 3, "Developing OLAP Applications"
• OLAP Catalog API supports third-party applications development
  • PL/SQL interfaces to the OLAP Catalog allow developers to query and update the logical multidimensional metadata model and map it to physical relational and analytic workspace data. See Also: Chapter 5, "Creating OLAP Catalog Metadata"
• OLAP metadata provides extended schema support
  • The Oracle OLAP Catalog metadata supports star, snowflake, and multidimensional schema. The metadata supports level-based, parent-child, and complex dimension hierarchies. See Also: Chapter 5, "Creating OLAP Catalog Metadata"
• Oracle Globalization Support extended to Oracle OLAP
  • Oracle Globalization Support provides the Oracle standard for internationalizing and localizing Oracle products. The character set encoding supports Unicode using the UTF-8 standard, which is a format that transforms all Unicode characters into a variable-length encoding of bytes. Its use in the database and Oracle OLAP allows text data in native languages to be passed between them without data loss or performance degradation.
    

9iR1 - Key Features

Oracle9i OLAP supports business intelligence. Analytical queries require an online analytical processing (OLAP) solution. Oracle provides a comprehensive multi-dimensional model

Support for OLAP:

• The Oracle relational database management system (RDBMS) is an efficient and secure way to store vast amounts of data. Within the Oracle database, you can create a data warehouse that provides data in a form suitable for business analysis.
• OLAP Services provides a Java OLAP API, a calculation engine, and an analytic workspace. These facilities enable you to build analytical applications that support complex statistical, mathematical, and financial calculations along with predictive analysis functions such as forecasting, modeling, consolidations, allocations, and scenario management. Because the OLAP API is all Java, OLAP Services supports deployment of analytical applications to large, geographically distributed user communities on the Internet.
• The Oracle BI Beans complements OLAP Services by providing pre-built OLAP-aware application building blocks. The BI Beans can be used within Oracle JDeveloper or other Java development environments to build analytical applications, which can be deployed as either Java or HTML ("thin") clients.

Complementary functionality
The Oracle RDBMS and OLAP Services provide complementary functionality to support the most versatile and high performance applications. The RDBMS provides detail data, summary management, and one-dimensional calculations using the SQL-99 OLAP extensions. OLAP Services expands these capabilities to provide forecasting, modeling, what-if scenarios, and multidimensional calculations.

Java programming language
Java is the language of the Internet, and also the language of OLAP applications. Using Java, an application developer can write a standalone application or an applet, which is a program that can be included in an HTML page and executed in a browser. As an object-oriented, platform-independent, network-based, and secure language, Java is fast superseding C++ and Visual Basic as the language of choice for application developers:

• Object oriented. Java allows application developers to focus on the data and methods of manipulating that data, rather than on abstract procedures; the programmer defines the desired object rather than the steps needed to create that object. Almost everything in Java is defined as an object.
• Platform independent. The Java compiler creates byte code that is interpreted at runtime by the Java Virtual Machine (JVM). As the result, the same software can run on all Windows, Unix, and Macintosh platforms where the JVM has been installed. All major browsers have the JVM built in.
• Network based. Java was designed to work over a network, which allows Java programs to handle remote resources as easily as local resources.
• Secure. Java code is either trusted or untrusted, and access to system resources is determined by this characteristic. Local code is trusted to have full access to system resources, but downloaded remote code (that is, an applet) is not trusted.

The Java "sandbox" security model provides a very restricted environment for untrusted code. For example, untrusted Java code cannot read to or write from files on the local file system, run programs, load libraries, define native method calls, or make network connections except to the originating host computer. A security manager determines the system resources that an applet can access. However, a signed applet, which identifies itself as being from a trusted source, has full access to system resources the same as local code.

JavaBeans for business intelligence
JavaBeans are the building blocks of application development. They are reusable pieces of Java code that can be assembled quickly into an application. The BI Beans provide the basic building blocks for an OLAP application: Connecting to a database; authenticating user credentials; selecting and fetching data; and displaying the data in a variety of tabular and graphical formats. Using the BI Beans, you can create applications with a common "look and feel," enabling users to gain expertise quickly in the new product.

The Oracle OLAP API
The Oracle OLAP API is a Java application programming interface to OLAP Services. It is a querying language that selects and manipulates data in a data warehouse for display in a Java client. The analytical BI Beans are built using this API; you can extend (or even replace) the functionality provided by the BI Beans by using Java classes.

The Oracle OLAP DML
The Oracle OLAP DML is a data manipulation language that extends the analytic support of the OLAP API to include forecasting, modeling, and what-if scenarios. It operates on data that is stored (permanently or temporarily) in multidimensional objects in the analytic workspace.

Managing OLAP Services
In its broadest definition, OLAP Services consists of the following components: the Oracle Java OLAP API, one or more OLAP services that run as child processes of a database instance, a metadata repository in each database instance, and tools within Oracle Enterprise Manager for creating OLAP metadata and managing OLAP services. However, OLAP Services is frequently used in this guide to refer to one or more components (such as an OLAP service child process) where distinctions among software components are not necessary.

Integrated tools
All of the tools that you use to manage OLAP services are integrated into Oracle Enterprise Manager. You do not need to use a separate set of tools.

Instance management
OLAP Services Instance Manager is the primary tool for managing OLAP services. You can use Instance Manager to do these system administration tasks:

• Start and stop existing services, and create additional services.
• Monitor user sessions.
• Add and remove optional features so that you can use the features you want using the fewest resources.
• Fine-tune its operating parameters so that OLAP services perform optimally in your unique data processing environment.

Metadata definition
The OLAP component of Oracle Enterprise Manager generates metadata for use by applications. This metadata is used to identify the facts available for analysis, and to create and populate multidimensional objects. These objects are needed to process analytical queries using data stored permanently in an Oracle data warehouse.

OLAP metadata
Metadata is typically defined as "data about data." OLAP applications can query the metadata repository to find out what data is available for them to analyze and display. While the data is stored in tables in an Oracle data warehouse, the metadata identifies the data in terms of the multidimensional objects they will become in OLAP Services: dimensions, measures, and attributes. Moreover, the metadata defines the dimension hierarchies required to aggregate the data into meaningful levels of detail.

Language support
Globalization Technology provides the Oracle standard for internationalizing and localizing Oracle products. Its use throughout Oracle9i allows text data in native languages to be passed between the RDBMS and OLAP Services without data loss or performance degradation.

Security methods
The metadata maps OLAP Services security directly to the security of the RDBMS. Users can access through OLAP Services only the data that they are authorized to access in the database. Users can provide proof of identity in the following ways:

• A database user ID and password that will be validated against either:
  • The local user repository in the associated RDBMS.
  • An Oracle Internet Directory, which is a Lightweight Directory Access Protocol (LDAP) Version 3 service.
• A client-side X.509 certificate that is sent over a Secure Sockets Layer (SSL) connection for single sign-on for enterprise users. The certificate will be validated against an Oracle Internet Directory within the Public Key Infrastructure internet security strategy.

Express Server 6.3 - Key Features
Product enhancements in release 6.3.4 (which is primarily a maintenance release) include:

• Express Server release 6.3.4 is primarily a maintenance release. One significant change is that any SEGWIDTH specification that you have applied to a variable is now retained after export/import.

Product enhancements in release 6.3.2.1 (which is primarily a maintenance release) include:

• HAverage, HFirst, HLast, and HWAverage non-additive AGGREGATE functions. These functions are documented under the RELATION command in the Express Language Help.
• Ability to use a value set to limit hierarchy dimensions, or to specify which values to calculate ahead of time with AGGREGATE and which values to recalculate at execution time.See the RELATE command in Express Language Help.
• The ability to use a relation as a weight object. See the AGGREGATE command in the Express Language Help.
• Use of Oracle Required Support Files version 8.1.7, which allows Express Server 6.3.2.1B to be installed in an Oracle 8.1.7 home directory.
• An increase to the maximum size of Express Server's ranspace from 2 GB per object to 4 GB per object.You can use ranspace to store BTREE information for composite dimensions. The higher maximum may eliminate RSALLOC errors that occur when very large composites lead to large aggregating data dimensions.

Product enhancements in release 6.3.1 include:

• Support for non-additive aggregation methods using the AGGREGATE command.
• Block fetch from Oracle Database using SQL command.
• ODBC Data Direct version 3.6. (Express Server only).
• Use of Oracle Required Support Files version 8.1.6 which allows Express Server 6.3.2 to be installed in an Oracle 8.1.6 home directory
• Oracle Universal Installer version 1.7.

Product enhancements in release 6.3.0 include:

• General performance improvements.
• Reduced growth of database size when one writer and many readers are working with the same databases, as is often the case in applications such as Oracle Financial Analyzer.
• Support for distributing paging files (EXPTEMP files) across multiple drives that helps to eliminate I/O bottlenecks on paging files. This is important when applications allow users to change data values (for example, what-if and budgeting applications) and when Express Relational Access Manager is used to access data in a relational database. (Express Server only)
• New statistical commands, including CATEGORIZE, CORRELATION, PERCENTAGE, SMOOTH, RANK, and MODE.
• A new high performance aggregate management system. You can use the AGGREGRATE command to presummarize and store data and to calculate summary level data on the fly.

• A new forecasting system that provides options (see the FCEXEC, FCQUERY, FCSET, FCOPEN, and FCCLOSE commands in Express Language Help).
• NATRIGGER property that provides a more efficient method to execute formulas or programs when a data point has an NA value (see the PROPERTY command in Express Language Help).

• EIF file partitioning that enables export of more than 2 GB of data with a single EXPORT command.
• UNIX SNAPI (clients) that allows UNIX services and applications to act as Express Server clients. (Express Server Only)
• Support for UNIX MODE file permissions that eliminates the need to use access control lists (ACLs). (Support for ACLs is available as an option.)
• Support for CSV files with the IMPORT (to Worksheet) command (see the IMPORT (WKS) command in Express Language Help).
• Express Instance Manager which replaces Express Service Manager, Express Configuration Manager, and Express Session Manager. (Express Server only)
• Relational Access Manager enhancements (Express Server only), including:
• Significant performance improvements to build/update processing, particularly in the case of complex Express data models
• Support for processing only changed dimension values during a general maintenance procedure (build/update)

Express Server 6.2 - Key Features


Express Server 5.0 - Key Features


Express Server 4.8 - Key Features