2 Getting Started with Oracle OLAP

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/sampleschemasfordoc11g.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 must create or manage dimensional objects in Oracle Database needs 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 are 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.

CREATE USER "GLOBAL" IDENTIFIED BY password
   DEFAULT TABLESPACE glo 
   TEMPORARY TABLESPACE glotmp
   QUOTA UNLIMITED ON glo
   PASSWORD EXPIRE;

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 learn how to install Analytic Workspace Manager software and make a connection to Oracle Database.

Opening Analytic Workspace Manager

Use the appropriate procedure for your platform.

On Windows, to 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, to open Analytic Workspace Manager: 

• From the shell command line, enter this command:

  $ORACLE_HOME/olap/awm/awm.sh
  

Figure 2-1 shows the initial display.

Figure 2-1 Opening Analytic Workspace Manager

Description of "Figure 2-1 Opening Analytic Workspace Manager"

If Analytic Workspace Manager does not have access to the Internet, the property viewer shows links to several useful sites. It also shows an exception, because Analytic Workspace Manager cannot display the OLAP home page. To connect to the Internet, you typically need to identify the proxy server.

To identify the proxy server: 

1. From the Tools menu, choose Configuration to display the Configuration dialog box.

2. Under OLAP Home Page Settings, enter the address of the proxy server.

3. Enter the port number for the proxy server, if it is not default port 80.

4. Click OK to save these settings. The OLAP Home page will be displayed the next time you start Analytic Workspace Manager.

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: 

1. Right-click the top Databases folder in the navigation tree, then choose New Database Connection from the shortcut menu.

2. Complete the New Database Connection dialog box.

Figure 2-2 shows the connection information on the General tab of the New Database Connection dialog box.

Figure 2-2 Defining a Database Connection

Description of "Figure 2-2 Defining a Database Connection"

Upgrading Metadata From Oracle OLAP 10g

You can upgrade an Oracle OLAP 10g analytic workspace to OLAP 11g by saving the objects as an XML template and importing the XML into a different schema. The original analytic workspace remains accessible and unchanged by the upgrade process.

Prerequisites: 

• The OLAP 10g analytic workspace can use CWM metadata or OLAP standard form metadata.

• The original relational source data must be available to load into the new analytic workspace. If the data is in a different schema or the table names are different, then you must remap the dimensional objects to the new relational sources after the upgrade.

• You can create the OLAP 11g analytic workspace in the same schema as the OLAP 10g analytic workspace. However, if you choose to create the OLAP 11g analytic workspace in a different schema, you must grant the new user the appropriate privileges as described in "Granting Privileges to DBAs and Application Developers".

To upgrade an OLAP 10g analytic workspace: 

1. Open Analytic Workspace Manager for Oracle Database 11g Release 2.

2. If necessary, create a new database connection to the database instance with the analytic workspace. See "Defining a Database Connection".

3. Open the database connection. On the Connect to Database dialog box, select OLAP 10g for the Cube Type. See "Opening a Database Connection".

4. Expand the navigation tree until the name of the analytic workspace is displayed.

5. Right-click the analytic workspace and select Create 11g Upgrade Template for 10g Analytic Workspace. Save the XML template to a file.

   The Create 11g Upgrade Template for 10g Analytic Workspace dialog box is displayed if any subobjects, such as a level and a hierarchy, have the same name. This practice was permitted in OLAP 10g, but invalidates the namespace requirements in OLAP 11g.

   Duplicate object names are changed automatically for the upgrade. You cannot edit the names now, but you can change them later.

6. Click Close to close the dialog box.

7. Right-click the connection in the tree and choose Disconnect Database.

8. Right-click the connection again and choose Connect Database.

9. On the Connect to Database dialog box, log in with the new user name and select OLAP 11g for the Cube Type.

10. Expand the tree, right-click Analytic Workspaces under the new schema, and choose Create Analytic Workspace From Template.

11. Open the upgrade template that you created previously.

    The Correct Duplicate Names From Analytic Workspace Template Import dialog box is displayed if any objects, such as a cube, dimensions, or the analytic workspace, duplicate object names that already exist in the schema.

12. Enter new names to resolve any conflicts, then click OK.

13. Before loading the data, you may want to browse the dimensional objects and make any changes to the object names, cube partitioning, or aggregation strategy. See "What's New in Oracle OLAP?" for a summary of new features in Oracle OLAP 11g.

14. Load data into the new analytic workspace as described in "Loading Data Into Cubes". Select all objects for maintenance.