1. Using Oracle Essbase
  2. Work with Connections and Datasources
  3. Create a Connection and Datasource to Access Oracle Database

Create a Connection and Datasource to Access Oracle Database

Define a connection and Datasource between Essbase and Oracle Database.

If applicable, use one of the following subtopics instead of this one:

  1. Log in to the Essbase web interface as a service administrator or an application manager.
  2. Click Sources, then Connections.

    To define the connection and Datasource at application level instead of globally, start on the Applications page instead of the Sources page. Click an application name, and then click Sources.

  3. Click Create Connection, and for the connection type, select Oracle Database.
  4. Click Create Connection and select Oracle Database.
  5. Enter a connection name, host, port number, user name, and password. When you enter the User name, enter the Oracle Database user name, without the role. Select SID (server ID) or Service, and enter server details.

    Create connection dialog for creating connection to Oracle Database. Name: Oracle Database. Host: myserver.example.com. Port: 1521. User: essbase. Password: (obscured). Service is checked and the value is orclpdb.example.com. Description: Oracle PDB connection

  6. Click Test to validate the connection, and if successful, click Create.
  7. Verify that the connection was created successfully and appears in the list of connections.
    Next, you will create a Datasource for the Oracle Database connection.
  8. Click Datasources, and click Create Datasource.
  9. From the Connection drop-down box, select the name of the connection you just created; for example, Sample.Oracle Database. Application-level connections are prefixed with the application name, in the format appName.connectionName.
  10. Provide a name for the Datasource; for example, OracleDB_DS.
  11. Optionally enter a description of the Datasource; for example, SAMPLE_BASIC_TABLE on Oracle Database.
  12. In the Query field, provide the appropriate SQL query that selects the Oracle Database data you want to make available in this Datasource.

    Create Datasource dialog, General step, for creating Datasource to Oracle Database. Connection: Sample.Oracle Database, Name: OracleDB_DS, Description: SB_DATA table on Oracle Database, Query: select * from SB_DATA

  13. Click Next. If the SQL statement was correct to query an Oracle Database area, The preview of the data source should display up to 10 records of data.

    Create Datasource dialog, Columns step, for creating Datasource to Oracle Database. Index 1: Name: DIMENSION_PRODUCT, Type: String. Several more columns with other names are listed in order, some of type String, and some of type Double.

  14. Change any numeric columns to Double, and click Next.
  15. Change any additional source-specific parameters, if applicable, and click Next. For information about parameter use, see Implement Parameters for Datasources.
  16. Review the preview panel. You should see the results of the SQL query fetching columns of data from Oracle Database.

    Create Datasource dialog, Preview step, for creating Datasource to Oracle Database. Preview data from Oracle Database is shown, with values under the column names.

  17. If the preview looks correct, click Create to finish creating the Datasource.