DB2 OLAP Server Installation Guide

Creating an OLAP Metadata Catalog

To create a database for an OLAP Metadata Catalog, you must have permission to create tables in the database, or similar access privileges required by the RDBMS you are using.

On MS SQL Server, the tables in a catalog will not be accessible if they were created by a user that does not have db_owner privileges.

To create an OLAP metadata catalog:

Verify that you have all of the product components necessary for the software, and make sure that your computer systems meet the system requirements for the software
Create a database for OLAP Metadata Catalog tables using an RDBMS:
1. Create a database with 30 MB for storage.
2. Create user names and passwords for the database.
3. Grant user privileges or permissions for the database.
Take one of the following actions:
- Create an OLAP Metadata Catalog automatically by using the Integration Services Console (see Creating an OLAP Metadata Catalog Automatically).
- Create tables for the OLAP Metadata Catalog database by running SQL scripts (see Creating an OLAP Metadata Catalog Manually).

Although an OLAP model must be created from a single relational data source, one OLAP Metadata Catalog can store all OLAP models and metaoutlines from numerous relational data sources. You can also create separate OLAP Metadata Catalogs to store additional OLAP models and metaoutlines for different projects.

Creating an OLAP Metadata catalog automatically

When you complete installation of DB2 OLAP Integration Server and start Essbase Integration Services Console, the program automatically displays the OLAP Metadata Catalog Setup dialog box shown in Figure 14. This dialog box enables you to create an OLAP Metadata Catalog automatically, using Essbase Integration Services Console. If you have an older version of the OLAP Metadata Catalog, you also use this dialog box to upgrade it to the current version.

To create the OLAP metadata catalog automatically:

From the Essbase Integration Server drop-down list in the OLAP Metadata Catalog Setup dialog box, select the server computer on which you have installed DB2 OLAP Integration Server.
From the ODBC Data Source Name (DSN) drop-down list, select the Data Source Name for the OLAP Metadata Catalog that you are creating.
Note:
You must have created the database in which you will store the OLAP Metadata Catalog, assigned the appropriate user permissions, and configured the ODBC connection before you can create the catalog.
In the User Name text box, type the user name to which you have assigned permission to access the database in which the OLAP Metadata Catalog will be stored.
In the Password text box, type the password for the user name to which you have assigned permission to access the database in which the OLAP Metadata Catalog will be stored.
Leave the Show this dialog at Startup check box selected to have the OLAP Metadata Catalog Setup dialog box display automatically each time you start up Essbase Integration Services Console.
If you do not select the Show this dialog at Startup check box, you can access the OLAP Metadata Catalog Setup dialog box at any time by selecting Tools > OLAP Metadata Catalog Setup from either the OLAP Model or OLAP Metaoutline main window.
Click Create.
Essbase Integration Services Console creates the OLAP Metadata Catalog automatically. Proceed to the section titled Setting up the Sample Application.
Note:
If you are migrating from a previous release of DB2 OLAP Integration Server, the OLAP Metadata Catalog auto-creation process upgrades your existing OLAP Metadata Catalog to the current version.

Deleting an OLAP Metadata Catalog

You can delete an existing OLAP Metadata Catalog at any time. Remember that if you delete an OLAP Metadata Catalog, you also delete the OLAP models and metaoutlines that it contains.

To delete an OLAP Metadata Catalog:

From either the OLAP Model or OLAP Metaoutline main window, select Tools > OLAP Metadata Catalog Setup to display the OLAP Metadata Catalog Setup dialog box.
From the Server Name drop-down list, select the appropriate DB2 OLAP Integration Server computer.
From the ODBC Data Source Name (DSN) drop-down list, select the Data Source Name for the OLAP Metadata Catalog that you want to delete.
Enter your user name and password and click Delete.
Click Yes in the confirmation prompt to delete the catalog.
If the OLAP Metadata Catalog that you are attempting to delete is currently in use, you will be prompted to disconnect from the catalog before you can delete it.

Creating an OLAP Metadata catalog manually

If you choose to create the OLAP Metadata Catalog manually, you must create a set of tables that the catalog uses to store OLAP model and metaoutline metadata. Create the tables for the OLAP Metadata Catalog by running SQL scripts using the same utility program you normally use to create tables. The SQL scripts to create tables for the OLAP Metadata Catalog are located in the ocscript directory where you installed DB2 OLAP Integration Server.

The utilities listed in Table 16 have been tested to work with the SQL scripts.

Table 16. Tested utilities for creating TBC tables

Database	SQL script	Utility program
DB2	oc_create_db2.sql oc_drop_db2.sql oc_upgrade20_db2.sql oc_upgrade61_db2.sql oc_upgrade65_db2.sql	DB2 Command Window, or `>DB2 -tvf`
Informix	oc_create_informix.sql oc_drop_informix.sql oc_upgrade20_informix.sql oc_upgrade61_informix.sql oc_upgrade65_informix.sql	DBAccess
MS SQL Server	oc_create_sqlsrv.sql oc_drop_sqlsrv.sql oc_upgrade20_sqlsrv.sql oc_upgrade61_sqlsrv.sql oc_upgrade65_sqlsrv.sql	Query Analyzer (MS SQL Server 7.0 and 2000)
Oracle	oc_create_oracle.sql oc_drop_oracle.sql oc_upgrade20_oracle.sql oc_upgrade61_oracle.sql oc_upgrade65_oracle.sql	SQL*Plus
Sybase	oc_create_sybase.sql oc_drop_sybase.sql oc_upgrade20_sybase.sql oc_upgrade61_sybase.sql oc_upgrade65_sybase.sql	ISQL

DB2 OLAP Server provides five SQL scripts for each RDBMS:

oc_create_database_name.sql to build tables
oc_drop_database_name.sql to drop tables
oc_upgrade20_database_name.sql to upgrade tables from Version 7.1 GA to Version 7.1 FixPak 3
oc_upgrade61_database_name.sql to upgrade tables from Version 7.1 FixPak 4 to Version 7.1 FixPak 7
oc_upgrade65_database_name.sql to upgrade tables from Version 7.1 FixPak 7 to Version 8.1

To create tables for the OLAP Metadata Catalog database:

Start the utility program.
Connect to the database you created for the OLAP Metadata Catalog as the user hyperion.
Open the appropriate SQL script file in the ocscript directory.
Run the SQL script to build 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.
Verify that you have created the OLAP Metadata Catalog tables. For example, type the following command:
```
SELECT * FROM JOIN_HINTS
```
or start the RDBMS and verify that the OLAP Metadata Catalog has the new tables.
Close the utility program.

After you create an OLAP Metadata Catalog manually, you must map the catalog to a supported ODBC driver.

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

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

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]