2 Getting Started with Oracle OLAP

This chapter describes the preliminary steps you need to take to use Oracle OLAP. It assumes that you have already installed Oracle Database 11g Enterprise Edition. The OLAP option is installed automatically as part of a Basic installation of Oracle Database.

Note:

To start querying dimensional objects immediately, install the Global analytic workspace, as described in "Installing the Sample Schema". Then follow the instructions in Chapter 4.

This chapter includes the following topics:

Installing the Sample Schema
Database Management Tasks
Granting Privileges to DBAs and Application Developers
Getting Started with Analytic Workspace Manager

Installing the Sample Schema

You can download and install the sample Global schema from the Oracle Web site and use it to try the examples shown throughout this guide:

http://www.oracle.com/technology/products/bi/olap/doc_sample_schemas/sampleschemasfordoc.html

Instructions for installing the schema are provided in the README file.

Database Management Tasks

You should create undo, permanent, and temporary tablespaces that are appropriate for use by dimensional objects. Follow the recommendations in "Storage Management".

Granting Privileges to DBAs and Application Developers

Anyone who needs to create or manage dimensional objects in Oracle Database must have the necessary privileges. These privileges are different from those needed just to query the data stored in dimensional objects. The security system is discussed in Chapter 8.

DBAs and application developers need the following roles and privileges.

To create dimensional objects in the user's own schema:

OLAP_USER role
CREATE SESSION privilege

To create dimensional objects in different schemas:

OLAP_DBA role
CREATE SESSION privilege

To administer data security:

OLAP_XS_ADMIN role

To create cube materialized views in the user's own schema:

CREATE MATERIALIZED VIEW privilege
CREATE DIMENSION privilege
ADVISOR privilege

To create cube materialized views in different schemas:

CREATE ANY MATERIALIZED VIEW privilege
CREATE ANY DIMENSION privilege
ADVISOR privilege

Users also need an unlimited quota on the tablespace in which the dimensional objects will be stored. The tablespaces should be defined specifically for OLAP use, as described in Chapter 7.

If the source tables are in a different schema, then the owner of the dimensional objects needs SELECT object privileges on those tables.

Example 2-1 shows the SQL statements for creating the GLOBAL user.

Example 2-1 SQL Statements for Creating the GLOBAL User

CREATE USER 'GLOBAL' IDENTIFIED BY 'global'
   DEFAULT TABLESPACE glo 
   TEMPORARY TABLESPACE glotmp
   QUOTA UNLIMITED ON glo;

GRANT OLAP_USER TO GLOBAL;
GRANT CREATE SESSION TO GLOBAL;
GRANT OLAP_XS_ADMIN TO GLOBAL;

Getting Started with Analytic Workspace Manager

In this section, you will learn how to install Analytic Workspace Manager software and make a connection to Oracle Database.

Installing Analytic Workspace Manager

Analytic Workspace Manager is distributed on the Oracle Database Client installation disk.

If you are installing on the same system as the database, then choose a Custom installation and install into the same Oracle home directory as the database. Select OLAP Analytic Workspace Manager and Worksheet from the list of components.

If you are installing on a remote system, then choose either an Administrator or a Custom installation. The Administrator choice automatically installs Analytic Workspace Manager on the client.

Opening Analytic Workspace Manager

On Windows, open Analytic Workspace Manager from the Start menu. Choose Oracle - Oracle_home, then Integrated Management Tools, and then OLAP Analytic Workspace Manager and Worksheet.

On Linux, open Analytic Workspace Manager from the shell command line:

$ORACLE_HOME/olap/awm/awm.sh

Defining a Database Connection

You can define a connection to each database that you use for OLAP. After you define a connection, the database instance is listed in the navigation tree for you to access at any time.

To define a database connection:

Right-click the top Databases folder in the navigation tree, then choose Add Database to Tree from the pop-up menu.
Complete the Add Database to Tree dialog box.

Opening a Database Connection

To connect to a database:

Click the plus icon (+) next to a database in the navigation tree.
Complete the Connect to Database dialog box.

Figure 2-1 shows Analytic Workspace Manager displaying the properties of the database connection by the Global user.

Figure 2-1 Analytic Workspace Manager Connection to Oracle Database

Description of "Figure 2-1 Analytic Workspace Manager Connection to Oracle Database"

Installing Plugins

Plugins extend the functionality of Analytic Workspace Manager. Any Java developer can create a plugin. Plugins are distributed as JAR files. The developer should provide information about what the plugin does and how to use it.

If you have one or more plugins, then you only need to identify their location to Analytic Workspace Manager.

To Use Plugins:

Create a local directory for storing plugins for Analytic Workspace Manager.
Copy the JAR files to that directory.
Open Analytic Workspace Manager.
Choose Configuration from the Tools menu.

The Configuration dialog box opens.
Select Enable Plugins and identify the plugin directory. Click OK.
Close and reopen Analytic Workspace Manager.

The new functionality provided by the plugins is available in the navigator.