OLAP Setup and User's Guide

Installing DB2 OLAP Integration Server

DB2 OLAP Integration Server is installed as a component of the OLAP Starter Kit, but is an optional add-on for the DB2 OLAP Server.

To install the DB2 OLAP Integration Server as an add-on component:

  1. Install the OLAP Integration Server from the CD-ROM.

    If you selected the OLAP Integration Server component during installation, it will automatically be installed to the default directory, x:\hyperion\is unless you specify a different drive or directory. The environment settings can be updated automatically, or you can update them manually. See "Manually Updating Environment Variables".

  2. Configure a data source using ODBC. See Loading and Configuring ODBC for the SQL Interface.
  3. Create an OLAP Metadata catalog. See About OLAP Metadata Catalogs.
  4. Connect to servers and relational data sources. See Connecting to Servers and Relational Data Sources.

Connecting to Servers and Relational Data Sources

To use the OLAP Integration Server to create OLAP models and metaoutlines, you must connect the client software to the servers: OLAP Integration Server and DB2 OLAP Server. You must also connect to a relational data source, and to an OLAP metadata catalog where you want to store the OLAP models and metaoutlines that you create. Two metadata catalogs are created and configured as a relational data source during installation:
OLAPCATP (for production)
OLAPCATD (for development)

On the OLAP Starter Kit, these two metadata catalogs are created automatically during a typical or custom install.

To use OLAP Integration Server on Sybase, you must have execute permission for sp-fkeys in Sybsystemprocs.

Summary of SQL Scripts to Manage Catalogs and Tables

Use the following SQL scripts to create, drop, or upgrade the OLAP metadata catalog and tables:
To build catalogs use: ocdb2.sql
To drop catalogs use: ocdrop_db2.sql
To upgrade catalogs use: ocdb2_upgrd20.sql

If you need to rebuild tables, you must first drop the tables before rebuilding them.

About OLAP Metadata Catalogs

An OLAP metadata catalog is a relational database that contains tables in which OLAP Integration Server stores OLAP models and metaoutlines. An OLAP metadata catalog must be configured before you can connect to it.

The RDBMS for an OLAP metadata catalog can run on any supported platform as long as you have the Open Database Connectivity (ODBC) driver and database client software to connect to it from the workstation that runs OLAP Integration Server. The RDBMS for the relational data source that you use to create OLAP models and build metaoutlines also can run on any supported operating system, if you have the ODBC driver and database client software to access it. The RDBMS for the OLAP metadata catalog can be different from the RDBMS for the relational data source, and the platforms for the two RDBMSs do not need to be the same. You can have more than one OLAP metadata catalog, but you cannot move OLAP models and metaoutlines from one OLAP metadata catalog to another.

To make these connections, you must first map each data source to a supported ODBC driver. All configuration of ODBC data sources is done only on the workstation that runs OLAP Integration Server. On Windows, configure ODBC drivers using the ODBC Administrator. If a supported ODBC driver is already mapped to the relational data source, do not map it again. Simply map a supported ODBC driver to the OLAP metadata catalog.

The following RDBMSs are supported by DB2 OLAP Server, but not by the DB2 OLAP Starter Kit:

Configure the ODBC connections to the relational data source and the OLAP metadata catalog only on the workstation that is running OLAP Integration Server. You do not need to configure ODBC connections on OLAP Integration Server client workstations.

Manually Creating an OLAP Metadata Catalog for DB2

You must have database administrator or similar access privileges required by the RDBMS to create a database.

To create an OLAP metadata catalog:

  1. Create a database for the OLAP metadata catalog tables
    1. Create a database with 30 MB for storage.
    2. Create user names and passwords for the database.
    3. Grant user privileges for the database.
  2. Create tables for the OLAP metadata catalog

    The SQL scripts that you use to create tables for the OLAP metadata catalog are in the ocscript directory where you installed OLAP Integration Server. In the DB2 Command Center, run the db2 -tvf utility to perform the following steps:

    1. Connect to the database that you created for the OLAP metadata catalog.
    2. Run the ocdb2.sql SQL script to build the catalog.
    3. Run the ocdatabase_name.sql to build the tables in the catalog.
    4. Verify that the tables have been created by either starting the RDBMS and verifying that the OLAP metadata catalog contains the new tables, or enter a select command such as SELECT * FROM JOIN_HINTS.
    5. Close the utility.
    6. Map the catalog to an ODBC driver. See "Loading and Configuring ODBC for the SQL Interface".

      If you try to access an SQL Server database with the Microsoft native ODBC driver without access permission, Microsoft SQL Server connects you to the default database without notifying you.

Updating the Environment for the Database Client

For the OLAP Integration Server, you need to set the environment variables required for database client software in the login script of the user who runs OLAP Integration Server. These environment settings are required for ODBC access to the databases in the RDBMS that you are using. The database vendor typically supplies a shell script to set any environment variables required by the database client. Add this shell script to the login script for the user who runs the OLAP Integration Server software. For more information about how to set environment variables for the database client, see the database client installation documentation.

To verify that the database client software is set up correctly, log on as the user who runs OLAP Integration Server, and use a database utility to connect to the databases that you use with OLAP Integration Server.

OLAP Integration Server Directory Structure


Table 5. OLAP Integration Server directory structure
Directory Structure Description
\<installation directory>\IS\
bin OLAP Integration Server and OLAP Command Interface software. If you also install client software, the OLAP Integration Server Desktop software is in the bin directory. A server log file, created when you first run OLAP Integration Server, is also in the bin directory.
esscript Empty. The esscript directory will contain calculation and ESSCMD scripts you create.
esslib Three subdirectories: esslib\bin, esslib\client, and esslib\locale.
loadinfo Empty. The loadinfo directory will contain folders that represent session IDs, which will contain the reject files created during a member load or data load.
locale National Language support files.
ocscript SQL script files to create, drop, and upgrade tables for an OLAP metadata catalog in each of the supported databases. For example, you can use ocdb2.sql to create OLAP metadata catalog tables.
samples Two subdirectories: samples\tbcdbase and samples\tbcmodel.
samples\tbcdbase SQL script files, batch files, and text files to create tables and load data for the TBC sample application database.
samples\tbcmodel SQL script files, batch files, and text files to load data for OLAP model (TBC Model) and metaoutline (TBC Metaoutline) for the TBC_MD sample application OLAP metadata catalog database. You create tables for TBC_MD with an SQL script file in the ocscript directory.

[ Top of Page | Previous Page | Next Page ]