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:
- Create a database with 30 MB for storage.
- Create user names and passwords for the database.
- 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.
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.
6 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.
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.
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.