DB2 OLAP Integration Server 7.1

Fixpack 4

Additional instructions for OLAP Integration Server


This file contains the following sections:

ODBC Requirements

Configuration Tips

New Sample Applications
 

ODBC Requirements

Starting with Fixpack 2, IBM DB2 OLAP Integration Server, requires an ODBC.ini file for operation of Open Database Connectivity (ODBC) connections from OLAP Integration Server to the relational data source and to the OLAP Metadata Catalog.

On Windows systems, this file is in the Registry under HKEY_LOCAL_MACHINE/SOFTWARE/ODBC. Use ODBC Data Source Administrator to store information about how to connect to a relational data source.

On UNIX systems, DB2 OLAP Integration Server installation creates a model odbc.ini file. To store information about how to connect to a relational data source, edit the file using your preferred editor.

The ODBC.ini file is available in ODBC software packages and is included with Microsoft Office software. For more information about applications that install ODBC drivers or the ODBC Administrator, visit the following web site:

http://support.microsoft.com/support/kb/articles/Q113/1/08.asp

For Oracle users on AIX and HP-UX machines: To configure ODBC for Oracle, you must update the ODBC.ini file to point to the MERANT 3.6 drivers.

Operating System Requirements:

With Fixpak 2, DB2 OLAP Integration Server manages ODBC connections to the relational data source and to the OLAP Metadata Catalog.  To accommodate these ODBC connections, DB2 OLAP Integration Server software uses ODBC drivers on Windows NT 4.0, Windows 2000, AIX, HP-UX, and Solaris.

Supported ODBC Drivers matrix:

Configuration Tips

This supplements the OLAP Setup and User's Guide for DB2 OLAP Integration Server 7.1.
 

Configuring Data Sources on UNIX Systems

On AIX, HP-UX and Solaris, you must manually set environment variables for ODBC and edit the odbc.ini file to configure the relational data source and OLAP Metadata Catalog.  Note: Edit the odbc.ini file if you add a new driver or data source or if you change the driver or data source.
 

Configuring ODBC Environment Variables

On UNIX systems, you must set environment variables to enable access to ODBC core components. The is.sh and is.csh shell scripts that set the required variables are provided in the DB2 OLAP Integration Server home directory. You must run one of these scripts before using ODBC to connect to data sources. You should include these scripts in the login script for the user name you use to run OLAP Integration Server.
 

Editing the odbc.ini File

To configure a data source in an odbc.ini file, you must add a name and description for the ODBC data source and provide the ODBC driver path, file name, and other driver settings in a separate section that you create for the data source name. The DB2 OLAP Integration Server installation program installs a sample odbc.ini file in the ISHOME directory. The file contains generic ODBC connection and configuration information for supported ODBC drivers. Use the file as a starting point to map the ODBC drivers that you use to the relational data source and OLAP Metadata Catalog.

Note: If you use a different file than the odbc.ini file, be sure to set the ODBCINI environment variable to the name of the file you use.
 

To add a data source to an odbc.ini file:

  1. On the computer running OLAP Integration Server, open the odbc.ini file by using a text editor such as vi.
  2. Find the section starting with [ODBC Data Sources] and add a new line with the data source name and description; for example:  mydata=data source for analysis.  To minimize confusion, the name of the data source should match the name of the database in the RDBMS.
  3. Add a new section to the file by creating a new line with the name of the new data source enclosed in brackets; for example:   [mydata]
  4. On the lines following the data source name, add the full path and file name for the ODBC driver required for this data source and any other required ODBC driver information. Use the examples shown in the following sections as a guideline to map to the data source on your RDBMS.  Note: Make sure that the ODBC driver file actually exists in the location you specify for the Driver= setting.
  5. When you have finished editing odbc.ini, save the file and exit the text editor. For more information about the odbc.ini file and the ODBC driver settings for each RDBMS, see the MERANT DataDirect Connect ODBC Reference in PDF format and the related Readme files in the ODBCDocs directory. For more information about native ODBC driver settings, refer to the installation documentation for your RDBMS native ODBC drivers.

Example of ODBC Settings for DB2

Following is an example of how you might edit odbc.ini to connect to a relational data source, db2data, on DB2 Version 6.1 (on AIX), using an IBM DB2 native ODBC driver.

Use the vi $ODBCINI command to edit the odbc.ini file and insert the following statements:

[ODBC Data Sources]
db2data=DB2 Source Data on AIX
...
[db2data]
Driver=/home/db2inst1/sqllib/lib/db2.o
Description=DB2 Data Source - AIX, native

Example of ODBC Settings for Oracle

Here is an example of how you might edit odbc.ini to connect to a relational data source, oradata, on Oracle Version 8 (on Solaris), using a MERANT Version 3.6 ODBC driver.  Note: LogonID and Password in this example are overridden with the actual values used in the Hyperion Integration Server user name and password.
[ODBC Data Sources]
oradata=Oracle8 Source Data on Solaris
...
[myoracle] Driver=
/export/home/users/dkendric/is200/odbclib/ARor815.so
Description=my oracle source
Note: ServerName=mytnsServerName must refer to the name of an Oracle database defined in the tnsnames.ora file. For more information, see the Oracle installation documentation.

Tip: Run ivtestlib to verify that the environment is set to run the correct odbc driver file. For example, run ivtestlib and paste the path and file name that follow Driver= in the odbc.ini file that you have edited.
 

Configuring the OLAP Metadata Catalog on UNIX Systems

Configuring an OLAP Metadata Catalog on AIX, HP-UX, and Solaris is similar to configuring a data source. For the OLAP Metadata Catalog database, add a data source name and section to the odbc.ini file. No other changes are required.

You must create an OLAP Metadata Catalog database in a supported RDBMS before configuring it as an ODBC data source.

Here is an example how you might edit odbc.ini to connect to the OLAP Metadata Catalog, TBC_MD, on DB2 Version 6.1 (on Solaris), using a native ODBC driver:

[ODBC Data Sources]
ocd6a5a=db2 v6
...
[ocd6a5a]
Driver=/home/db2instl/sqllib/lib/db2.0
Description=db2
 

Using ODBC drivers coded in Unicode on Solaris:

On Solaris, the OLAP Integration Server (OIS) Desktop does not support ODBC drivers coded in Unicode.  If you use a Unicode ODBC driver, you might experience problems connecting to the server component.  To enable the connection, update your odbc.ini file and specify a non-Unicode driver, such as libdb2_36.so, under the $ISHOME/odbclib directory.  For example:
[ODBC Data Sources]
TBC_MD=IBM DB2 ODBC DRIVER
TBC=IBM DB2 ODBC DRIVER

[TBC_MD]
Driver=/export/home/arbor7fp/sqllib/lib/libdb2_36.so
Description=Sample DB2 ODBC Database

[TBC]
Driver=/export/home/arbor7fp/sqllib/lib/libdb2_36.so
Description=Sample DB2 ODBC Database

[ODBC]
Trace=0
TraceFile=odbctrace.out
InstallDir=/export/home/arbor7fp/sqllib/lib

Configuring Data Sources on Windows Systems

To configure a relational data source on Windows NT or Windows 2000 systems, you must start ODBC Administrator and then create a connection to the data
source that you will use for creating OLAP models and metaoutlines. Run the ODBC Administrator utility from the Windows Control Panel. Note: This example creates a DB2 data source. The dialog boxes for other RDBMSs will differ.

To configure a relational data source with ODBC Administrator, complete the following steps:
 

  1. On the Windows desktop, select Start > Settings > Control Panel to open the Control Panel window.
  2. In the Control Panel window, perform one of the following steps:
  3. In the ODBC Data Source Administrator dialog box, click the System DSN tab.
  4. Click Add to open the Create New Data Source dialog box.
  5. In the driver list box of the Create New Data Source dialog box of ODBC Administrator, select an appropriate driver—for example, IBM DB2 ODBC Driver—and click Finish to open the ODBC IBMDB2 Driver - Add dialog box.
  6. In the ODBC IBM DB2 Driver - Add dialog box Database alias drop-down list, select the name of the database for your relational source data (for example, TBC in the sample application). The name of the selected database is automatically displayed in the Data Source Name text box.
  7. If you want to change the name of the data source, select the name displayed in the Data Source Name text box, press Enter, and type the name you want to use for the data source.
  8. In the Description text box, type an optional description that indicates how you use this driver and click Add. For example, type the following words to describe the My Business database:
      9. Customers, products, markets
    You might type the following words to describe the sample application database:
      Sample relational data source
    The descriptions help to identify the available data sources for your selection when you connect from Hyperion Integration Server Desktop.
  9. Click OK to return to the ODBC Data Source Administrator dialog box. The data source name you entered and the driver you mapped to it are displayed in the System Data Sources list box on the System DSN tab.

To edit configuration information for a data source:

  1. Select the data source name and click Configure to open the ODBC IBM DB2 - Add dialog box.
  2. Correct any information you want to change.
  3. Click OK twice to exit.

Configuring the OLAP Metadata Catalog on Windows Systems

To configure an OLAP Metadata Catalog on Windows NT or Windows 2000, start ODBC Administrator and then create a connection to the data source that contains the OLAP Metadata Catalog database.

Note: This example creates a DB2 data source. Dialog boxes for other RDBMSs will differ.

To create a data source for the OLAP Metadata Catalog, complete the following steps:
 

  1. On the desktop, select Start > Settings > Control Panel to open the Control Panel window.
  2. In the Control Panel window, perform one of the following steps:
  3. In the ODBC Data Source Administrator dialog box, click the System DSN tab.
  4. Click Add to open the Create New Data Source dialog box.
  5. In the Create New Data Source dialog box of ODBC Administrator, select an appropriate driver—for example, IBM DB2 Driver—in the list box, and click Finish to open the ODBC IBM DB2 Driver - Add dialog box.
  6. In the Database alias drop-down list, select the name of the database for your OLAP Metadata Catalog  (for example, TBC_MD in the sample application).  The name of the selected database is automatically displayed in the Data Source Name text box.
  7. If you want to change the name of the data source, select the name displayed in the Data Source Name text box, type a new name to indicate how you use this driver, and click Add.  For example, you might type the following name to indicate that you are using the driver to connect to the first OLAP Metadata Catalog:
      8. OLAP Catalog #1
      You would type the following name to indicate that you are connecting to the sample application OLAP Metadata Catalog database:
      TBC_MD
  8. In the Description text box, enter a description that indicates how you use this driver. For example, you might type the following words to describe the OLAP Metadata Catalog:
      9. My first models and metaoutlines
      You might type the following words to describe the sample application OLAP Metadata Catalog database:
      Sample models and metaoutlines
      The descriptions help you to identify the catalog that you want to select when you connect to the OLAP Metadata Catalog from Hyperion Integration Server Desktop.
  9. Click OK to return to the ODBC Data Source Administrator dialog box. The data source name you entered and the driver you mapped to it are displayed in the System Data Sources list box on the System DSN tab.


To edit configuration information for a data source:

1. Click Configure to open the ODBC IBM DB2 - Add dialog box.
2. Correct any information you want to change.
3. Click OK twice to exit.
 

After You Configure a Data Source

After you configure the relational data source and OLAP Metadata Catalog, you can connect to them from DB2 OLAP Server. You can then create, modify, and save OLAP models and metaoutlines.

Note: The SQL Server ODBC driver may time out during a call to an SQL Server database. Try again when the database is not busy. Increasing the driver time-out period may avoid this problem. For more information, see the ODBC documentation for the driver you are using.

For more information on ODBC connection problems and solutions, see the DB2 OLAP Integration Server OLAP System Administrator’s Guide.
 
 

Migrating to OLAP Integration Server 7.1

If you are migrating to IBM DB2 OLAP Integration Server 7.1, fixpack 2 or higher, from an earlier release of the Hyperion Solutions product Hyperion Integration Server, you should assess several system environment and data migration issues before installing the new software. This topic explains and recommends solutions for the following migration issues: Read the following topics carefully to assess migration issues that may affect your OLAP Integration Server installation. Note that the following tasks cannot be completed until after you have installed OLAP Integration Server:

Preserving Existing ODBC Driver

Starting with Fixpak 2, OLAP Integration Server 7.1 has a new architecture that concentrates ODBC driver configuration solely on the computer that runs the server component of OLAP Integration Server. With this new architecture, you do not have to configure ODBC drivers for client computers that run OLAP Integration Server Desktop or worry about upgrading ODBC drivers on clients.

If you use the INTERSOLV (now MERANT) Version 3.10 or Version 3.11 ODBC drivers supplied with previous versions of Hyperion Integration Server, you might want to preserve the older drivers for compatibility with the other applications. In general, however, you should upgrade the ODBC drivers to the versions provided with the latest release of OLAP Integration Server.

DB2 OLAP Integration Server 7.1 Fixpak 2 supports MERANT Version 3.6 ODBC drivers; it does not operate with MERANT Version 3.10 or Version 3.11 ODBC drivers.

Install the new MERANT Version 3.6 ODBC drivers and preserve the older drivers by installing OLAP Integration Server to a new location. Be sure that you do not update the ODBC register settings when prompted to do so by the installation program. You can continue to use the previous ODBC definitions.

Choosing an Installation Directory

If you are upgrading from Hyperion Integration Server, you should install the software to the same directory as the Hyperion product to take advantage of the previous environment settings. However, if you want to preserve older ODBC driver settings for other applications, you should install Hyperion Integration Server to a different directory.

Verifying Path Variable Settings

If you are upgrading to the current release of OLAP Integration Server from an earlier release of Hyperion Integration Server, allow the setup program to update the environment variables for you. After installation is complete, however, you should check that the path variables are set properly for the system configuration:
     
    Operating System Path Variable Settings  (1)
    Windows NT or 
    Windows 2000 (2)    		 PATH=CURRENT_PATH;%ISHOME%\Bin;
                 %ISHOME%\odbclib;%ISHOME%\hyperlib;
                 %ISHOME%\esslib
    AIX (3) LIBPATH=$LIBPATH:$ISHOME/odbclib/$ISHOME/esslib
    PATH=$PATH:$ISHOME/bin
    HP-UX (3)  SHLIB_PATH $SHLIB_PATH:$ISHOME/ odbclib/$ISHOME/esslib
    PATH=$PATH:$ISHOME/bin
    Solaris (3)  LD_LIBRARY_PATH=
      $LD_LIBRARY_PATH:$ISHOME/odbclib/$ISHOME/esslib
    PATH=$PATH:$ISHOME/bin

    Table Notes:

    1. %ISHOME% represents the main installation directory of OLAP Integration Server on Windows systems. $ISHOME represents the main installation directory of OLAP Integration Server on UNIX systems.
    2. For Windows systems, check to make sure that the setup program did not add duplicate path entries for the OLAP Integration Server directories.
    3. If you are using MERANT (formerly INTERSOLV) ODBC drivers, place the MERANT library at the beginning of the library path; for example, LIBPATH=$ISHOME/odbclib: $LIBPATH:$ISHOME/esslib).

Migrating Existing OLAP Metadata Catalogs

If you have an existing OLAP Metadata Catalog from Hyperion's Integration Server product, you must upgrade it before using the OLAP Metadata Catalog with the current release of the software.

You cannot upgrade an existing OLAP Metadata Catalog until after you have completed the installation process.  The Hyperion Integration Server setup program installs the scripts that you need for the upgrade.

CAUTION:  If you have installed the sample application from Hyperion Integration Server, you should back up your existing sample database, OLAP Metadata Catalog, and the OLAP models and metaoutlines stored in the catalog. You can then upgrade your existing catalog to be compatible with the current release of the software. You cannot, however, store new sample OLAP models and metaoutlines in your previous catalog.

To upgrade an existing OLAP Metadata Catalog, perform the following tasks:

Upgrading the OLAP Metadata Catalog

If you have OLAP Metadata Catalogs from a earlier releases of Hyperion Integration Server, you need to upgrade the OLAP Metadata Catalogs before you can use them with this release of DB2 OLAP Integration Server 7.1, Fixpack 2.

Upgrade the tables of an OLAP Metadata Catalog with the same utility program you normally use to create tables running SQL scripts. The SQL scripts to upgrade tables for the OLAP Metadata Catalog are in the ocscript directory where you installed OLAP Integration Server. The upgrade scripts are named ocdatabase_name_upgrd20.sql and are listed the following table, along with the utility programs with which they have been tested.
 
 
Database DBMS SQL Script  Utility Program
DB2 ocdb2.sql
ocdrop_db2.sql
ocdb2_upgrd20.sql		 DB2 Command Center, or:
>DB2 -tvf
Oracle ocoracle.sql
ocdrop_oracle.sql
ocoracle_upgrd20.sql		 SQL*Plus
MS SQL Server ocsqlsrv.sql
ocdrop_sqlsrv.sql
ocsqlsrv_upgrd20.sql		 ISQL (MS SQL Server 6.5) 
Query Analyzer (MS SQL Server 7.0)

To upgrade tables for the OLAP Metadata Catalog database, perform the following tasks:

  1. Start the utility program.
  2. Connect to the database you created for the OLAP Metadata Catalog as the user who created the original OLAP Metadata Catalog tables.
  3. Open the appropriate SQL script file in the ocscript directory.
  4. Run the SQL script to upgrade the tables. On SQL Server, you receive a message that you did not create data or rows. This message is normal because you created only tables and columns.
  5. Verify that you have created the additional OLAP Metadata Catalog tables.  For example, type the following command:

    6. SELECT * FROM OM_DESCRIPTION
    or start the RDBMS and verify that the OLAP Metadata Catalog has the new tables.
  6. Close the utility program.


New Sample Applications

IBM DB2 OLAP Integration Server provides a sample database based on a fictitious company named The Beverage Company (TBC). A new sample OLAP Metadata Catalog (TBC_MD) contains a sample OLAP model (TBC Model) which includes attribute-enabled columns, and a sample metaoutline (TBC Metaoutline), which includes attribute dimensions.

Shipped with DB2 OLAP Integration Server are two Drill-Through  sample spreadsheets based on the TBC sample data.  These two spreadsheets are:  essdt.xls and essdt.123.  Both spreadsheets are located in IS\Samples\Spreadsheets.

In order to use the sample HIS Drill-Through reports, essdt.xls and essdt.123, you need to login to a machine that has both the DB2 OLAP Server and DB2 OLAP Integration Server installed. You must run member and data load and calculate the data for the sample OLAP Integration Server database that you will access from Spreadsheet Add-in.
 

Copyright (c) 1998-2001 by International Business Machines Corporation. All Rights Reserved.
Copyright (c) 1991-2001 by Hyperion Solutions Corporation. All Rights Reserved.