12 Administering Oracle OLAP

12.2.1 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

12.2.2 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;

12.2.3 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;

12.2.4 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, DBMS_LOB.GETLENGTH(AWLOB) FROM AW$awname;

Where:

awname is the name of the analytic workspace.

12.3.1 SQL Access For DBAs and Application Developers

To create OLAP Catalog metadata, users must be granted the OLAP_USER role. To create analytic workspaces, users also need SELECT privileges on the source schema tables, and an unlimited quota on the tablespace in which the workspace is created. Example 12-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
   QUOTA UNLIMITED ON glotmp
   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;

12.3.2 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 XADEMO analytic workspace is stored in the AW$XADEMO relational table.

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

Example 12-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;

12.3.3 Access to Database Objects Using the BI Beans

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

• CONNECT role

• QUERY REWRITE system privilege

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

12.4.1 Procedure: Setting System Parameters for OLAP

Take the following steps to set system parameters:

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

   The initialization file is located in $ORACLE_HOME/admin/sid/pfile, where sid is the system identifier as defined in $ORACLE_HOME/network/admin/tnsnames.ora.

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=/users/oracle/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
   

12.4.2 About the OLAP_PAGE_POOL_SIZE Setting

OLAP_PAGE_POOL_SIZE is an initialization parameter that is specific to Oracle OLAP. This parameter specifies in bytes the maximum size of the paging cache to be allocated to each OLAP session. The minimum value of OLAP_PAGE_POOL_SIZE is 2 MB. The default value is 32 MB.

These are the basic guidelines for setting OLAP_PAGE_POOL_SIZE:

• In the database initialization file, set OLAP_PAGE_POOL_SIZE to a value based on the maximum number of simultaneous OLAP users. The setting should be in the order to 2-8MB; 4MB is typical. Larger is better, but remember that each user is allocated that amount.

• For data loads, use a SQL ALTER SESSION statement to enlarge the OLAP page pool as much as possible just for the duration of the load, on the basis that the page pool is not shared with other users at this time. The setting should be in the order of 100-400MB, but smaller than DB_CACHE_SIZE.

The OLAP page pool is allocated at the start of an OLAP session and released when the user closes the session. An OLAP session can be initiated by the OLAP_TABLE function, the DBMS_AWM PL/SQL package, or using the command line in OLAP Worksheet.

The OLAP page pool is allocated from the User Global Area (UGA). When the database is running in dedicated mode, the UGA is part of the Process Global Area (PGA). When the database is running as a shared server process, the UGA is part of the Shared Global Area (SGA).

When the OLAP page pool is full, it uses the DB cache as a swap space. This in-memory swapping is a relatively fast operation. When the DB cache is full, it swaps to disk, which is a relatively slow operation. If the DB cache must swap to disk frequently, then performance will suffer significantly.

12.4.3 About the PGA_AGGREGATE_TARGET Setting

PGA_AGGREGATE_TARGET is used by SQL statements, particularly when performing SELECT statements with GROUP BY and ORDER BY clauses. It is not used by the OLAP engine. However, PGA_AGGREGATE_TARGET can affect the performance of the BI Beans when selecting data from relational tables. If your Oracle Database supports this type of application, set PGA_AGGREGATE_TARGET initially to 200-400MB, and use the database performance monitoring tools to recommend adjustments.

12.6.1 Creating a Database Directory

To create a database directory, 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:

12.6.2 Granting Access Rights to a Database Directory

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 database directory

user is a database user who gets immediate access rights to directory

role is a database role that gets immediate access rights to directory

PUBLIC gives all database users immediate access rights to directory

12.6.3 Example: Creating and Using a Database Directory

The following SQL commands create a directory named OLAPFILES to control access to a 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

12.7.1 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 12-2 describes the columns of a table that stores an analytic workspace.

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

SELECT * FROM aw$global WHERE
     OBJNAME = 'TIME' OR
     OBJNAME = 'UNITS_VARIABLE'
     ORDER BY GEN#, PS#;

12.7.2 System Tables

The SYS user owns several tables associated with analytic workspaces:

AW$EXPRESS
AW$AWCREATE
AW$AWMD
AW$
PS$

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

• 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 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$.

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