1. Database Administrator's Guide for Oracle Essbase
  2. Design Rules for Data Load or Dimension Build
  3. Define Rules that Query External Sources
  4. Access Data Using ODBC Connectivity
  5. Configure the ODBC Source of Data (Windows)

Configure the ODBC Source of Data (Windows)

To make ODBC sources of data work with Essbase on Windows, you use the ODBC Data Source Administrator utility to configure external sources of data.

To configure Essbase on Windows to work with the ODBC drivers, use the following workflow.

Prerequisites

Before you begin,

  • Make note of the location of the ODBC driver libraries on the Essbase server domain:

  • If you are configuring Teradata as a source, do the following:

    1. Download the Teradata Tools and Utilities client from https://downloads.teradata.com.

    2. Install the Teradata client onto your Essbase server machine.

Configure the ODBC Source of Data (Oracle Database)

To configure Essbase on Windows to work with Oracle Database ODBC drivers, use the following workflow.

  1. Click Start, click Windows Administrative Tools, and select ODBC Data Sources (64-bit).

  2. Click the System DSN tab if you will configure for all users of this instance of Essbase on Windows, or click User DSN to configure only for the current user.

  3. Click Add, and select a driver to configure. For example, to add an ODBC driver for Oracle, select Oracle 8.0 Oracle Wire Protocol.


    "Create New Data Source" screen within the Windows 64-bit ODBC Data Source Administrator utility. Text on the dialog reads "Select a driver for which you want to set up a data source." The selected driver is "Oracle 8.0 Oracle Wire Protocol," and the Finish button is highlighted.

  4. Click Finish.

  5. In the driver setup dialog,

    1. Enter the Data Source Name (for example, TBC_ORA).

    2. Enter the name of the Oracle Database host machine (for example, myhost.example.com).

    3. Enter the port number of the Oracle Database (for example, 1521).

    4. Enter either one of the following:

      • the Oracle System ID (SID) that uniquely identifies the database (for example, ORCL)

      • the Oracle Service Name, if the Database is registered with a listener (for example, orcl.esscs.myhost.example.com)


      "ODBC Oracle Wire Protocol Driver Setup" screen within the Windows 64-bit ODBC Data Source Administrator utility. The entries on the screen are as follows. Data Source Name: TBC_ORA, Host: myhost.example.com, Port Number: 1521, SID: ORCL. All other fields are left default, and the "Test Connect" button is highlighted.

    5. Click Test Connect.

  6. In the Logon to <source> dialog, enter the user name and password of an authorized Oracle Database user, and click OK.


    "Logon to Oracle Wire Protocol" screen within the Windows 64-bit ODBC Data Source Administrator utility. The entries on the screen are as follows. Host: myhost.example.com, Port Number: 1521, SID: ORCL, User Name: tbc, Password: (obscured).

  7. If the connection to Oracle Database was successful, a Connection established message displays.


    Windows system message for Test Connect. The message reads: Connection established!

    Click OK.

  8. Click Apply, and click OK. The ODBC data source is configured and ready to use.


    Windows 64-bit ODBC Data Source Administrator utility, System DSN tab. A System Data Source named TBC_ORA has been added, associated with driver named Oracle 8.0 Oracle Wire Protocol.

  9. If the data source configuration needs to be modified,

    1. Select the data source name in System Data Sources.

    2. Click Configure.


      Windows 64-bit ODBC Data Source Administrator utility, System DSN tab. Selected is a System Data Source named TBC_ORA, associated with driver named Oracle 8.0 Oracle Wire Protocol. The Configure button is highlighted.

    3. Make any needed changes, using only the General tab.

Configure the ODBC Source of Data (Teradata)

The following workflow is for configuring Teradata to work with Essbase as an ODBC data source on Windows.

  1. Click Start, click Windows Administrative Tools, and select ODBC Data Sources (64-bit).

  2. Click the System DSN tab if you will configure for all users of this instance of Essbase on Windows, or click User DSN to configure only for the current user.

  3. Click Add, and select a driver to configure. For example, to add an ODBC driver for Teradata, select Teradata Database ODBC Driver 16.20.

  4. Click Finish.

  5. In the driver setup dialog,

    1. Enter the Data Source Name (for example, TBC_TD).

    2. Enter the host name or IP address of the Teradata Database Server (for example, 10.xx.xx.211).

    3. Enter the user name and password of an authorized Teradata user.

    4. Enter the Default Database name (for example, TBC).

    5. Click Test Connect.

  6. If the connection to Teradata was successful, a success message displays.

    Click OK.

  7. Click Apply, and click OK. The ODBC data source is configured and ready to use.

  8. If the data source configuration needs to be modified,

    1. Select the data source name in System Data Sources.

    2. Click Configure.

    3. Make any needed changes, using only the General tab.

Configure the ODBC Source of Data (SQL Server)

To configure Essbase on Windows to work with SQL Server ODBC drivers, use the following workflow.

  1. Click Start, click Windows Administrative Tools, and select ODBC Data Sources (64-bit).

  2. Click the System DSN tab if you will configure for all users of this instance of Essbase on Windows, or click User DSN to configure only for the current user.

  3. Click Add, and select a driver to configure. For example, select Oracle 7.1 SQL Server Wire Protocol.


    "Create New Data Source" screen within the Windows 64-bit ODBC Data Source Administrator utility. Text on the dialog reads "Select a driver for which you want to set up a data source." The selected driver is "Oracle 7.1 SQL Server Wire Protocol," and the Finish button is highlighted.

  4. Click Finish.

  5. In the driver setup dialog,

    1. Enter the Data Source Name (for example, TBC_SQL).

    2. Enter the name of the host machine (for example, myhost.example.com).

    3. Enter the port number of the SQL Server (for example, 1433).

    4. Enter the Database Name of the SQL Server (for example, TBC).

    5. Click the Advanced tab, and check the Enable Quoted Identifiers box.

    6. Click Test Connect.

  6. If the connection to SQL Server was successful, a Connection established message displays.


    Windows system message for Test Connect. The message reads: Connection established!

    Click OK.

  7. Click Apply, and click OK. The ODBC data source is configured and ready to use.

  8. If the data source configuration needs to be modified,

    1. Select the data source name in System Data Sources.

    2. Click Configure.

    3. Make any needed changes, using only the General tab.