6 Administering Oracle OLAP

Creating an UNDO Tablespace

The following SQL commands create an undo tablespace with the appropriate parameters for use by analytic workspaces:

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;

SQL Access For DBAs and Application Developers

To use Analytic Workspace Manager, users 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.

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

• QUERY REWRITE system privilege (for relational tables).

• OLAP_USER role (for relational tables).

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:

   PGA_AGGREGATE_TARGET=1G

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
   

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

Is the OLAP Option Installed in the Database?

The OLAP option is provided with Oracle Database Enterprise Edition. To verify that the OLAP components have been installed, issue this SQL command:

SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY WHERE COMP_NAME LIKE '%OLAP%';
 
COMP_NAME                 VERSION      STATUS
------------------------- ------------ -----------
OLAP Analytic Workspace   10.2.0.3.0   VALID
Oracle OLAP API           10.2.0.3.0   VALID
OLAP Catalog              10.2.0.3.0   VALID

What Analytic Workspaces are in the Database?

The DBA_AWS view provides information about all analytic workspaces. Use the following SQL command to get a list of names and their owners:

SELECT OWNER, AW_NAME FROM DBA_AWS;
 
OWNER                          AW_NAME
------------------------------ ------------------------------
SYS                            EXPRESS
SYS                            AWMD
SYS                            AWCREATE
SYS                            AWCREATE10G
SYS                            AWXML
SYS                            AWREPORT
GLOBAL_AW                      GLOBAL

How Big is the Analytic Workspace?

To find out the size of the tablespace extensions for a particular analytic workspace, use the following SQL statements, replacing GLOBAL with the name of your analytic workspace.

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

    EXTNUM SUM(DBMS_LOB.GETLENGTH(AWLOB))
---------- ------------------------------
         0                       50450928

The DBMS_LOB PL/SQL package includes a program for reporting the size of a LOB table that stores an analytic workspace. Use a SQL command like the following, replacing GLOBAL with the name of your analytic workspace and GLOBAL_AW with the name of the schema.

SELECT ROUND(SUM(DBMS_LOB.GETLENGTH(AWLOB))/1024,0) "KB" FROM global_aw.aw$global;
 
        KB
----------
     53700

How Is the Analytic Workspace Stored?

The DBMS_METADATA PL/SQL package contains a subprogram that shows how any particular analytic workspace is stored in the database. Use a SQL command like the following, replacing GLOBAL with the name of your analytic workspace and GLOBAL_AW with the name of the schema.

SELECT DBMS_METADATA.GET_DDL('TABLE', 'AW$GLOBAL', 'GLOBAL_AW') FROM DUAL;
 
DBMS_METADATA.GET_DDL('TABLE','AW$GLOBAL','GLOBAL_AW')
--------------------------------------------------------------------------------
 
  CREATE TABLE "GLOBAL_AW"."AW$GLOBAL"
   (    "PS#" NUMBER(10,0),
        "GEN#" NUMBER(10,0),
        "EXTNUM" NUMBER(8,0),
        "AWLOB" BLOB,
        "OBJNAME" VARCHAR2(256),
        "PARTNAME" VARCHAR2(256)
   ) PCTFREE 10 PCTUSED 40 INITRANS 4 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "GLOBAL"
 LOB ("AWLOB") STORE AS (
  DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 0
  CACHE
  STORAGE(
  BUFFER_POOL DEFAULT))
  PARTITION BY RANGE ("GEN#")
  SUBPARTITION BY HASH ("PS#","EXTNUM")
  SUBPARTITIONS 8
        .
        .
        .

When Were the Analytic Workspaces Created?

The DBA_OBJECTS view provides the creation date of the objects in your database. The following SQL command generates an easily readable report for analytic workspaces.

SELECT OWNER ||'.'||SUBSTR(OBJECT_NAME,4) AS AW_NAME, CREATED FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'AW$%' AND OBJECT_NAME != 'AW$' GROUP BY OWNER, OBJECT_NAME, CREATED ORDER BY OWNER, AW_NAME;
 
AW_NAME              CREATED
-------------------- ---------
GLOBAL_AW.GLOBAL     10-JUL-06
SYS.AWCREATE         08-JUL-06
SYS.AWCREATE10G      08-JUL-06
SYS.AWMD             08-JUL-06
SYS.AWREPORT         08-JUL-06
SYS.AWXML            08-JUL-06
SYS.EXPRESS          08-JUL-06

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 measure is stored in one row; a partitioned measure 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

The SYS user owns several tables and views associated with analytic workspaces. Most of them are LOB tables that contain analytic workspaces, which are attached automatically to a user's session as needed. Following are brief descriptions of these objects.

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

• The following tables contain analytic workspaces:

  • AW$AWCREATE10G stores the AWCREATE10G analytic workspace, which contains programs for using OLAP Catalog metadata in Oracle Database 10g Release 10.1.0.3.

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

  • AW$AWREPORT stores the AWREPORT analytic workspace, which contains a program named AWREPORT for generating a summary space report.

  • AW$AWXML stores the AWXML analytic workspace, which contains programs for creating and managing standard form analytic workspaces for Oracle Database 10g Release 10.2 and later.

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

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

Static Data Dictionary Views

Among the static views of the database data dictionary are several that provide information about analytic workspaces. Table 6-5 brief descriptions of them. There are corresponding DBA and USER views.