CODEWITHSUNDEEP
oracle-databasepentahoolapmondriantns
Bagdat
Bagdat

Reputation: 328

Mondrian cannot use oracle TNS name as JDBC Data source

Could someone please help with the following issue:

I have 2 JDBC Data sources created in Pentaho BA Server:

  1. Oracle Connection with provided user credentials, host, port and SID as database name.
  2. Oracle Connections with provided user credentials and TNS name as database name, since if there is SERVICE NAME then I should use TNS name.

Both above connections ping successfully.

When I use the #1 Option as JDBC Data source for my Cube schema - everything works.

But, when I use the #2 Option, cube cannot be loaded and the following exception occurs in the log file:

java.lang.RuntimeException: Unable to get connnection 
jdbc:mondrianataSource=Report DB; EnableXmla=false; overwrite=false; 
Locale=en_US; Catalog=mondrian:/My_Cube_Schema; UseContentChecksum=true
at com.pentaho.analyzer.service.impl.OlapConnectionManagerImpl.createConnection(SourceFile:152)
at com.pentaho.analyzer.service.impl.OlapConnectionManagerImpl.createConnection(SourceFile:75)
at com.pentaho.analyzer.service.impl.a.getConnection(SourceFile:55)

So, the question is, if it is possible to use the #2 Option, as I have just SERVICE NAME as for connection to DB.

Any help would be appreciated!

Upvotes: 0

Views: 441

Answers (2)

Bagdat
Bagdat

Reputation: 328

I found a workaround, but will not mark it as answer since I was searching the solution that works with TNSName.

From the following page http://www.orafaq.com/wiki/JDBC I found 3 valid ways of JDBC URLs:

  1. By SID
    jdbc:oracle:thin:[<user>/<password>]@<host>[:<port>]:<SID>

Ex.: jdbc:oracle:thin:@myhost:1521:orcl

  1. By Service name
    jdbc:oracle:thin:[<user>/<password>]@//<host>[:<port>]/<service>

Ex.: jdbc:oracle:thin:@//myhost:1521/orcl

  1. By TNSName
    jdbc:oracle:thin:[<user>/<password>]@<TNSName>

Ex.: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<host>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<service>)))

Here are my screenshots for my cases described in my question:
For the #1 Option:
Oracle Connection with provided user credentials, host, port and SID as database name enter image description here

For the #2 Option:
Oracle Connections with provided user credentials and TNS name as database name, since if there is SERVICE NAME then I should use TNS name. enter image description here

And finally, the solution that worked for my current situation (Note that Service name is provided with slash): enter image description here

Upvotes: 1

Helping Hand..
Helping Hand..

Reputation: 2440

The reason behind the issue is connection-name. Connection name should be same in PSW and BA server that is the basic requirement.

Upvotes: 1

Related Questions