DB2 OLAP Server Installation Guide

Samples for DB2 OLAP Integration Server

DB2 Universal database and Oracle users can create the sample application (TBC, TBC Model, and TBC Metaoutline) either manually or by using the automated functionality in the Essbase Integration Services Console. However, to create the sample application with Hybrid Analysis enabled, you must also complete the tasks in Setting up the sample application manually. Upon completion of these tasks, the sample TBC relational data source will contain Hybrid Analysis data. The OLAP Metadata Catalog for DB2 and Oracle users will contain a Hybrid Analysis-enabled OLAP model (HA TBC Model) and metaoutline (HA TBC Metaoutline).

Setting up the sample application involves two main tasks:

You create two relational databases: one for the data in the TBC sample application and one for the OLAP Metadata Catalog database (TBC_MD). This is a manual task that you must perform before setting up the sample application, regardless of whether you choose the automatic or manual method.
You then create tables in each of these databases and load data into them, using scripts provided with DB2 OLAP Integration Server. This can be performed either automatically, through the use of Essbase Integration Services Console, or manually.

When you finish setting up the sample application, the OLAP metadata catalog database (TBC_MD) will contain an OLAP model (TBC Model) and a metaoutline (TBC Metaoutline) based on data in the sample TBC relational data source.

If you have installed the sample application from a previous release of DB2 OLAP Integration Server, you should back up and preserve your existing sample database, OLAP Metadata Catalog, and the OLAP models and metaoutlines that OLAP Metadata Catalog contains. You can then upgrade your existing catalog to be compatible with the current release of the software (see Upgrading the OLAP Metadata Catalog). You cannot, however, store newly-created OLAP models and metaoutlines in your previous catalog.

This section tells you what to consider before and after you set up the sample application, whether automatically or manually. For manual setup, this section describes the scripts and batch files you must run to create and load the TBC database and the TBC_MD OLAP Metadata Catalog.

The procedures in this section assume that you know how to create a database using a relational database management system (RDBMS) and have installed the required database client software. For manual setup, the procedures assume that you know how to create tables and load them with data running SQL scripts, batch files, and shell scripts. For information on these topics, see the documentation for the RDBMS you are using.

About the DB2 OLAP Integration Server samples

DB2 OLAP Integration Server provides a sample database based on a fictitious company named The Beverage Company (TBC) and includes the following components:

Sample OLAP Metadata Catalog (TBC_MD)
Sample data source (TBC)
Sample OLAP model (TBC Model) and sample OLAP metaoutline (TBC Metaoutline)
DB2 Universal Database and Oracle: Additional sample data that demonstrates Hybrid Analysis functionality.
DB2 Universal Database and Oracle: Additional sample Hybrid Analysis-enabled OLAP model (HA TBC Model) and sample Hybrid Analysis-enabled OLAP metaoutline (HA TBC Metaoutline)

The sample OLAP models and metaoutlines are provided as XML files, supported by Java. The sample application demonstrates how to create a Measures dimension recursively, using a sort on an alternative column in the relational data source. It also shows how to load metadata from database columns. Additionally, the sample application includes UDAs and Date Time Series data, additional years, drill-through paths, and a Time table to demonstrate data load incremental updates.

For DB2 and Oracle users, the sample application also provides sample data, a sample OLAP model, and a sample OLAP metaoutline that demonstrate Hybrid Analysis functionality.

Before you set up the sample application

Before you set up the sample application, you must install the database client software for a supported RDBMS. When setting up the sample application, you must use a computer on which the server component of DB2 OLAP Integration Server and the necessary database client software are both installed. For more information, see About configuring relational data sources.

For manual setup of the sample application, the sample application scripts do not have to be installed on the same computer you are using, but you must be able to access the script files.

It is recommended that you use the automatic installation process to install the sample applications (see Setting up the sample application manually).

In addition to the sample application scripts, the sample application requires the database client software for the RDBMS you are using. The database client software is required to run the sample application scripts. Verify that the database client software for the RDBMS is installed. For manual sample application setup, refer to Table 18 and also verify that the appropriate client utility program is installed.

Table 18. Required utilities for setting up the sample application

Database	Utility Programs
DB2 Universal Database	DB2 Command Window, or `> DB2 -tvf`
Informix	DBAccess
MS SQL Server	Query Analyzer (MS SQL Server 7.0 and 2000)
Oracle	SQL * Plus
Sybase	ISQL BCP command line utility

Setting up the sample application automatically

When you bring up DB2 OLAP Integration Server, it displays the OLAP Metadata Catalog Setup dialog box to enable automatic setup of the catalog (see Creating an OLAP Metadata catalog automatically). Whether or not you chose to create the OLAP Metadata Catalog Setup automatically, you can choose to create the sample application automatically (after you have finished creating the OLAP Metadata Catalog). The automatic sample application creation feature enables you to complete the process quickly and easily. You are encouraged to use this convenient feature, but if you choose manual setup, see Setting up the sample application manually for detailed procedures.

DB2 and Oracle: If you plan to set up the sample application for Hybrid Analysis, you must follow the procedures in Setting up the sample application manually.

ODBC connections must be configured before you begin to set up the sample application. If you have not yet configured ODBC connections, see Chapter 8, Configuring data sources for DB2 OLAP Integration Server for information on setting up ODBC data sources, including an OLAP Metadata Catalog and relational data sources.

To set up the sample application automatically:

Select Tools > Create Sample to access the Sample Application Setup dialog box.
In the ODBC Name (DSN) text box, select or type the ODBC name for creating or dropping (deleting) tables in the source database.
The User Name text box displays the name, TBC (lowercase tbc for Informix). This name must be used to create the TBC sample application database.
In the Password text box, type the password for the user to which you have assigned privileges to access the sample application TBC database.
Click Create to create the sample application.
DB2 OLAP Integration Server creates the necessary tables, loads the TBC sample data into the tables, and loads the sample OLAP model and metaoutline data into the OLAP Metadata Catalog.
If DB2 OLAP Integration Server detects that you are not currently connected to the OLAP Metadata Catalog in which to load the sample OLAP model and metaoutline data, the Login dialog box is displayed so that you can enter the information necessary to connect. After you close the Login dialog box and are returned to the Sample Application Setup dialog box, click Create again to initiate setup of the sample application.
If, after clicking Create, DB2 OLAP Integration Server detects either an existing sample data source or an existing sample OLAP model and metaoutline in the OLAP Metadata Catalog, you are prompted with the message that tables already exist in the database. Click OK to return to the Sample Application Setup dialog box to reenter information, or click Delete to delete the existing sample application data. If you click Delete, your existing OLAP Metadata Catalog sample OLAP models and metaoutlines will be deleted and replaced with the sample OLAP model and metaoutline for the current release.

After you have created the sample application, you are ready to begin working with Essbase Integration Services Console to create your own sample OLAP models and metaoutlines.

Setting up the sample application manually

To set up the sample application manually, you begin by creating two databases in a supported RDBMS: TBC (the sample database) and TBC_MD (an OLAP Metadata Catalog). You then create tables in each of these databases and load data into them, using scripts provided with Essbase Integration Services. The names and directory locations of these scripts are detailed in the topics that follow.

It is recommended that you use the automatic installation process to install the sample applications Setting up the sample application automatically; however, if you plan to set up the sample application for Hybrid Analysis, you must also complete the procedures in this section.

Be aware that the sample application setup process for Hybrid Analysis may take some time.

After you create the TBC and TBC_MD databases in a supported RDBMS, you run several scripts to create the sample application:

A table creation script for the TBC sample database, located in the samples\tbcdbase directory.
DB2 Universal Database and Oracle: To create the sample application with Hybrid Analysis enabled, you must run two scripts, tbc_create_*.sql and tbc_create_ha_*.sql.
A data load script for the TBC sample database, located in the samples\tbcdbase directory.
DB2 Universal Database and Oracle: To create the sample application with Hybrid Analysis enabled, you must run two scripts, sampledata.sql and sampledata_ha.sql.
A table creation script for the TBC_MD OLAP Metadata Catalog database, located in the ocscript directory
XML files to import a sample OLAP model and metaoutline in the TBC_MD OLAP Metadata Catalog database, located in the samples\tbcmodel directory
DB2 Universal Database and Oracle: To create the sample application with Hybrid Analysis enabled, you must import a second OLAP model (sample_ha_model.xml) and metaoutline (sample_ha_metaoutline.xml).

The sample application scripts and catalog creation scripts are installed with the server software during the installation process. Different versions of the scripts are provided for each of the supported RDBMSs.

DB2 Universal Database and Oracle: Scripts are provided to create the sample application with Hybrid Analysis enabled. These scripts contain the letters ha in their names.

Some of the sample application scripts require slightly different procedures, depending on the RDBMS you are using. Be sure to follow the procedure for your specific RDBMS.

Setting up the TBC relational data source

The relational data source for the sample application is TBC. To create a database, you must have database administrator or similar access privileges required by the RDBMS that you are using.

To set up TBC relational data source:

Create the TBC database using an RDBMS. For more information, see Creating the TBC database.
Create tables for the TBC database by running SQL scripts. For instructions, see Creating tables for the TBC database.
Load data into the TBC database by running SQL scripts. For instructions, see Loading data into the TBC tables.

Creating the TBC database

Create the TBC database in the same way that you create any database using an RDBMS:

Create a database device or tablespace named TBC.
Allot 20 MB for storage.
Create a user TBC who can drop and create tables. The user TBC must create the tables for the TBC database, or portions of the sample application will not work.
Grant user privileges or permissions to create and drop tables.

Creating tables for the TBC database

Create tables for the TBC database with the same utility program you normally use to create tables by running SQL scripts.

The sample application SQL scripts needed to create tables for the TBC database are in the samples\tbcdbase directory where you installed DB2 OLAP Integration Server.

The utilities listed in Table 19 have been tested to work with the SQL scripts:

Table 19. Tested utilities for creating TBC tables

Database	SQL script	Utility program
DB2	tbc_create_db2.sql tbc_create_ha_db2.sql tbc_drop_db2.sql tbc_drop_ha_db2.sql	DB2 Command Window, or `>DB2 -tvf`
Informix	tbc_create_informix.sql tbc_drop_informix.sql	DBAccess
MS SQL Server	tbc_create_sqlsrv.sql tbc_drop_sqlsrv.sql	Query Analyzer (MS SQL Server 7.0 and 2000)
Oracle	tbc_create_oracle.sql tbc_create_ha_oracle.sql tbc_drop_oracle.sql tbc_drop_ha_oracle.sql	SQLPlus SQLLoader command line utility
Sybase	tbc_create_sybase.sql tbc_drop_sybase.sql	ISQL

Each RDBMS has two scripts--one to build tables and another to drop tables.

DB2 and Oracle: To create the sample application with Hybrid Analysis enabled, you must run two scripts, tbc_create_*.sql and tbc_create_ha_*.sql.

You must create the tables for the TBC database as user TBC or portions of the sample application will not work.

To create tables for the TBC database:

Start the utility program.
Connect to the TBC database as user TBC. You must create the tables for the TBC database as user TBC or portions of the sample application will not work.
Use lowercase tbc for Informix; use uppercase TBC for Sybase and SQL Server.
In the samples\tbcdbase directory, open the appropriate SQL script file.
Run the SQL script.
MS-SQL Server: You should receive a message that you did not create data or rows. This message is normal because you created only tables and columns.
Informix: If you run the Informix SQL script (tbc_create_informix.sql) more than once, you must first run tbc_drop_informix.sql to drop tables before you build them again.
DB2 and Oracle: To create the sample application with Hybrid Analysis enabled, you must run two scripts, tbc_create_*.sql and tbc_create_ha_*.sql.
Verify that you have created the TBC tables; for example, type:
```
SELECT * FROM PRODUCTDIM
```
or start the RDBMS and verify that the TBC database has the new tables.
Close the utility program.

Loading data into the TBC tables

Load data into the TBC tables by running an SQL script using the same utility program you normally use to load tables by running SQL scripts.

DB2 Universal Database and Oracle: To create the sample application with Hybrid Analysis enabled, you must run two scripts, sampledata.sql and sampledata_ha.sql.

Be aware that the sample application setup process for Hybrid Analysis may take some time.

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

Table 20. Tested utilities for loading data into TBC tables

Database	SQL script	Utility program
DB2	sampledata.sql sampledata_ha.sql	DB2 Command Window, or `>DB2 -tvf`
Informix	sampledata.sql	DBAccess
MS SQL Server	sampledata.sql	Query Analyzer (MS SQL Server 7.0 and 2000)
Oracle	sampledata.sql sampledata_ha.sql	SQL*Plus
Sybase	sampledata.sql	ISQL

To load data into the TBC tables:

From the command line, move to the samples\tbcdbase directory where you installed DB2 OLAP Integration Server.
Start the utility program.
Connect to the TBC database as user TBC.
In the sample\tbcdbase directory, open the sampledata.sql script file.
Run the SQL script using your RDBMS tool.
DB2 and Oracle: To create the sample application with Hybrid Analysis enabled, you must run a second script, sampledata_ha.sql.
Verify that you have loaded data into the TBC tables; for example, type:
```
SELECT * FROM TBC.PRODUCT
```
or start the RDBMS and execute a query.
Close the utility program.

Setting up TBC_MD

The OLAP Metadata Catalog for the sample application is TBC_MD. For more information on OLAP Metadata Catalogs, see Chapter 9, Creating OLAP Metadata Catalogs for DB2 OLAP Integration Server.

To set up the TBC_MD OLAP Metadata Catalog:

Create a TBC_MD database using an RDBMS. For more information, see Creating the TBC_MD Database
Create tables for the TBC_MD database by running SQL scripts. For instructions, see Creating tables for the TBC_MD database.
Load data into the TBC_MD database using the XML import utility. For instructions, see Loading data into the TBC_MD tables using the XML Import utility.

If you have a previous release of DB2 OLAP Integration Server and are upgrading your OLAP Metadata Catalog, you cannot roll back to the previous version. The new version of the OLAP Metadata Catalog is not compatible with earlier releases of DB2 OLAP Integration Server.

Creating the TBC_MD Database

Create the TBC_MD database in the same way that you create any database using an RDBMS:

Create a database device or tablespace named TBC_MD.
Allot 20 MB for storage.
Create a user TBC who can drop and create tables. The user TBC must create the tables for the TBC_MD database, or portions of the sample application will not work.
Grant user privileges or permissions to create and drop tables.

Creating tables for the TBC_MD database

Create tables for the TBC_MD database with the same utility program you normally use.

The sample application SQL scripts used to create tables for the TBC_MD database are in the ocscript directory where you installed DB2 OLAP Integration Server.

The SQL scripts in the ocscript directory are the same scripts you use to create any OLAP Metadata Catalog. For information on OLAP Metadata Catalogs, see Chapter 9, Creating OLAP Metadata Catalogs for DB2 OLAP Integration Server

The utilities listed in Table 21 have been tested to work with the SQL scripts:

Table 21. Tested utilities for creating TBC_MD 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 Integration 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 DB2 OLAP Integration Server Version 7.1, General Availability (Essbase Integration Services Release 1.9) to Version 7.1, FixPak 3 (Essbase Integration Services Release 2.0)
oc_upgrade61_database_name.sql to upgrade tables from DB2 OLAP Integration Server Version 7.1, FixPaks 3 through 7 (Essbase Integration Services Release 2.0) to Version 7.1, FixPak 8 (Essbase Integration Services Release 6.2)
oc_upgrade65_database_name.sql to upgrade tables from DB2 OLAP Integration Server Version 7.1, FixPaks 7 and later (Essbase Integration Services Release 6.1 or 6.2) to Version 8.1 Essbase Integration Services Release 6.5)

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

You must create the tables for the TBC_MD database as user TBC or portions of the sample application will not work.

To create tables for the TBC_MD database:

Start the utility program.
Connect to the TBC_MD database as user TBC. You must create the tables for the TBC_MD database as user TBC or portions of the sample application will not work.
In the ocscript directory, open the appropriate SQL script file.
Run the SQL script.
MS-SQL Server: You should 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 TBC_MD tables; for example, type:
```
SELECT * FROM TBC.MS_INFO
```
or start the RDBMS and verify that the TBC_MD database has the new tables.
Close the utility program.

Loading data into the TBC_MD tables using the XML Import utility

Use the XML Import utility to load OLAP model and metaoutline data into the TBC_MD tables.

The XML files listed in Table 22 have been tested to work with their respective RDBMS. These files are located in the samples\tbcmodel directory.

Table 22. XML files for loading data into TBC_MD tables

Database	XML File in sample\tbcmodel
DB2 Universal Database Oracle	sample_model.xml sample_ha_model.xml sample_metaoutline.xml sample_ha_metaoutline.xml
MS SQL Server Sybase	sample_model.xml sample_metaoutline.xml
Informix	sample_model_informix.xml sample_metaoutline.xml

To complete the procedure below, you must start Essbase Integration Services Console and connect to the server component of DB2 OLAP Integration Server.

To start Essbase Integration Services Console and connect to to the server component of DB2 OLAP Integration Server:

On the Windows desktop, click Start > Programs > IBM DB2 OLAP Server 8.1 > IBM DB2 OLAP Integration Server > Integration Server.
On the Windows desktop, click Start > Programs > IBM DB2 OLAP Server 8.1 > IBM DB2 OLAP Integration Server > Desktop.
If necessary, click Close to clear the OLAP Metadata Catalog Setup dialog box. Essbase Integration Services Console automatically displays the Login dialog box.
1. In the Server text box, under Essbase Integration Services, select or type the name of a computer on which the server component of DB2 OLAP Integration Server has been installed.
2. In the OLAP Metadata Catalog text box, select or type the name of the OLAP Metadata Catalog, TBC_MD, for the sample TBC (The Beverage Company) database.
3. In the User Name and Password text boxes, type the user name and password for the sample application user, TBC.

To load sample OLAP model data into the TBC_MD tables using XML Import:

In Essbase Integration Services Console, click File > XML Import/Export.
Select the Import tab.
Click the Open XML File button to navigate to the location of the sample XML files. For example, if you selected the default directory during installation, the files are in ibm\db2olap\IS\Samples\tbcmodel.
Select the XML file to import for the sample OLAP model data, sample_model.xml.
Informix: Select the sample OLAP model for Informix, sample_model_informix.xml.
Click OK.
Essbase Integration Services Console displays the XML Import/Export dialog box with the XML file path and name in the XML File Path text box.
The name of the sample OLAP model is displayed in the OLAP Model Name text box.
Click the Import to Catalog button to load the selected XML file into the sample OLAP Metadata Catalog.
DB2 and Oracle: To create a sample OLAP model with Hybrid Analysis enabled, repeat this procedure to import the second XML file, sample_ha_model.sql.

To load sample OLAP metaoutline data into the TBC_MD tables using XML Import:

In Essbase Integration Services Console, click File > XML Import/Export.
Select the Import tab.
Click the Open XML File button to navigate to the location of the sample XML files. For example, if you selected the default directory during installation, the files are in ibm\db2olap\IS\Samples\tbcmodel.
Select the XML file to import for the sample OLAP metaoutline data, sample_metaoutline.xml.
Click OK.
Essbase Integration Services Console displays the XML Import/Export dialog box with the XML file path and name in the XML File Path text box, the name of the sample OLAP metaoutline in the OLAP Metaoutline Name text box, and the name of the OLAP model upon which this metaoutline is based in the Based on OLAP Model text box.
The name of the sample OLAP model is displayed in the OLAP Model Name text box.
Click the Import to Catalog button to load the selected XML file into the sample OLAP Metadata Catalog.
DB2 and Oracle: To create a sample metaoutline with Hybrid Analysis enabled, repeat this procedure to import the second XML file, sample_ha_metaoutline.sql.

After you set up the sample application

You must connect to both TBC and TBC_MD from Essbase Integration Services Console to create, modify, and store TBC OLAP models and TBC metaoutlines. To make these connections, each database (TBC and TBC_MD) must be mapped to a supported ODBC driver, as described in Chapter 8, Configuring data sources for DB2 OLAP Integration Server

When you connect to Essbase Integration Services Console, you can view TBC columns, tables, OLAP models, and metaoutlines in Essbase Integration Services Console. For more information, see Viewing TBC tables, columns, OLAP models, and metaoutlines.

You must connect to TBC and TBC_MD as user TBC, unless you create user name aliases or synonyms in the RDBMS

Viewing TBC tables, columns, OLAP models, and metaoutlines

After you set up the sample application and configure TBC and TBC_MD by mapping them to supported ODBC drivers, you can view TBC tables, columns, OLAP models, and metaoutlines in Essbase Integration Services Console.

Start DB2 OLAP Integration Server and the Essbase Integration Services Console
In a blank Essbase Integration Services Console window, select Connections > OLAP Metadata Catalog > Connect, and connect to the OLAP Metadata Catalog, TBC_MD.
In the New tab, double-click the OLAP Model icon.
In the Data Source dialog box, connect to the TBC sample database.

To see the OLAP model (TBC Model) in the right frame of Essbase Integration Services Console, follow these steps:

Start DB2 OLAP Integration Server and the Essbase Integration Services Console
If the Login dialog box is not already displayed, in a blank Essbase Integration Services Console window, select Connections > OLAP Metadata Catalog > Connect.
Connect to the OLAP Metadata Catalog TBC_MD.
Click the Existing tab, select TBC Model, and click Open.
DB2 and Oracle: To view the Hybrid Analysis-enabled OLAP model, select HA TBC Model.
In the Data Source dialog box, connect to the TBC sample database.

To see the metaoutline (TBC Metaoutline) in the right frame of Essbase Integration Services Console:

Start DB2 OLAP Integration Server and the Essbase Integration Services Console
If the Login dialog box is not already displayed, in a blank Essbase Integration Services Console window, select Connections > OLAP Metadata Catalog > Connect.
Connect to the OLAP Metadata Catalog TBC_MD.
Click the Existing tab, then click the plus symbol (+) to the left of TBC Model to expand the view, and select TBC Metaoutline.
DB2 and Oracle: To view the Hybrid Analysis-enabled metaoutline, click the plus symbol (+) to the left of HA TBC Model to expand the view, and select HA TBC Metaoutline.
Click Open. The Data Source dialog box is displayed.
In the Data Source dialog box, connect to the TBC sample database.

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