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. Connection Strings for SQL-Based Connectivity

Connection Strings for SQL-Based Connectivity

To design Essbase load rules that access external sources of data using SQL, you sometimes need to use a connection string. Connection string formats are different depending on the source type: Oracle Call Interface, Oracle Database, Microsoft SQL Server, DB2, MySQL, or Teradata.

The available OCI and DSN-less connection string types are listed below, with syntax and examples.

In the examples, DBName is the name of the external source database containing tables and data.

Oracle Call Interface (OCI)

Syntax: DatabaseServerName:PortNumber/ServiceName

Example: OracleServer123.example.com:1521/orcl.example.com

Oracle Database (DSN-less)

Syntax (SID): oracle://HostName:PortNumber/SID

Example (SID): oracle://somedb99:1234/ORCL

Syntax (ServiceName): ORACLESERVICE:oracle://HostName:PortNumber/Servicename

Example (ServiceName): ORACLESERVICE:oracle://somedb99:1234/esscs.host1.oraclecloud.com

Microsoft SQL Server (DSN-less)

Syntax: sqlserver://HostName:1433:DBName

Example: sqlserver://myMSSQLHost:1433:myDbName

DB2 (DSN-less)

Syntax: db2://HostName:PortNumber:DBName

Example: db2://myDB2Host:1234:myDbName

MySQL (DSN-less)

Syntax: mysql://HostName:3306:DBName

Example: mysql://someHostName:3306:myDbName

Teradata (DSN-less)

Syntax: teradata://host:port/DBName

Example: teradata://192.0.2.110:1025/myDBName