Overview of SQL Access - OLAP

Using SQL, you can manipulate analytic workspace data and extract that data into your application. There are various methods that you can use, and the best one depends on the type of analytic workspace you have, the particular task you want to accomplish, and your personal preferences.

Manipulating Analytic Workspace Data

To manipulate analytic workspace data using SQL, you must use PL/SQL procedures that execute OLAP DML commands. The OLAP DML is the language for working in an analytic workspace. Using it, you can create, modify, delete, and Overview of SQL Access populate workspace objects. Any method that you use for performing these tasks uses the OLAP DML.

Several PL/SQL packages are available that execute OLAP DML commands. A call to a single procedure can execute a single OLAP DML command, or dozens of commands to perform a specific task. Among these packages are:

  • DBMS_AW contains procedures for executing individual OLAP DML commands.
  • DBMS_AW_UTILITIES contains procedures for managing custom measures in standard form analytic workspaces that have been enabled for the BI Beans.
  • DBMS_AWM contains procedures for creating standard form analytic workspaces.

You can use any of these packages directly in a SQL interface such as SQL*PLus. Analytic Workspace Manager and OLAP Worksheet are applications that use these SQL packages. Figure shows the relationships among them.

Analytic Workspace Manager's Use of PL/SQL Packages

Analytic Workspace Manager's Use of PL/SQL Packages

Querying an Analytic Workspace

The OLAP_TABLE function provides the basic technology for querying an analytic workspace, as described in "Using OLAP_TABLE for Direct Access to Workspace Data". It operates outside of the conventions of standard form, and can access data from any analytic workspace. However, tools that use OLAP_TABLE, such as the enablers, require standard form to construct the appropriate syntax.

About the Active Catalogs

Oracle OLAP provides catalogs of information about standard form analytic workspaces. These active catalogs are generated and maintained automatically without requiring any action by the DBA.

The active catalogs are implemented as public views with names that begin ALL_OLAP2_AW. For example, ALL_OLAP2_AW_CUBES lists cubes in all analytic workspaces, and ALL_OLAP2_AW_DIMENSIONS lists all of the dimensions. You can query the active catalogs directly from SQL.

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

OLAP Topics