Configuring OLAP Sample Schema and OLAP PDS for Oracle Reports

An Oracle ReportsTechnical Note
July 2003

| Introduction | Installing OLAP Sample Schema in Oracle9i Database | Installing OLAP Pluggable Data Source (OLAP PDS) | Running OLAP-based Reports | General Issues | Related Materials |

Introduction

Using Oracle Reports, you can generate reports based on data from any data source, called "pluggable data sources." For more information on pluggable data sources, refer to "Getting Started with Oracle Reports" on the Oracle Technology Network (OTN). This paper discusses the OLAP pluggable data source, which enables you to access OLAP data stored in an Oracle9i Database (Release 2). Here, you will learn:

1. How to install the OLAP Schema in an Oracle9i Database (Release 2)
2. How to configure the OLAP pluggable data source (PDS) in an existing Oracle Developer Suite installation
3. How to run reports built using the OLAP PDS.

Note: You can also view a demonstration that shows how to build a report based on OLAP data using Oracle Reports.

Back to Top

Installing OLAP Schema in Oracle9i Database

This section discusses how to install an OLAP schema into an Oracle9i database (Release 2).

OLAP Schema Installation Requirements

1. Make sure you have installed Oracle9i database Release 2 (9.2.0.1.x).
2. If you have not already done so, apply the latest available patch set to the database installation. The latest patch set is currently 9.2.0.3 (patch number 2761332). Note: All patches mentioned in this document can be downloaded from Metalink.
3. Follow the instructions in patch 2529822 for configuring the database for OLAP. The document for this patch provides techniques and practices designed to optimize the performance of typical OLAP operations.

OLAP Schema Installation Steps

1. Define the appropriate OLAP metadata, as described in the Oracle9i OLAP User's Guide Release 2, which is part of the documentation set for Oracle9i. You can also refer to the online help system for the OLAP management tool of Oracle Enterprise Manager, which is the tool that you use to create the metadata. Note: This step is only relevant if you are defining your own OLAP data. If you just want to install the sample schema, you can skip this step, and directly go to Step 2.
2. Populate the database with the sample schema and catalog by following these steps:
   1. Install the Business Intelligence Beans (BI Beans) by following these instructions.
   2. Install the Demo Database Schema by following these instructions.
   3. Create and configure the BI Beans catalog by following these instructions.

Once you have succesfully installed the OLAP schema in your Oracle9i database and it contains the necessary data, you are ready to install the OLAP PDS.

Back to Top

Installing OLAP Pluggable Data Source (OLAP PDS)

OLAP PDS Installation Requirements

1. Make sure you have installed Oracle9iDS Release 2 (9.0.2.0.x) or Oracle Reports 10g.
2. If you have not already done so, apply the latest available patch set to the Oracle9iDS installation:
   1. Forms and Reports Patch 9.0.2.3.0 (patch number 3129219).
   2. Patch number 2517919.
3. If you plan to use Oracle9iAS for running OLAP-based reports, make sure you have applied the latest available patch sets to the Oracle9iAS installation, namely:
1. 9.0.2.2 Patchset for 9iAS Core (patch number 2703110).
2. Forms and Reports Patch 9.0.2.3.0 (patch number 3129219).
3. Patch number 2517919.

Note: Do not install the database client into the Oracle9iAS ORACLE_HOME.

OLAP PDS Installation Steps

1. Download the olappds.zip Release 2 (9.0.2).
2. Extract the files in olappds.zip to your Oracle9iDS or Oracle Developer Suite 10g home. Note: Check the Use Folder Names check box in WinZip.
3. Go to the directory that contains the extracted files and execute SetupPDS.exe. For example:
   <ORACLE_HOME>/reports/bibeans27/ReadMe/SetupPDS.exe

You have now completed configuring the OLAP PDS, and can use the OLAP data to build and run reports in Oracle Reports. For more information on building a report using OLAP data, see the demonstration on the Oracle Technology Network.

To learn how to run an OLAP-based report, see the following section.

Back to Top

Running OLAP-based Reports

You can run a report to the web or to paper in Reports Builder by clicking the Run to Web or Run to Paper buttons in the toolbar. To run an OLAP-based report over the Reports Servlet or from the command line, you must use the following syntax for the OLAP connect string:

OLAP_Con="username/password/hostname/SID/portnumber"

The following list describes the above syntax:

• username: the user ID for connecting to the OLAP schema you installed.
• password: the password for the username.
• hostname: the host name for the database where the OLAP schema is installed.
• SID: the system identifier for connecting to the database where the OLAP schema is installed.
• portnumber: the port number for connecting to the database where the OLAP schema is installed.

For example, you could use the following syntax:
OLAP_Con="jsmith/abcdef/mypc.mycompany.com/JSMYC/9201"

For information on deploying and executing a report using OC4J, see "Running Report Requests" in the Oracle Publishing Reports manual on OTN. You can also follow this Technical Note on OTN. After you've followed the instructions, use the OLAP connect string to run the report. For example:
http://host:8888/reports/MyOLAPReport.jsp?OLAP_Con=jsmith/abcdef/mypc.mycompany.com/JSMYC/9201

Back to Top

General Issues

Catalog issues: In the OLAP PDS online help system, you will notice references to a remote catalog, called the BI Beans Catalog. In the current release of the OLAP PDS (version 9.0.2), only a local catalog is supported, not the remote BI Beans catalog. Once you've installed the OLAP PDS,a local catalog directory is created in the Oracle_Home\reports\bibeans27\catalog directory.

Help issues: When you display the OLAP PDS Connect dialog box or the OLAP Query Builder from within Oracle Reports Builder, you can press the relevant Help buttons to display the context-sensitive help. Although the help topics display in a separate window, the Help window does not respond to any mouse or keyboard actions. To work around this known issue, you can close the Connect dialog box or the OLAP Query Builder, then manipulate the Help window.

Back to Top 

Related Materials

Building a Report Using an OLAP Data Source

The latest patch sets are available on Metalink.

Back to Top