Connecting to Oracle

This topic describes how to add data from Oracle to your data model using Live Connect or an ElastiCube .

Sisense enables quick and easy access to databases and tables, and to views contained within Oracle databases.

To add data from Oracle to your data model:

  1. In the Data page, open a Live model, open an ElastiCube, or create a new ElastiCube .
    For more information on Live models, see Creating Live Models and Adding Live Connections .

  2. Click . The Add Data dialog box is displayed.

  3. Select your live Oracle data source:


    Or select theOracle connector:

The Oracle settings form opens.

Service ID (SID)

  • Location : Enter the address of the remote server to connect to in the format host:port .
  • User name and Password : Enter the username and password to connect to the database
  • Service ID and Port : Enter your Service ID. These can be found in the TNSNAmes.ora file. You can find a full description in the following topic: Service ID .
  • (Optional) Additional Parameters : Enter additional configuration options by appending key-value pairs to the connection string (for example, Schema=XXX;). For a full list of connection string options, click here .

Service Name

  • Location : Enter the address of the remote server to connect to with the following string:
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$your_ip)(PORT=$your_port))(CONNECT_DATA=(SERVICE_NAME=$service_name))).
    Enter your own values to replace the following:

  • $your_ip

  • $your_port

  • $service_name
    These values can be found in the TNSNAmes.ora file. You can find a full description in the following topic: Service ID.

  • User name and Password : Either use your Windows login details if they are configured with the database, or enter the username and password to connect to the database.

  • Enter the Service name and Port .

  • (Optional) Additional Parameters : Enter additional configuration options by appending key-value pairs to the connection string (for example, Schema=XXX;). For a full list of connection string options, click here .

TNS

  • User name and Password : Either use your Windows login details if they are configured with the database, or enter the username and password to connect to the database.
  • TNS_ADMIN path : Enter the full path of the .ora file
  • (Optional) Network Alias : Enter the network alias, if there is one.
  • (Optional) Additional Parameters : Enter additional configuration options by appending key-value pairs to the connection string (for example, Schema=XXX;). For a full list of connection string options, click here .

Custom

  • User name and Password : Either use your Windows login details if they are configured with the database, or enter the username and password to connect to the database.
  • URL connection string : The JDBC connection string.

  1. Click . A list of tables in the database is displayed. All tables and views associated with the database appear in a new window.

  2. From the Tables list, select the relevant table or view you want to work with. You can click next to the relevant table or view to see a preview of the data inside it. When you select the table or view, two new options are displayed at the bottom of the list, Import Relationships and Add Live Table Query (for Live)/Add Custom Import SQL (for ElastiCube )

  3. Enter the following details, depending on the method you use to connect:

Using Oracle Service ID (SID) :

  • Location : Enter the computer/server IP address of the database. To connect to a database running on your own computer, enter localhost .
  • User name and Password : Either use your Windows login details if they are configured with the database, or enter the username and password to connect to the database.
  • Select Use Direct Connection. Enter the Service ID and Port number to connect.
  • Service ID and Port : Enter your Service ID. These can be found in the TNSNAmes.ora file. You can find a full description in the following topic: Service ID .

Using Service Name :

  • Location : Enter the following string: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=$your_ip)(PORT=$your_port))(CONNECT_DATA=(SERVICE_NAME=$service_name))).
    Enter your values instead of the below variables. These can be found in the TNSNAmes.ora file. You can find a full description in the following topic: Service ID.
  • $your_ip
  • $your_port
  • $service_name
  • User name and Password : Either use your Windows login details if they are configured with the database, or enter the username and password to connect to the database.
  • Make sure that Use Direct Connection is clear.
  • Additional Parameters : (Optionally) Enter additional configuration options by appending key-value pairs to the connection string (e.g., Schema=XXX;). For a full list of connection string options, click here .

  1. Click . A list of tables in the database are displayed. All tables and views associated with the database will appear in a new window.

  2. From the Tables list, select the relevant table or view you want to work with. You can click next to the relevant table or view to see a preview of the data inside it. When you select the table or view, two new options are displayed at the bottom of the list, Import Relationships and Add Custom Import SQL .

  3. (Optional) By default, existing relationships between tables are automatically replicated in the ElastiCube . You can disable this by toggling the Import Relationships switch.

  4. (Optional) Click + to to use SQL to customize the data See Creating Custom Live Table Queries or Importing Data with Custom Queries for more information.

  5. After you have selected all the relevant tables, click Done . The tables are added to your schema in Sisense .

.r.