Rittman Mead - Delivered Intelligence

"What is the definition and purpose of an olap catalog? how can I create
one? what do I add to it? and how do I add things to it? I am using Oracle 10g
with JDeveloper 9.0.4 and BI Beans 9.0.4"

The OLAP Catalog is a set of metadata that sits between Java applications
that use the OLAP API, and the actual OLAP data stored within the Oracle
database. Within Oracle 9i and 10g, you can store OLAP data either relationally
within regular tables and columns, and multidimensionally, in analytic
workspaces, and the OLAP Catalog acts as a layer of abstraction between the
physical implementation and your BI applications. By using the OLAP Catalog as
the datasource for your BI applications, the DBA can decide on a case-by-case
basis whether to store OLAP data relationally or multidimensionally, and all
your Oracle BI applications have to worry about is working with generalised
dimensions, measures, levels, hierarchies and attributes. Not all Oracle’s BI
tools use the OLAP Catalog - at the moment, only JDeveloper applications built
using BI Beans, and Oracle’s next version of Discoverer, use the OLAP Catalog,
and regular reporting tools such as Discoverer 9iAS and Oracle Reports generally
report directly against Oracle relational tables. You also need to have licensed
the OLAP Option before you can use the OLAP Catalog, and like all options this
requires the Enterprise Edition of the database.

The
OLAP Catalog is held within the OLAPSYS schema and holds the logical, abstracted model of the OLAP
data within the database. This logical model can map on to data physically
held in regular relational structures (known as a Relational OLAP cube, or ROLAP
cube) or on to data held within multidimensional analytic workspaces (which are
referred to as Multidimensional OLAP cubes, or MOLAP cubes).

When the OLAP Catalog refers to OLAP data that is physically stored an analytic workspace, it
holds details of the views, created using abstract data types and the OLAP_TABLE
function, that are mapped on to the analytic workspace so that the data can be
accessed via SQL. For OLAP Catalog entries that refer to OLAP data in regular
tables and columns, the OLAP Catalog contains references to them together with
additional OLAP metadata used by Oracle’s OLAP query tools. Entries in the catalog that refer to ROLAP cubes are generally held within the CWM1 (or "CWMLite")
prefixed tables, whilst entries referring to analytic workspaces are held within
the CWM2-prefixed tables.

To create the OLAP Catalog, ensure you select the OLAP Option when you create
your database using the Database Creation Assistant, or run Universal Installer
to install the OLAP Option into an existing database.

The OLAP Catalog includes the following:

• Metadata model tables -
  A set of tables that instantiate the OLAP metadata model. These tables define
  all the OLAP metadata objects: dimensions, measures, cubes, measure folders,
  and so on. Within the metadata definitions are references to the actual
  warehouse data.
• A Write API - A
  
  set of PL/SQL packages prefixed by CWM2_* for creating and editing OLAP
  metadata. These packages contain procedures for inserting, updating, and
  deleting rows in the model tables.
• A Read API - A
  
  set of SQL views providing information about the metadata registered in
  the model tables.

The BI Beans catalog is installed when you install BI Beans and sits in the
BIBCAT schema. This catalog stores the definition of reports, crosstabs, graphs
and calculations created using BI Beans and itself references OLAP metadata held
in the OLAP Catalog.

Since Oracle 9i Release 2, there have been a number of developments with the
OLAP Catalog that makes working with it much simpler and easier. The first
development was with Oracle 9i 9.2.0.4.1, when Oracle released a utility called
Analytic Workspace Manager, a GUI for creating and populating analytic
workspaces and their corresponding OLAP Catalog entries.

Analytic Workspace Manager was updated for Oracle Database 10g and can be
found on the Client CD. Together with Analytic Workspace Manager, Oracle also
released a new PL/SQL package called

DBMS_AWM, used by Analytic Workspace Manager, which simplifies the process
of creating OLAP cubes, and their OLAP Catalog metadata, from relational star
schemas.

In practice, it’s been quite hard to work with the OLAP Catalog as it’s
proved rather fragile and invalidates easily; also, the various read and write
APIs, even with the DBMS_AWM wrapper, are tricky and cumbersome to work with and
this has had the effect of putting off many "dabblers" who would otherwise like
to try out Oracle OLAP. The latest bit of news, however, is that with the forthcoming 10.1.0.4
release of Oracle Database 10g, the OLAP Catalog will be made more or less
redundant as OLAP cubes held within analytic workspaces will store their
metadata within the analytic workspace itself, rather than in the OLAP Catalog.
Oracle Database 10g 10.1.0.4 uses the new AWXML Java API, rather than the old
CWM2_* PL/SQL packages, to define analytic workspace objects, and these objects
are now dynamically enabled for use by the OLAP API, without developers having
to worry about creating and maintaining OLAP Catalog entries. The logical
dimensional model that used to be held within the OLAP Catalog is now held in
additional analytic workspace objects within the analytic workspace and is
referred to as the "Active Catalog Views", and the OLAP API and BI Beans query
this rather than the OLAP Catalog. In addition, the SELECT statements for the
view over the analytic workspace are stored in the analytic workspace itself,
and enablement no longer requires the creation of additional abstract data
types.

With this new AWXML API and the new Active Catalog Views feature, a new
release of Analytic Workspace Manager is due for release at the start of 2005,
more details of which are due to be released at next month’s Oracle Open World
in San Francisco. This new Analytic Workspace Manager allows you to create
analytic workspaces without having to first have a version held in a relational
star schema, and comes with a cut-down version of Oracle Warehouse Builder’s
mapping feature that allows you to load data from any set of Oracle tables, or
from flat files via the external table feature.

So, to sum things up; the OLAP Catalog is used to store a
logical model of the relational and multidimensional data within your Oracle
database, and is the datasource used by the Java OLAP API. You can add metadata
to it either via the CWM2_* PL/SQL APIs, by the newer DBMS_AWM package which
contains routines for migrating relational data into analytic workspaces, or in
a few months time, you’ll be able to use the AWXML Java API to create
dynamically-enabled analytic workspaces and forget about the OLAP Catalog
altogether for multidimensional data.

Posted in Oracle OLAP |