2 Administering Discoverer Systems that Access Multidimensional Data Sources

This chapter introduces you to administering a Discoverer system that accesses a multidimensional data source, and includes the following topics:

2.1 What are the prerequisites for administering Discoverer systems that access multidimensional data sources?

Before Discoverer end users can access a multidimensional data source, you must perform the following tasks:

  • Install the Enterprise Edition of the Oracle9i database or the Oracle Database 10g database with the OLAP Option and install the appropriate database patches.

    For more information, see the Oracle9i or Oracle Database installation guide for your platform and the guide that was used when installing Discoverer.

  • Provide access to data from the OLAP Catalog, which defines logical multidimensional objects. The OLAP Catalog is mapped either to a relational database schema or to an analytic workspace.

    An analytic workspace stores multidimensional data objects and procedures that are written in the OLAP DML. The OLAP DML is a data manipulation language that is understood by the Oracle OLAP calculation engine. The OLAP DML extends the analytic capabilities of querying languages such as SQL and the OLAP API to include forecasting, modeling, and what-if scenarios.

    For more information, see Oracle9i OLAP User's Guide or Oracle OLAP Application Developer's Guide.

2.2 What is online analytic processing (OLAP)?

Online analytic processing (OLAP) describes queries that are run against a multidimensional data source. A simple query might ask, "When did order 84305 ship?" A typical series of OLAP queries might ask, "How do sales in the Southwestern region for this quarter compare with sales a year ago? What can we predict for sales next quarter? What factors can we alter to improve the sales forecast?"

Most standard queries involve simple data selection and retrieval. However, OLAP queries are more structured and involve calculations, time series analysis, and quick access to aggregated historical and current data. In OLAP queries, you know the dimensions and hierarchies that measures use. You query the data in business terms, such as "How are my products selling in my regions?" Each dimension has its own selection that mirrors the way that you ask questions.

For more information about OLAP, see:

  • Oracle9i OLAP User's Guide

  • Oracle OLAP Application Developer's Guide

  • Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus

2.3 What is a multidimensional data source?

A multidimensional data source is optimized for analyzing large amounts of data. Such data sources are sometimes called data warehouses, or online analytical processing (OLAP) data sources.

In a relational data source, data is organized in tables. A table is a data structure with columns and rows. Multidimensional data is data that is organized by one or more dimensions. These multidimensional data structures are often referred to as cubes. Oracle databases (version 9.2.0.7 or later) can include both relational data structures (tables and columns) and multidimensional data structures (cubes). This combination provides fast multidimensional data access while providing summaries of the relational data.

2.4 What is the Discoverer Catalog?

The Discoverer Catalog is a repository for storing and retrieving definitions of objects for Discoverer Plus OLAP. The Discoverer Catalog can be used by applications that are built with Oracle Business Intelligence Beans (BI Beans). With Discoverer Plus OLAP, end users store in the Discoverer Catalog objects such as workbooks, calculations, and saved selections and share objects with others who have access to the Discoverer Catalog. For example, in a BI Beans application, a user can create a graph and store it in the Discoverer Catalog. If another user has appropriate access, that user can retrieve the graph that was stored in the Discoverer Catalog and insert the graph into a new worksheet in Discoverer Plus OLAP.

The Discoverer Catalog provides security at the object level, by allowing users and system administrators to specify access to certain objects.

Note: Do not confuse the Discoverer Catalog, which contains object definitions, with the OLAP Catalog. The OLAP Catalog defines logical multidimensional objects that can be mapped either to columns in the tables of a star or snowflake schema or to multidimensional objects in an analytic workspace.

For complete information on installing and administering the Discoverer Catalog, see Oracle Fusion Middleware Configuration Guide for Oracle Business Intelligence Discoverer.

2.5 How do I administer a Discoverer system that accesses multidimensional data sources?

This section provides information on your role as the manager of Discoverer system that accesses a multidimensional data source, on the tools you use, and on the differences between administering for multidimensional and relational data sources.

2.5.1 About managing a Discoverer system that accesses multidimensional data sources

As a Discoverer manager, you are responsible for the initial implementation of Discoverer Plus OLAP and its ongoing administration and maintenance. To fulfill your role as a Discoverer manager and to support your company's decision-makers when using a multidimensional data source, you must:

  • Understand the database: You must know what data is in the database, where the data is located, how the data is stored (whether it is stored in a relational database schema or analytic workspace), how its metadata is configured, and how the data relates to other data.

  • Understand the business: You must know what data the decision-makers require, what kinds of analysis they perform, and how the final results should be presented for easy comprehension.

2.5.2 About administering Discoverer for multidimensional and relational data sources

Before administering a Discoverer system that accesses a multidimensional data source, you should understand the differences from administering a Discoverer system that accesses a relational data source, as described in the following list:

  • When you use Discoverer with a multidimensional data source, you do not use Discoverer Administrator to perform administrative tasks. Instead, you use the tools that are outlined in "Which tools are used to administer Discoverer systems that access multidimensional data sources?"

  • When you use Discoverer with a relational data source, Discoverer uses the End User Layer (EUL). When you use Discoverer with a multidimensional data source, Discoverer does not use the End User Layer (EUL) but uses the Discoverer Catalog.

  • The following tasks are typically performed for a relational data source and not for a multidimensional data source:

    • Scheduling workbooks

    • Creating and maintaining joins

    • Creating and maintaining summary folders

    • Predicting query performance

For more information on the differences between using Discoverer with a relational data source and using Discoverer Plus OLAP, see Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Discoverer Plus.

2.5.3 Which tools are used to administer Discoverer systems that access multidimensional data sources?

When you administer a Discoverer system that accesses a multidimensional data source, you perform the following administrative tasks using the specified tools:

  • You define OLAP Catalog metadata using Oracle Analytic Workspace Manager or Oracle Warehouse Builder. You define the metadata differently depending on whether you are using ROLAP (Relational Online Analytic Processing) or MOLAP (Multidimensional Online Analytic Processing):

    • In ROLAP, the OLAP Catalog is mapped to a relational database schema.

    • In MOLAP, the OLAP Catalog is mapped to an analytic workspace.

    For more information on which definition is appropriate for your data and on configuration details, see Oracle9i OLAP User's Guide or Oracle OLAP Application Developer's Guide.

  • You configure the Discoverer Catalog, using Oracle Fusion Middleware Control to perform the following tasks:

    • Installing the Discoverer Catalog

    • Controlling access to information

    • Importing and exporting objects for the Discoverer Catalog

    • Customizing the logo and look and feel

    • Specifying the log level

    • Specifying URL parameters for the Discoverer Plus OLAP Servlet

    For more information, see Oracle Fusion Middleware Configuration Guide for Oracle Business Intelligence Discoverer.

  • You maintain dimensions and measures, using Oracle Analytic Workspace Manager.

    For more information, see Oracle9i OLAP User's Guide or Oracle OLAP Application Developer's Guide.

  • You maintain dimension hierarchies, using Oracle Analytic Workspace Manager or Oracle Warehouse Builder.

    For more information, see Oracle9i OLAP User's Guide or Oracle OLAP Application Developer's Guide.

  • You create and maintain calculations and conditions in Discoverer Plus OLAP.

    For more information, see the Discoverer Plus OLAP Help system.

2.6 How do I assist users of Discoverer Plus OLAP in getting started?

As the Discoverer manager, you can assist users of Discoverer Plus OLAP in two important ways, as described in the following sections:

2.6.1 How do I enable access to the sample workbook?

Discoverer Plus OLAP is shipped with a sample workbook that demonstrates and explains many of the product's powerful features. To enable users to access the sample workbook, you must install the Discoverer Catalog and the sample workbook. You can find a document that describes this installation process in the Discoverer area of Oracle Technology Network.

2.6.2 How do I create a preprocessor program?

As the Discoverer manager, you can create a preprocessor program that assists users in performing specific tasks before Discoverer Plus OLAP or Discoverer Viewer starts.

2.6.2.1 Why create a preprocessor program?

Users of Discoverer Plus OLAP and Discoverer Viewer might find it useful to run a preprocessor program before the product is started. You can specify preprocessing tasks by entering PL/SQL code and defining the D4O_AUTOGO stored procedure in the D4OSYS schema. This stored procedure runs automatically whenever Discoverer Plus OLAP and Discoverer Viewer is started.

You can use the D4O_AUTOGO stored procedure to perform tasks such as the following ones:

  • Execute SQL commands, in a ROLAP implementation.

  • Call a routine in the analytic workspace using the AW.EXECUTE command in SQL, in a MOLAP implementation. You can use this routine to perform tasks such as the following:

2.6.2.2 Arguments for the D4O_AUTOGO stored procedure

The D4O_AUTOGO stored procedure accepts only one argument, which is a text value for the user ID of the user who is logging in. Discoverer Plus OLAP and Discoverer Viewer pass the user ID of the current user to the D4O_AUTOGO stored procedure, if the procedure has been defined.

2.6.2.3 Example of creating a preprocessor program

One example of a preprocessor task is to scope the set of data values that users are permitted to analyze. For example, in the sample workbook, the Scott user is not allowed to see the forecasted data for future time periods (such as 2005). To set up this type of security, you must specify an environment for scoping data using the D4O_AUTOGO stored procedure.

The data used for the sample workbook is stored in an Oracle OLAP analytic workspace named shaw. In this workspace, a stored procedure named scope_data is defined that scopes the time periods based on the current database user ID. The scope_data stored procedure is defined as follows:

DEFINE SCOPE_DATA PROGRAM
PROGRAM
"Scopes data based on user id
trap on error noprint
aw attach CS_OLAP.SHAW first
"***
"* shawt_time_isvisible specifies for each user the time periods that are
 visible.
"* The SCOTT user does not have access to 2002 forecast data
"***
limit shawusers to upcase(userid)
 
cns shawt_time
    permit read when shawt_time_isvisible
error: 
   return 
END

To run the scope_data stored procedure, you can define the D4O_AUTOGO stored procedure as follows:

CREATE OR REPLACE PROCEDURE D4OSYS.D4O_AUTOGO (user_id IN VARCHAR) is
     BEGIN
     dbms_aw.aw_attach('CS_OLAP', 'SHAW');
     dbms_aw.execute('scope_data');
     END;