Create an undo tablespace with the EXTENT MANAGEMENT LOCAL clause, as shown in this example:

CREATE UNDO TABLESPACE olapundo DATAFILE '$ORACLE_BASE/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 the following settings, then restart the database as described in "Setting Database Initialization Parameters".

UNDO_TABLESPACE=tablespace
UNDO_MANAGEMENT=AUTO

Each dimensional object occupies at least one extent. A fixed extent size may waste most of the allocated space. For example, if an object is 64K and the extents are set to a uniform size of 1M (the default), then only a small portion of the extent is used.

Create permanent tablespaces with the EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO clauses, as shown in this example:

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

Oracle OLAP uses the temporary tablespace to store all changes to the data in a cube, whether the changes are the result of a data load or data analysis. Saving the cube moves the changes into the permanent tablespace and clears the temporary tablespace.

This usage creates numerous extents within the tablespace. A temporary tablespace suitable for use by Oracle OLAP should specify the EXTENT MANAGEMENT LOCAL clause and a UNIFORM SIZE clause with a small size, as shown in this example:

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

Oracle Database provides excellent storage management tools to simplify routine tasks. Automatic Storage Management (ASM) provides a simple storage management interface that virtualizes database storage into disk groups. You can manage a small set of disk groups, and ASM automates the placement of the database files within those disk groups.

ASM spreads data evenly across all available storage resources to optimize performance and utilization. After you add or drop disks, ASM automatically rebalances files across the disk group.

Because OLAP is part of Oracle Database, you can use ASM to manage both relational and dimensional data.

ASM is highly recommended for analytic workspaces. A system managed with ASM is faster than a file system and easier to manage than raw devices. ASM optimizes the performance of analytic workspaces both on systems with Oracle RAC and those without Oracle RAC.

However, you do not need ASM to use Oracle OLAP. You can still spread your data across multiple disks, just by defining the tablespaces like in this example:

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;

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

For example, if the GLOBAL user creates two analytic workspaces, one named FINANCIALS and the other named MARKETING, then these tables are created in the GLOBAL schema:

AW$FINANCIALS
AW$MARKETING

The tables store all of the object definitions and data.

To discover the current partitioning, query the ALL_CUBES data dictionary view. The PARTITION_DIMENSION_NAME, PARTITION_HIERARCHY_NAME, and PARTITION_LEVEL_NAME columns display partitioning information. For example, the following query shows that the Units Cube is partitioned on the Time dimension, the Calendar hierarchy, and the Calendar Year level.

SELECT partition_dimension_name, partition_hierarchy_name, 
     partition_level_name FROM all_cubes
     WHERE owner='GLOBAL' AND cube_name='UNITS_CUBE';

PARTITION_DIMENSION_NAME  PARTITION_HIERARCHY_NAME  PARTITION_LEVEL_NAME
------------------------- ------------------------- --------------------
TIME                      CALENDAR                  CALENDAR_YEAR

The OLAP engine automatically creates and drops partitions as part of data maintenance, as members are added and deleted from the partitioning dimension.

For example, assume that in the sample Global analytic workspace, the Units cube is partitioned on the Time dimension, using the Calendar hierarchy, and at the Calendar Quarter level. The OLAP engine creates a partition for each Calendar Quarter and its children. The default top partition contains Calendar Years and all members of the Fiscal hierarchy. If Global has three years of data, then the Units cube has 13 partitions: Four bottom partitions for each Calendar Year, plus the top partition.

A data refresh typically creates new time periods and deletes old ones. Whenever a Calendar Quarter value is loaded into the Time dimension, a corresponding partition is added to the cube. Whenever a Calendar Quarter value is deleted from the Time dimension, the corresponding empty partition is deleted from the cube.

You can improve the performance of data maintenance by enabling parallel processing. There are two levels of parallelism:

• Parallel job execution: Loading and aggregating the data using multiple processes.

• Parallel update: Moving the data from temporary to permanent tablespaces using multiple processes.

This number of parallel processes is controlled by these factors:

• The number of objects that can be aggregated in parallel. Each cube and each partition (including the top partition) can use a separate process.

  You can control the number of partitions in a cube on the Partitioning tab of the cube property sheet in Analytic Workspace Manager.

• The number of simultaneous database processes the user is authorized to run.

  This number is controlled by the JOB_QUEUE_PROCESSES parameter. If you have SYS privileges, you can obtain the current parameter setting with the following SQL command:

  SHOW PARAMETER JOB_QUEUE_PROCESSES
  

• For parallel update, the number of processes you allocate to the job. You can specify the number of processes in the Maintenance Wizard of Analytic Workspace Manager when specifying the task processing options, or on the Materialized View tab of the cube.

• The number of processes allocated to SQL to fetch rows from the relational source tables. When PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED, the database can allocate additional processes for executing SQL statements.

Suppose that a cube is partitioned on the Quarter level of Time, and the cube contains three years of data. The cube has 3*4=12 bottom partitions, JOB_QUEUE_PROCESSES is set to 8, and you set the parallelism option to 4 for the build. Oracle Database processes the cube in this way when PARALLEL_DEGREE_POLICY is set to its default value of MANUAL:

1. Load and build the dimensions of the cube serially using a single process.

2. Load and build the 12 bottom partitions in parallel using 4 processes. As soon as one process finishes, another begins until all 12 are complete.

   This cube could use the 8 processes allowed by JOB_QUEUE_PROCESSES, but it is limited to 4 by the build setting.

3. Load and build the top partition.

When PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED, Oracle Database may allocate more than the designated processes.

SLAVE_NUMBER STATUS     COMMAND              BUILD_OBJECT    PARTITION
------------ ---------- -------------------- --------------- ---------------
           0 STARTED    BUILD
           0 STARTED    ATTACH AW RW WAIT
           0 COMPLETED  ATTACH AW RW WAIT
           0 STARTED    FREEZE
           0 COMPLETED  FREEZE
           0 STARTED    LOAD NO SYNCH        TIME
           0 SQL        LOAD NO SYNCH        TIME
               .
               .
               .
           0 SQL        LOAD NO SYNCH        PRODUCT
           0 SQL        LOAD NO SYNCH        PRODUCT
           0 COMPLETED  LOAD NO SYNCH        PRODUCT
           0 STARTED    COMPILE              PRODUCT
           0 COMPLETED  COMPILE              PRODUCT
           0 STARTED    COMPILE AGGMAP       UNITS_CUBE
           0 COMPLETED  COMPILE AGGMAP       UNITS_CUBE
           0 STARTED    COMPILE AGGMAP       PRICE_CUBE
           0 COMPLETED  COMPILE AGGMAP       PRICE_CUBE
           0 STARTED    UPDATE/COMMIT        PRODUCT
           0 COMPLETED  UPDATE/COMMIT        PRODUCT
           0 STARTED    UPDATE/COMMIT
           0 COMPLETED  UPDATE/COMMIT
           0 STARTED    REATTACH AW MULTI TH
                        AW
 
           0 COMPLETED  REATTACH AW MULTI TH
                        AW
 
           0 STARTED    SLAVE                UNITS_CUBE      P10:CY2007
           0 STARTED    SLAVE                UNITS_CUBE      P9:CY2006
           1 STARTED    BUILD                                P10:CY2007
           1 STARTED    ATTACH AW MULTI THAW UNITS_CUBE      P10:CY2007
           1 COMPLETED  ATTACH AW MULTI THAW UNITS_CUBE      P10:CY2007
           1 STARTED    ACQUIRE              UNITS_CUBE      P10:CY2007
           1 COMPLETED  ACQUIRE              UNITS_CUBE      P10:CY2007
           1 STARTED    LOAD                 UNITS_CUBE      P10:CY2007
           1 SQL        LOAD                 UNITS_CUBE      P10:CY2007
           1 COMPLETED  LOAD                 UNITS_CUBE      P10:CY2007
           1 STARTED    UPDATE/COMMIT        UNITS_CUBE      P10:CY2007
           1 COMPLETED  UPDATE/COMMIT        UNITS_CUBE      P10:CY2007
               .
               .
               .
          10 STARTED    BUILD                                P1:CY1998
          10 STARTED    ATTACH AW MULTI THAW UNITS_CUBE      P1:CY1998
          10 COMPLETED  ATTACH AW MULTI THAW UNITS_CUBE      P1:CY1998
          10 STARTED    ACQUIRE              UNITS_CUBE      P1:CY1998
          10 COMPLETED  ACQUIRE              UNITS_CUBE      P1:CY1998
          10 STARTED    LOAD                 UNITS_CUBE      P1:CY1998
          10 SQL        LOAD                 UNITS_CUBE      P1:CY1998
          10 COMPLETED  LOAD                 UNITS_CUBE      P1:CY1998
          10 STARTED    SOLVE                UNITS_CUBE      P1:CY1998
          10 COMPLETED  SOLVE                UNITS_CUBE      P1:CY1998
          10 STARTED    UPDATE/COMMIT        UNITS_CUBE      P1:CY1998
          10 COMPLETED  UPDATE/COMMIT        UNITS_CUBE      P1:CY1998
          10 STARTED    DETACH AW            UNITS_CUBE      P1:CY1998
          10 COMPLETED  DETACH AW            UNITS_CUBE      P1:CY1998
          10 COMPLETED  BUILD                                P1:CY1998
           0 COMPLETED  SLAVE                UNITS_CUBE      P1:CY1998
           0 STARTED    REATTACH AW MULTI TH
                        AW
 
           0 COMPLETED  REATTACH AW MULTI TH
                        AW
 
           0 STARTED    SLAVE                UNITS_CUBE      P0
          11 STARTED    BUILD                                P0
          11 STARTED    ATTACH AW MULTI THAW UNITS_CUBE      P0
          11 COMPLETED  ATTACH AW MULTI THAW UNITS_CUBE      P0
          11 STARTED    ACQUIRE              UNITS_CUBE      P0
          11 COMPLETED  ACQUIRE              UNITS_CUBE      P0
          11 STARTED    LOAD                 UNITS_CUBE      P0
          11 COMPLETED  LOAD                 UNITS_CUBE      P0
          11 STARTED    SOLVE                UNITS_CUBE      P0
          11 COMPLETED  SOLVE                UNITS_CUBE      P0
          11 STARTED    UPDATE/COMMIT        UNITS_CUBE      P0
          11 COMPLETED  UPDATE/COMMIT        UNITS_CUBE      P0
          11 STARTED    DETACH AW            UNITS_CUBE      P0
          11 COMPLETED  DETACH AW            UNITS_CUBE      P0
          11 COMPLETED  BUILD                                P0
           0 COMPLETED  SLAVE                UNITS_CUBE      P0
           0 STARTED    REATTACH AW RW WAIT
           0 COMPLETED  REATTACH AW RW WAIT
           0 STARTED    ANALYZE              UNITS_CUBE
           0 COMPLETED  ANALYZE              UNITS_CUBE
           0 STARTED    THAW
           0 COMPLETED  THAW
           0 STARTED    DETACH AW
           0 COMPLETED  DETACH AW
           0 COMPLETED  BUILD
 
268 rows selected.

The following queries extract OLAP information from the data dictionary. You must have a privileged account to query the DBA views.

More complex queries are provided in a script that you can download from the Oracle OLAP website on the Oracle Technology Network. For descriptions of these scripts and download instructions, refer to "OLAP DBA Scripts".

You can download a file that contains several SQL scripts from the Oracle OLAP website on the Oracle Technology Network. These scripts typically extract information from two or more system views and generate a report that may be useful in monitoring and tuning a database. To download the file, use this URL:

http://www.oracle.com/technetwork/database/options/olap/olap-dba-scripts-393636.zip

Table 7-6 describes these scripts. For more information, refer to the README file provided with the scripts.

Several of the scripts listed in "OLAP DBA Scripts" provide detailed information about the use of memory and other database resources by OLAP sessions. You can use these scripts as is, or you can use them as the starting point for developing your own scripts.

Example 7-3 shows the information returned by the session_resources script. It lists the use of resources such as cursors, PGA, and UGA.

@session_resources
 
USERNAME             NAME                                VALUE
-------------------- ------------------------------ ----------
GLOBAL:86            opened cursors cumulative             621
                     opened cursors current                 18
                     session cursor cache count             50
                     session cursor cache hits             432
                     session pga memory                5356368
                     session pga memory max           10468176
                     session stored procedure space          0
                     session uga memory                4230692
                     session uga memory max            7049780
 
9 rows selected.

Several of the scripts listed in "OLAP DBA Scripts" provide detailed information about the use of disk space by analytic workspaces. Example 7-4 shows the information returned by the aw_size script. It lists all of the analytic workspaces in the database, the disk space they consume, and the tablespaces in which they are stored.

@aw_size
 
Analytic Workspace                            On Disk MB Tablespace
---------------------------------------- --------------- --------------------
GLOBAL.GLOBAL                                     249.31 GLOBAL
SYS.AWCREATE                                        7.81 SYSAUX
SYS.AWCREATE10G                                     1.63 SYSAUX
SYS.AWMD                                            8.00 SYSAUX
SYS.AWREPORT                                        1.63 SYSAUX
SYS.AWXML                                          18.00 SYSAUX
SYS.EXPRESS                                         2.25 SYSAUX
                                         ---------------
Total Disk:                                       288.63
 
7 rows selected.

Templates are XML documents that describe dimensional objects. You can save the XML descriptions of all the objects in an analytic workspace or just selected objects, and re-create them later in the same database or in a database on another computer or platform. You can use templates to back up your work while developing a dimensional model of your data or to distribute the design to other users.

You can save the XML definitions of the following types of objects:

• Analytic workspace: Saves all dimensional objects and all user-defined OLAP DML programs and objects.

• Dimension: Saves the dimension and its levels, hierarchies, attributes, and mappings.

• Cube: Saves the cube and its measures, calculated measures, dimensions, mappings, and all user-defined OLAP DML programs and objects associated with the cube.

• Measure Folder: Saves a list of the measures in the measure folder. It does not save the objects.

Templates store metadata, not data. You can store templates in a small text file or in a database table. When re-creating objects from a template, you must have access to the source data.

You can export objects in an analytic workspace to an EIF file. EIF files are specially formatted files for copying analytic workspaces. You can use EIF files to:

• Backup individual analytic workspaces

• Copy an analytic workspace to another database

EIF files are upwardly compatible among releases of Oracle Database. An EIF file saves the definitions of OLAP DML objects and optionally saves the data also. When you create an EIF file, you can save only the data that you have permission to access.

EIF files do not save object security rules.

You can export and import EIF files for analytic workspaces. You can use EIF files at a more granular level, such as saving just your custom programs, using the OLAP DML.

The data dictionary contains numerous static views that provide information about materialized views. They list cube materialized views along with all other materialized views.

You can initiate a data refresh of a cube materialized view in several different ways using Analytic Workspace Manager or a PL/SQL package:

• Automatic Refresh: On the Materialized View tab for a cube, you can create a regular schedule for the materialized view refresh subsystem, as described in "Adding Materialized View Capability to a Cube".

• Maintenance Wizard: The Maintenance Wizard is available for refreshing all cubes and dimensions, including cube materialized views.

• DBMS_CUBE: The DBMS_CUBE PL/SQL package is available for refreshing all cubes, cube dimensions, and cube materialized views.

• DBMS_MVIEW: The DBMS_MVIEW PL/SQL package contains several procedures for use with cube materialized views.

In Analytic Workspace Manager, you can specify the COMPLETE, FAST, or FORCE methods for refreshing a cube. Two additional methods, FAST_PCT and FAST_SOLVE, are invoked by the materialized view subsystem. They are not separate choices.

Query rewrite changes a query to select data from the materialized views instead of calculating the result set from the master tables. The transformation is fully transparent to the client, and requires no mention of the materialized views in the SQL statement. In the case of cube materialized views, the query is written against the tables or views of a star or snowflake schema, and it is transformed into a query against a cube materialized view. This transformation can result in significant improvements in run-time performance.

Query rewrite requires optimizer statistics on the cubes and dimensions. You can discover whether a query is rewritten by generating and examining its execution plan.

Oracle Database uses two initialization parameters to control query rewrite:

• QUERY_REWRITE_ENABLED: Enables or disables query rewrite globally for the database.

• QUERY_REWRITE_INTEGRITY: Determines the degree to which query rewrite monitors the consistency of materialized views with the source data. The trusted or stale tolerated settings are recommended when using rewrite to cube materialized views.

Administration of cube materialized views is the same as any other materialized view except that the cube materialized views must be in the same schema as the analytic workspace. Users require the GLOBAL QUERY REWRITE privilege to have rewrite to materialized views that are in schemas other than their own. However, the owner can access the materialized views from any schema without additional privileges.