6 Administering Oracle OLAP

Creating an UNDO Tablespace

The following SQL commands create an undo tablespace.

CREATE UNDO TABLESPACE tablespace DATAFILE 'pathname' 
     SIZE size REUSE AUTOEXTEND ON NEXT size
     MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

Where:

For example:

CREATE UNDO TABLESPACE olapundo DATAFILE '$ORACLE_HOME/oradata/undo.dbf'
    SIZE 64M REUSE AUTOEXTEND ON NEXT 8M
    MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

After creating the undo tablespace, change your system parameter file to include these settings, then restart the database as described in "Initialization Parameters for Oracle OLAP".

UNDO_TABLESPACE=tablespace
UNDO_MANAGEMENT=AUTO

Creating a Permanent Tablespace for Analytic Workspaces

When a user creates an analytic workspace, it is created in the user's default tablespace, which is initially set to the SYS tablespace. The following SQL statements create a tablespace appropriate for storing analytic workspaces.

CREATE TABLESPACE tablespace DATAFILE 'pathname'
    SIZE size REUSE AUTOEXTEND ON NEXT size MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ALTER USER username DEFAULT TABLESPACE tablespace

Where:

For example:

CREATE TABLESPACE glo DATAFILE '$ORACLE_HOME/oradata/glo.dbf'
   SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
   EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

If your computer has multiple disks, then you can stripe the tablespace across them. The next example shows SQL statements that distribute the GLO tablespace across three physical disks:

CREATE TABLESPACE glo DATAFILE 
   'disk1/oradata/glo1.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE 1024M
   EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ALTER TABLESPACE glo ADD DATAFILE 
   'disk2/oradata/glo2.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE 1024M,
   'disk3/oradata/glo3.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED;

Creating a Temporary Tablespace for Analytic Workspaces

Oracle OLAP uses temporary tablespace to store all changes to the data in an analytic workspace, whether the changes are the result of a data load, what-if analysis, forecasting, aggregation, or some other analysis. An OLAP DML UPDATE command moves the changes into the permanent tablespace and clears the temporary tablespace.

Oracle OLAP also uses temporary tablespace to maintain different generations of an analytic workspace. This enables it to present a consistent view of the analytic workspace when one or more users are reading it while the contents are being updated. This usage creates numerous extensions within the tablespace, so be sure to specify a small EXTENT MANAGEMENT size.

The following commands create a temporary tablespace suitable for use by Oracle OLAP.

CREATE TEMPORARY TABLESPACE tablespace TEMPFILE 'pathname' 
   SIZE size REUSE AUTOEXTEND ON NEXT size MAXSIZE UNLIMITED
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE size;

Where:

For example:

CREATE TEMPORARY TABLESPACE glotmp TEMPFILE '$ORACLE_HOME/oradata/glotmp.tmp'
   SIZE 50M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

You can stripe temporary tablespaces across several disks the same as permanent tablespaces. The next example shows the GLOTMP temporary tablespace striped across three physical disks.

CREATE TEMPORARY TABLESPACE glotmp TEMPFILE 
   'disk1/oradata/glotmp1.tmp' SIZE 50M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 1024M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

ALTER TABLESPACE glotmp ADD TEMPFILE 
   'disk2/oradata/glotmp2.tmp' SIZE 50M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 1024M,
   'disk3/oradata/glotmp3.tmp' SIZE 50M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

Querying the Size of an Analytic Workspace

To find out the size of the tablespace extensions for a particular analytic workspace, use the following SQL statements:

COLUMN DBMS_LOB.GETLENGTH(AWLOB) HEADING "Bytes";
SELECT EXTNUM, SUM(DBMS_LOB.GETLENGTH(AWLOB)) FROM AW$awname GROUP BY EXTNUM;

Where:

awname is the name of the analytic workspace.

SQL Access For DBAs and Application Developers

To use Analytic Workspace Manager, users must be granted the OLAP_USER role. They also need SELECT privileges on the source schema tables, and an unlimited quota on the tablespace in which the workspace is created. Example 6-1 shows the SQL statements for creating the GLOBAL_AW user.

CREATE USER 'GLOBAL_AW' IDENTIFIED BY 'global_aw'
   DEFAULT TABLESPACE glo 
   TEMPORARY TABLESPACE glotmp
   QUOTA UNLIMITED ON glo
   ACCOUNT UNLOCK;

GRANT SELECT ON global.channel_dim TO global_aw;
GRANT SELECT ON global.customer_dim TO global_aw;
GRANT SELECT ON global.product_dim TO global_aw;
GRANT SELECT ON global.time_dim TO global_aw;
GRANT SELECT ON global.price_and_cost_history_fact TO global_aw;
GRANT SELECT ON global.price_and_cost_update_fact TO global_aw;
GRANT SELECT ON global.units_history_fact TO global_aw;
GRANT SELECT ON global.units_update_fact TO global_aw;

SQL Access for Analysts

To access an existing analytic workspace, users must have these access privileges on the table in which the workspace is stored:

• To read from the analytic workspace, SELECT privileges.

• To write to the analytic workspace, SELECT, INSERT, and UPDATE privileges.

Note that the name of the table is the same as the name of the analytic workspace, with the addition of an AW$ prefix. For example, the GLOBAL analytic workspace is stored in the AW$GLOBAL relational table.

For users to access views of workspace data, they must be granted EXECUTE privileges explicitly on those views.

Example 6-2 shows the SQL statements that gives all users read-only privileges to the GLOBAL analytic workspace, and user SCOTT read/write privileges.

GRANT SELECT ON   global_aw.aw$global TO public;
GRANT INSERT ON         global_aw.aw$global TO scott;
GRANT UPDATE ON         global_aw.aw$global TO scott;

Access to Database Objects Using OracleBI Beans

To connect to a database using OracleBI Beans, users must have the following access rights:

• CONNECT role

• QUERY REWRITE system privilege (for relational tables)

• SELECT privileges on the database objects containing the data to be analyzed, whether the data is stored in an analytic workspace or in relational tables. Refer to the previous topic, "SQL Access for Analysts", for information about granting access to analytic workspaces.

Access to the Oracle JVM

Users who want to author or execute Analytic Workspace Java API applications within the Oracle Java Virtual Machine (JVM) may need the following Java permissions, in addition to the OLAP_DBA or OLAP_USER role:

You can grant these permissions in either Java or SQL.

Procedure: Setting System Parameters for OLAP

Take the following steps to set system parameters:

1. Open the init.ora initialization file in a text editor.

2. Add or change the settings in the file.

   For example, you might enter a command like this so that Oracle can write files to the olapscripts directory:

   utl_file_dir=c:\olapscripts

3. Stop and restart the database, using commands such as the following. Be sure to identify the initialization file in the STARTUP command.

   SQLPLUS '/ AS SYSDBA'
   SHUTDOWN IMMEDIATE
   STARTUP pfile=$ORACLE_HOME/admin/rel10g/pfile/initrel10g.ora
   

About the PGA_AGGREGATE_TARGET Setting

PGA_AGGREGATE_TARGET helps the OLAP engine determine whether the OLAP page pool can grow in response to a session's demand for pages. It is also used by SQL statements, particularly when performing SELECT statements with GROUP BY and ORDER BY clauses. PGA_AGGREGATE_TARGET can affect the performance of OracleBI Beans when selecting data from relational tables.

Set PGA_AGGREGATE_TARGET initially to 200-400MB, and use the database performance monitoring tools to recommend adjustments.

Creating a Directory Object

To create a directory object, you must have CREATE ANY DIRECTORY system privileges.

Use a CREATE DIRECTORY statement to create a new directory, or a REPLACE DIRECTORY statement to redefine an existing directory, using the following PL/SQL syntax:

{CREATE | REPLACE | CREATE OR REPLACE} DIRECTORY directory AS 'pathname';

Where:

Granting Access Rights to a Directory Object

After you create a directory, grant users and groups access rights to the files contained in that directory, using the following PL/SQL syntax:

GRANT permission ON DIRECTORY directory TO {user | role | PUBLIC};

Where:

permission is one of the following:

directory is the name of the directory object

user is a database user

role is a database role

PUBLIC is all database users

Example: Creating and Using a Directory Object

The following SQL commands create a directory object named OLAPFILES to control access to a physical directory named /users/oracle/OraHome1/olap and grant read access to all users.

CREATE DIRECTORY olapfiles as '/users/oracle/OraHome1/olap';
GRANT READ ON DIRECTORY olapfiles TO PUBLIC;

Users access files located in /users/oracle/OraHome1/olap with DML commands such as this one:

IMPORT ALL FROM EIF FILE 'olapfiles/salesq2.eif' DATA DFNS

Analytic Workspace Tables

Analytic workspaces are stored in tables in the Oracle Database. The names of these tables always begin with AW$.

For example, if the GLOBAL_AW user creates two analytic workspaces, one named GLOBAL and the other named GLOBAL_PROGRAMS, then these tables will be created in the GLOBAL_AW schema:

AW$GLOBAL
AW$GLOBAL_PROGRAMS

Tables are created by default with eight partitions. You can manage these partitions the same as you would for any other table in your database.

The tables store all of the object definitions and data. Each object in an analytic workspace is stored in one or more page spaces, and each page space is stored in a separate row of the table. A page space is grouping of related data pages; a page is a unit for swapping data in and out of memory.

For example, a dimension is stored in three page spaces and thus has three rows (one each for dimension members, a hash index, and a logical-to-physical map). A variable is stored in one row; a partitioned variable has a row for each partition.

Table 6-3 describes the columns of a table that stores an analytic workspace.

Table 6-4 shows a few sample rows of an analytic workspace table, which are the results of the following query.

SELECT ps#, gen#, objname, partname FROM aw$global WHERE
     OBJNAME = 'TIME' OR
     OBJNAME = 'UNITS_CUBE_UNITS_STORED'
     ORDER BY GEN#, PS#;

System Tables and Views

The SYS user owns several tables and views associated with analytic workspaces:

AW$EXPRESS
AW$AWMD
AW$AWCREATE
AW$AWCREATE10G
AW$AWXML
AW$
PS$

Following are brief descriptions of these objects.

• AW$EXPRESS stores the EXPRESS analytic workspace. This workspace contains objects and programs that support the OLAP DML. The EXPRESS workspace is used any time that a session is open.

• AW$AWCREATE stores the AWCREATE analytic workspace, which contains programs for creating and managing standard form analytic workspaces for Analytic Workspace Manager 9.2.0.4.

• AW$AWCREATE10G stores the AWCREATE10G analytic workspace, which contains programs for using OLAP Catalog metadata in Analytic Workspace Manager 10.1.0.3.

• AW$AWXML stores the AWXML analytic workspace, which contains programs for creating and managing standard form analytic workspaces for Analytic Workspace Manager 10g.

• AW$AWMD stores the AWMD analytic workspace, which contains programs for creating standard form catalogs.

• AW$ maintains a record of all analytic workspaces in the database, recording its name, owner, and other information.

• PS$ maintains a history of all page spaces. A page space is an ordered series of bytes equivalent to a file. Oracle OLAP manages a cache of workspace pages. Pages are read from storage in a table and written into the cache in response to a query. The same page can be accessed by several sessions.

  The information stored in PS$ enables the Oracle OLAP to discard pages that are no longer in use, and to maintain a consistent view of the data for all users, even when the workspace is being modified during their sessions. When changes to a workspace are saved, unused pages are purged and the corresponding rows are deleted from PS$.

• ALL_AW_OBJ is a view that lists the current objects in all analytic workspaces to which the user has access rights.

• ALL_AW_PROP is a view that lists the current OLAP DML properties and their values in all analytic workspaces to which the user has access rights.

The CWM1 and CWM2 read APIs are tables owned by the OLAPSYS user. Public synonyms provide user access to these tables.