Using OWB and the AWM To Create Analytic Workspaces

Mark Rittman -

Now that the 9.2.0.4.1 Oracle Database patchset is available, together with the Analytic Workspace Manager and OWB9.2, I thought I'd take a look at the different options available for populating an Analytic Workspace and making the data available for querying using SQL.

Rather than manually building and populating analytic workspaces using the OLAP Worksheet and OLAP DML, you can now build and populate them using extensions to Oracle Warehouse Builder, or using the Analytic Workspace Manager add-in to Enterprise Manager. They work in broadly similar ways, using the same underlying AWM_ PL/SQL packages supplied as part of the 9.2.0.4.1 patchset, but there are a few subtle differences that are worth bearing in mind.

The two approaches I took to building the analytic workspace were as follows;

  1. Build the dimensions and cubes using Oracle Enterprise Manager, then use Analytic Workspace Manager to transfer the structures and data into an Analytic Workspace.
  2. Build the dimensions and cubes using Oracle Warehouse Builder, then use the 9i OLAP Metadata Transfer Bridge to transfer the structures and data into an Analytic Workspace.

Option 1 can be used using tools that you get 'out of the box' with Oracle 9i Release 2, although you'll need to download the Analytic Workspace Manager application separately from OTN. Option 2 requires you to license 9iDS, and then separately download the updated version of OWB, known as 9.2. Prior to all this, and before you install either the AWM or OWB, you'll need to download a list of patches from metalink to enable all the analytic workspace PL/SQL API packages. A guide to this process can be found elsewhere on the weblog.

Option 1 - Using Enterprise Manager and Analytic Workspace Manager.

Building the ROLAP Data Structures

Before you can use any of the new GUI tools for building an analytic workspace, you first have to build a ROLAP version of the cube, and this ROLAP version is used as the template for building the analytic workspace.

OEM 9.2 has a new node in the navigator view called 'Warehouse', with areas for Measures Folders, Cubes and Dimensions. This area is effectively a CWMLITE metadata browser, allowing you to create, view and amend ROLAP data structures such as cubes, dimensions, measures and summaries.

You can right-click on the 'dimensions' node and create CWMLITE metadata to construct dimensions for your warehouse or data mart. These dimensions are made up of columnns from relational tables, with the relationship between columns (and their hierarchies) being explicitly specified so that this can be later used by the query rewrite mechanism. The dimensions that OEM creates are different to the dimensions created by using the CREATE DIMENSION statement, or by using Oracle Warehouse Builder - whilst the CREATE DIMENSION statement is used in their intial creation, a number of PL/SQL CWM_* procedures are run afterwards to register CWMLITE metadata.

In addition to creating dimensions, OEM allows you to create Cubes, which bring together a fact table and a number of dimensions into a logical 'cube'. Creating the cube using OEM again runs a number of PL/SQL CWM_* packages to register the cube's CWMLITE metadata, and you can then browse the cube's measures by double-clicking on the cube icon and drilling down into the cube. In addition, you can use the OEM Cube Viewer facility to carry out ad-hoc querying of the data in the cube.

OEM therefore allows you to create dimensions and cubes in your database using CWMLITE metadata. CWMLITE, otherwise known as CWM or CWM1, is a predecessor to the CWM2 metadata used later in this process, and it is a 'stripped down' or earlier version of the metadata, and you can take a look at the CWMLITE metadata held in the database by logging on as OLAPSYS and browsing the CWM$CUBES, CWM$DIMENSIONS and CWM$HIERARCHIES tables. As mentioned previously, CMWLITE has several restrictions, one of which is that the underlying database objects used as the source for dimensions and cubes cannot be views - which meant it couldn't be used to describe analytic workspaces exposed using the OLAP_TABLE function, which appear in the database as views.

Populating The ROLAP Structures

If you want the Analytic Workspace Manager to populate the analytic workspace with data, as well as build the data structures, you first have to load up the ROLAP cube with the relevant data. If you don't want to do this, you could just use the AWM to build the data structures, and then separately build filereaders or data loaders and load the data directly into the analytic workspace.

Enterprise Manager doesn't however come with any data loading, or ETL, facility other than normal database imp's and exp's. Either your data needs to be in the tables (on which you've based your dimensions) already, or you need to load data into the underlying tables using SQL or PL/SQL. The usual method, however, is to use Oracle Warehouse Builder and i'll go into this more later on.

We'll work however on the assumption that you've loaded data into the dimension and cube tables by some method or another.

Building And Populating The Analytic Workspace

Just to recap : We've used OEM to build dimension and cube structures in the Oracle database, and as part of this process OEM has created and run the various CWM_ procedures to create the CWMLITE metadata. What we've built so far is a ROLAP cube and by some means or another we've loaded some data in to it.

Now, Analytic Workspace Manager is loaded up, and the OLAP Catalog view is selected, which shows our cube and dimensions under the 'Relational Cubes' node. AWM is an add-on to OEM that allows you to browse the OLAP Catalog, and in addition, when the 'Object View' option is selected, it allows you to browse and maintain objects in analytic workspaces.

The OLAP Catalog can contain metadata about cubes and dimensions in both CWMLITE, and CWM2 metadata formats. The ROLAP cube we've just defined is described using CWMLITE metadata, and as far as I know this is always the case for ROLAP cubes. CWM2 metadata is used to describe cubes built from analytic workspaces, as it contains more options and ways of describing cubes; dimensional and cube structures in the MOLAP world are often more complex that those in the ROLAP world, to take account of such areas as ragged, unbalanced and skip-level hierarchies, solved vs. fully solved cubes, parent-child hierarchies and so on. OEM can only view and display CWMLITE metadata, which we'll see later on means that the analytic workspace we create in a second won't be visible when browsing using OEM.

To create the analytic workspace, right-click on the relational cube and select the 'create analytic workspace using wizard' option. Options are then presented to allow you to populate the cube and dimensions as well as build them, and to enable the analytic workspace for Discoverer or the OLAP API. In this first instance, I just created the cube and allowed AWM to populate it.

Once this had completed, a new analytic workspace was created, and this could be browsed using the 'object view' AWM option, just like in Express Administrator. As I didn't select the 'enable for OLAP API access' option, no CWMLITE or CWM2 metadata was created, and therefore the newly created workspace didn't appear in either the OLAPSYS CWM$ or CWM2$ tables, or in the 'relational cubes' view in AWM.

As a next step, I right-clicked on the newly created analytic workspace, and selected the 'enable for OLAP API access' option. Once this had completed, another entry appeared in the 'relational cubes' area, representing the OLAP Catalog entry for this new ROLAP cube, which actually consisted of a number of SQL Views built using the OLAP_TABLE function over the analytic workspace. These OLAP Catalog entries were created using CWM2 metadata, and if you navigate back to OEM and the Warehouse node in the navigator, you'll see that OEM doesn't show this new cube - because it's been created using CWM2 metadata, and OEM is only a CWMLITE metadata browser.

Going back to the AWM, and loading up the OLAP Worksheet utility, running the listnames followed by rpr statement showed that data structures were indeed in the analytic workspace, and the dimensions and varables had data in them. Separately, running SELECT statements against the SQL Views created over the analytic workspace returned data as expected from the database.

Now this option was finished, I then loaded up OWB and tried the second approach.

Option 2 - Using Oracle Warehouse Builder and the 9i OLAP Metadata Bridge

OWB achieves much the same as using OEM and AWM, but it has a few key differences which are worth bearing in mind.

Building the ROLAP Data Structures

Loading up OWB, definitions for the dimensions and cubes were defined as you would normally do with OWB.

Populating The ROLAP Structures

Separately, the tables that contained the source data for the ROLAP cube had their metadata (table definitions) imported into OWB, and mappings were produced that loaded data from the source tables into OWB. This is a significant improvement over OEM and AWM, as OEM has no way of loading the data into the ROLAP data structures.

The dimensions and cube were then deployed to the Oracle database, and the mappings run to put data into the relevant tables. OEM was then loaded up, to take a look at what OWB had created.

Navigating down to the 'warehouse' node, the first thing to notice is that whilst the dimensions are listed, the cube isn't. This is because OWB doesn't create *any* CWM metadata initially - the scripts it produces just contain the CREATE TABLE and CREATE DIMENSION statements, and they don't contain any of the PL/SQL CWM_* procedure calls to create the CWMLITE metadata.

If you're now going to go on and use the 9i OLAP Metadata Bridge, this isn't a problem, as the bridge creates the CWMLITE metadata before creating the CWM2 metadata for the analytic workspace. If, however, you stop the process here and try and manipulate what you've created using OEM, you immediately get prompted to 'upgrade' the dimensions to CWMLITE standard before OEM will let you work with them. In addition, the 'cube' you've created using OWB isn't a cube by CWMLITE standards; it's just a table as far as it's concerned and you have to use OEM to create the cube as a separate exercise, which is accomplished by OEM creating and running the relevant CWM_* procedure calls.

Building And Populating The Analytic Workspace

Going back to OWB, then next job is to create a collection (a logical container for the cube and dimensions you're going to export to 9i OLAP) and then start up the 9i OLAP Metadata bridge. Once the bridge starts, you have the option of using the bridge to populate the cube, and to create the CMW2 entries to anable access to the analytic workspace using the OLAP API. I chose these options and let OWB create and populate the cube.

As an alternative to OWB directly populating the cube, you can just use the bridge to create the data structures, and then use some new mapping transformations to separately load and refresh the cubes and variables.

After running the transfer wizard, I then loaded up the AWM, and saw that there were two relational cubes listed; one for my ROLAP cube as defined using OWB (created using CWMLITE metadata), and one for my analytic workspace, that had been exposed as a relational cube using the OLAP_TABLE function. This is the same outcome as was achieved using the OEM / AWM approach. In addition, my analytic workspace was created and data had been loaded into the dimensions and variables.

Additional Features In The Different Tools

In addition to the basic features detailed above, each of the tools has additional features that can be used in a data warehousing project.

Analytic Workspace Manager Features

In addition to AWM's ability to browse the full OLAP Catalog, including both CWMLITE and CWM2-defined cubes, it also has a range of features designed to help the administration of analytic workspaces.

Starting off, the options available to you when initially building and populating the analytic workspace are greater than with OWB. AWM allows you to set up aggregation plans (or AGGMAPs) and composite dimensions, and additionally allows you to set up formulas, relations, and so on - all aspects of database administration that Express users are used to. It also allows you to create, view and execute OLAP DML programs, comes with the OLAP Worksheet (a GUI CLI-tool for executing OLAP DML directly against the analytic workspace), and contains functionality for exporting and importing EIF files.

Oracle Warehouse Builder Features

Exporting ROLAP cubes into analytic workspaces is only a minor feature of the OWB feature set. OWB is a full project lifecycle ETL tool that allows you define a complete process, and metadata management strategy, for Oracle data warehouses. OWB is typically used for sourcing and transforming your warehouse data, building up a complete set of metadata, and then sequencing all the various warehouse load jobs together using Oracle Workflow and OEM. Whilst primarily a relational OLAP tool, OWB as we've seen has hooks into various other Oracle BI tools, such as Oracle OLAP, Express Server and Discoverer.

Conclusions

  1. Both approaches will create and load analytic workspaces, taking a ROLAP cube as the template and loading data directly from the tables that make up the ROLAP cube. There is currently no way to build an analytic workspace (using GUI tools) without first building a ROLAP representation of it, and no way to load the analytic workspace unless you've got the data in Oracle tables first of all. Of course, if you don't want to bother with the AWM or OWB, you can write data loaders and OLAP DML to populate the analytic workspace directly.
  2. The cubes and dimensions you see when viewing the OLAP Catalog using OEM are CWMLITE cubes, and because of this they'll always be ROLAP cubes.
  3. The AWM's display of the OLAP Catalog includes both CWMLITE and CWM2-defined cubes, and therefore it can display cubes build on top of analytic workspaces
  4. The ROLAP cubes and dimensions that OWB deploys are not CWM-anything; they're just tables and dimensions, as defined in the data dictionary. OEM has to be used to upgrade these objects to CWMLITE standard before they become part of the OLAP Catalog and maintainable by OEM.
  5. Both AWM and OWB allow you to both create, initially populate and then refresh analytic workspaces, as well as create the relevant OLAP Catalog entries. The AWM allows a bit more fine-grained control, allowing you to build AGGMAPs, composites, functions and so on, and it also has an option to create Discoverer EUL entries for the OLAP-API-exposed analytic workspace.
  6. OWB however has the ability for you to define mappings to load data from relational source tables into your ROLAP star-schema, which makes it a bit more of an 'end-to-end' solution when putting together a warehouse project.

Given the above, the conclusion i've come to is that both tools effectively do more or less the same thing. The AWM, and the 9i OLAP Metadata Bridge in OWB, are effectively GUI front-ends to the new DBMW_AWM packages, and once you select a few options (schema to deploy to, whether to create OLAP API entries and so on) they allow you to define an analytic workspace based on an existing ROLAP star-schema, and then populate the AW using data from the star schema. OWB can then be used as the ETL tool to get data into the star schema in the first place, whilst AWM can be used to make fine-grained adjustments to the final analytic workspace, with AWM then carrying on as the main tool for maintaining the workspace, and OWB the tool used for refreshing it.