Understanding The Role Of The OLAP Catalog

Mark Rittman -

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