Hyperion(r) SQL Interface Guide Information Map
Show | Previous | Next
Hyperion(r) SQL Interface Guide Information Map
Hide | Previous | Next
Hyperion(r) SQL Interface Guide Information Map
Show | Previous | Next  
Hyperion(r) SQL Interface Guide Information Map
Hide | Previous | Next  



Using Non-MERANT ODBC Drivers

You can use ODBC database drivers with Essbase other than the MERANT drivers provided with SQL Interface. For example, you can use a Microsoft ODBC driver to connect to Microsoft Access, or use an IBM ODBC driver to connect to IBM DB2/6000. Some non-MERANT ODBC drivers are tested with Essbase. For more information on tested and supported drivers and data sources, see the Essbase Installation Guide or the readme.txt file.

To use a non-MERANT driver, you need to:

  1. Configure the driver.
  2. Configure the data source.

This chapter contains the following topics that describe how to use non-MERANT ODBC drivers:

Configuring a Non-MERANT Driver

When you configure a MERANT driver, Essbase recognizes the basic configuration information for the driver, especially the name of the driver and whether the name and password are case-sensitive. However, when you use a non-MERANT driver, you must give Essbase the configuration information for the driver that you use.

You give Essbase the configuration information for your non-MERANT driver by creating a driver configuration file called esssql.cfg, which is an ASCII text file. Place this file in your ARBORPATH\BIN directory on OLAP Server. This is c:\hyperion\essbase\ if you installed Essbase in the default directory (/home/hyperion/essbase/ on UNIX platforms).

Note: If you do not create a configuration file, Essbase uses its own default values. You may not be able to connect to the SQL database using the default values.

Creating a Configuration File for Non-MERANT Drivers

The configuration file must contain at least some of the following configuration values for each non-MERANT ODBC driver that you use. Required values may be different for different drivers. See the online help for each driver or see the documentation for specific information.

This section contains information about what you need to include in the configuration file, and in one case, how to find it:

Including the Minimum Required Contents

At a minimum, your configuration file must contain at least the DriverName for every non-MERANT ODBC driver that you use. Surround all values for each driver with brackets ( [ ] ). Leave one or more spaces between the configuration keyword and its corresponding value.

Use this template to help you create a configuration file:

[  

 

Description  

"Description of Driver "

DriverName   

DriverName

UserId  

Value

Password  

Value

Database  

Value

Server  

Value

Application  

Value

Dictionary  

Value

Files  

Value

SingleConnection  

Value

UpperCaseConnection  

Value

IsQEDriver  

Value

]  

 



Description of Driver is your description of the driver. Enclose the description in quotation marks (" "). The default value is " ".

DriverName is the file name of the non-MERANT ODBC driver and is required.

For all other configuration keywords, set Value to be either 1 or 0, according to is table:

Configuration Information
Value
Description

UserId

1

User ID required.

0

User ID not required. Default: 0

Password

1

Password required.

0

Password not required. Default: 0

Database

1

Database name required.

0

Database name not required. Default: 0

Server

1

Server name required.

0

Server name not required. Default: 0

Application

1

Application name required.

0

Application name not required. Default: 0

Dictionary

1

Dictionary name required.

0

Dictionary name not required. Default: 0

Files

1

Files name required.

0

Files name not required. Default: 0

SingleConnection

1

Driver not thread-safe. One active connection allowed. Default: 1

Recommendation for MERANT drivers: 1

0

Driver thread-safe. Multiple active connections allowed.

Caution: Specifying 0 (multiple active connections allowed) for MERANT or non-MERANT drivers may lead to instability.

UpperCaseConnection

1

Driver not case-sensitive. Connection information is converted to uppercase.

0

Driver case-sensitive. Connection information is unchanged. Default: 0

IsQEDriver

1

Driver is a MERANT driver. You can specify configuration information for MERANT drivers (for example, if you have a later version of an MERANT driver which Essbase does not yet support).

0

Driver is a non-MERANT driver. Default: 0



The defaults apply if you do not specify the value for the driver. These defaults are different from the Essbase default values that apply if you do not create an esssql.cfg file.

Include configuration information for all the non-MERANT drivers in one configuration file. Enclose the values for each driver in brackets ( [ ] ).

Use this template to help you create a configuration file:

[  
 
Description  
"Description of Driver "  
DriverName  
DriverName  
...  
 
]  
 
 
 
[  
 
Description  
"Description of Driver "  
DriverName  
DriverName  
...  
 
]  
 


Finding a Driver Name on Windows

To display the name of the drive, use this procedure:

  1. Start the ODBC Administrator using any method from Step 1 in Configuring a Data Source on Windows. If you do not have the ODBC components, see New for Release 6.2 for information about obtaining them.
  2. The ODBC Data Source Administrator dialog box, User DSN tab appears:

    Data sources that you have configured are in the User Data Sources list box. Drivers that have not been properly configured may also be in the User Data Sources list box. Ignore them.

  3. Select the Drivers tab to display the Drivers screen:
  4. Obtain the file name of the driver by scrolling to the right. For example, the file name for the Microsoft Access Driver is ODBCJT32.DLL.
  5. Click OK when you have selected the driver.

For more information on the ODBC Administrator, see Configuring an MS Access Data Source on Windows.

Finding a Driver Name on UNIX

On UNIX, the driver's name is in the .odbc.ini file. For more information on the .odbc.ini file, see Configuring a Data Source on UNIX.

Example: Creating a Configuration File for the MS Access Driver on Windows

The driver name for the Microsoft Access driver on Windows is ODBCJT32.DLL. This driver requires a database name, server name, application name, and dictionary (directory path). This driver is not a MERANT driver, so you must create a type its configuration file, esssql.cfg in ASCII-text format.

Use this example as a template:

[  

 

Description  

"Microsoft Access Driver (*.MDB) "

DriverName  

ODBCJT32.DLL

Database  

1

Server  

1

Database  

1

Application  

1

Dictionary  

1

IsQEDriver  

0

]  

 



Note: See Configuring an MS Access Data Source on Windows.

Example: Creating a Configuration File for the IBM DB2 Driver on AIX

The driver name for the IBM DB2/6000 driver on AIX is DB2.0. This driver requires a user ID, password, and database name, but not a server name. This driver is thread-safe and case-sensitive. It is not a MERANT driver, so you must create a configuration file, esssql.cfg, in ASCII-text format.

Use this example as a template:

[  

 

Description  

"IBM DB2/6000 ODBC Driver "

DriverName  

DB2.0

UserId  

1

Password  

1

Database  

1

SingleConnection  

0

UpperCaseConnection  

0

IsQEDriver  

0

]  

 



Note: See Configuring an IBM DB2 Data Source on AIX.

Configuring Non-MERANT Data Sources

Configure a non-MERANT data source in the same way that you configure a MERANT data source. See Configuring a Data Source for more information. The following sections tell you how to configure a Microsoft Access data source on Windows NT and an IBM DB2/6000 data source on AIX:

Note: Before you configure a non-MERANT data source, you must first configure the driver by creating an esssql.cfg configuration file. See Creating a Configuration File for Non-MERANT Drivers.

Configuring an MS Access Data Source on Windows

The following example describes how to configure a Microsoft Access data source on OLAP Server on Windows. Your data source and driver may differ, but the steps that you take to configure the data source are essentially the same. The example assumes:

On Windows, use the ODBC Administrator, ODBCAD32.EXE, to configure a non-MERANT data source, just as you use it to configure a MERANT data source.

This example uses the ORDERS.MDB file, which is installed with Microsoft Access, but you can use any .MDB file to test the data source configuration.

The Microsoft Access ODBC 32-bit database driver is also installed with Microsoft Access, but it can be installed in other ways. For Microsoft Access, you need to specify the data source name and database. You can also provide a description. Other ODBC drivers may require additional information. See the documentation for each driver, and online help, for what is required.

To configure the Microsoft Access data source:

  1. Start the ODBC Administrator in either of the ways described in Step 1 of Configuring a Data Source on Windows.
  2. The User DSN tab of the ODBC Data Source Administrator dialog box is displayed:

    Data sources that you have previously configured are displayed in the User Data Sources list box. Drivers that have not been properly configured may also be in the User Data Sources list box. Ignore or remove them.

  3. Click Add to display the Create New Data Source dialog box:
  4. Scroll down the list of available drivers and select Microsoft Access Driver (*.mdb).
  5. Click Finish to display the ODBC Microsoft Access 97 Setup dialog box.
  6. Type MSAccess Orders, or any name that will help you identify the source, in the Data Source Name text box.
  7. Type Load Data in the Description text box.
  8. Click Select in the Database group to display the Select Database dialog box.
  9. Locate and select ORDERS.MDB (or the name of your .MDB file).
  10. Click OK. The ODBC Microsoft Access 97 Setup dialog box is displayed with the path and file that you selected.
  11. Click OK to display the ODBC Data Source Administrator dialog box.
  12. The Microsoft Access data source is in the User Data Sources list box, with other data sources that may or may not have been configured.

  13. Click OK.

You can now load data from the Microsoft Access data source file that you chose into a Essbase database. For information on loading SQL data, see Loading SQL Data. For general information on loading data, see the Essbase Database Administrator's Guide.

Note: Remember that source files must be accessible to OLAP Server. They can be on the OLAP Server machine or on the network.

Configuring an IBM DB2 Data Source on AIX

If you use AIX, you can connect to an IBM DB2/6000 data source using the IBM DB2/6000 ODBC driver. Consult the database administrator for information on configuration, authorization, binding, and other steps that may be required to connect to the data source.

Note: See Example: Creating a Configuration File for the IBM DB2 Driver on AIX.

Support for Non-MERANT Drivers

Some non-MERANT drivers are not tested or supported for use with Essbase. The driver that you choose might not be specifically tested to work with Essbase. For information on qualified drivers and data sources, see the Essbase Installation Guide.




Copyright © 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com