Using OLAP_TABLE for Direct Access to Workspace Data - OLAP

The OLAP_TABLE function provides the basic technology for extracting data from an analytic workspace. All of the views of analytic workspaces that are generated by the enablers use the OLAP_TABLE function. By using OLAP_TABLE directly, you have full control over data access. You can develop your own views to support applications for which there are no enablers, and you can extract workspace data directly into your application. This capability can provide your application with tremendous flexibility, since user queries can be formulated into calls to OLAP_TABLE at runtime.

While the OLAP tools that use the OLAP_TABLE function require a standard form analytic workspace, the OLAP_TABLE function itself does not use standard form metadata.

Designing Views of an Analytic Workspace

The number of views that you create, and the number and characteristics of the columns in these views, depends largely on the requirements of the applications that these views are designed to support.
Because analytic workspaces contain aggregate data, the views must include the aggregates. There are several formats for presenting aggregate data:

  • Create a star schema with dimension views and measure views. The dimension views list dimension members at all levels in a single column.
  • Create a view that includes columns for all of the dimensions, attributes, and measures.
  • Create a view in rollup form that shows the full parentage of each dimension member in multiple columns.
  • Create a separate table for each aggregation level.
    Choose a format that is appropriate for your application and its metadata.

Process Overview

These are the basic steps you must follow to generate views of data stored in an analytic workspace.

  1. Explore the analytic workspace and identify the variables, formulas, relations, and dimensions that you want to expose to your application.
  2. Decide how you want to present these objects in relational tables or views, based on the requirements of the application that will use them.
  3. For each table or view that you plan to create, issue a SELECT statement using the OLAP_TABLE function. The SELECT statement can be an argument to a CREATE VIEW statement.
  4. Commit these changes to the database if you are creating views for general use.
  5. Create whatever metadata is required by your application to query the views.


You use the OLAP_TABLE function in a SQL SELECT statement to query the multidimensional data stored in an analytic workspace. OLAP_TABLE can be used wherever you would use the name of a table or view. You can use SELECT statements to create views, or to fetch data directly from an analytic workspace into an application.
OLAP_TABLE returns a table of objects that can be joined to relational tables and views, or to other tables of objects populated by OLAP_TABLE. It can also return stored workspace data, or it can perform calculations on stored data and return the results of the calculations.

Example is a template that you can use as the starting point for the SQL scripts that you will develop for extracting data from your analytic workspace. You can then execute the script with the @ command in SQL*Plus.

Template for Using OLAP_TABLE

Using the SELECT MODEL Clause

When used in a SELECT statement that queries OLAP_TABLE, the MODEL clause is an optimization that results in significantly faster response time. It can be used only when creating a table type with embedded total dimensions, such as the views used by the BI Beans and the OLAP API.

Note that while the MODEL clause is used in relational queries for inter-row calculations, you should not use it for this purpose with OLAP_TABLE. For OLAP_TABLE, the MODEL clause is used only to optimize the query. When used in a SELECT statement that queries an analytic workspace, MODEL has the following arguments.


The names of the embedded total dimension columns, as defined in the limit map. For BI Beans applications, include the GID columns in this list.
Any other columns in the DIMENSION BY list disables this optimization. A properly constructed SELECT statement still executes, but more slowly.


The measures, attributes, R2Cs, and any other columns excluded from the DIMENSION BY list.


The RULES clause is required, but it should not include complex or inter-row calculations since they will slow the query. Any calculations specified in the RULES clause are performed by SQL. If you want to perform inter-row calculations, you can create a custom measure in the analytic workspace using any of the alternative methods discussed in this chapter, including the limit map of OLAP_TABLE.

UPDATE indicates that you are not adding any custom members in the DIMENSION BY clause. Be sure to include this keyword, because otherwise the SQL WHERE clauses for measures are discarded, which can significantly degrade performance. SEQUENTIAL ORDER prevents Oracle from evaluating the rules to ascertain their dependencies.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

OLAP Topics