Help connecting KNIME to a SQL Server Analysis Services Olap Cube

KNIME Analytics Platform
1

Hi there,  I am looking for some information on how to connect KNIME to a SQL Server Analysis Services OLAP Cube.  I have tried both JDBC4olap and olap4j, but am having troube in different places.

For olap4j, I have added the olap4j-1.1.0.jar as a driver to my preferences, but it doesn't seem to show up when I go into the 'configure option' on a database connector.  I can't figure out how to have the driver recognized in the preferences so that it is actually usable to configure a DB connector.

For jdbc4olap, I can add it to my preferences as a driver, and see it as a driver option when configuring a database connector, but I can't seem to get the URL syntax figured out, or perhaps I am having some other problem.  Either way, I get the error, "failed to apply settings: Database table place holder (#table#) not replaced." when I try to map the URL in the configure option.

Sorry for the newb questions - Any help and recommendations would be greatly appreciated.  -  Thank you one and all.

2

Hi Dewie,

Can't help you fully I'm afdraid, but at least the "failed to apply settings: Database table place holder (#table#) not replaced." message hints at an unmodified default query. I.e., any DB reader comes with the standard, non-executable "SELECT * FROM #table#" query preconfigured, which obviously requires an actual table name to be filled in at the #table# placeholder.

Good luck figuring out & getting help for the rest, this is what (in my experience) takes most fiddling at first.

Cheers
E

3

Thank you E - didn't even realize I was making that mistake.

4

Any luck going further than only fixing this?

--E

5

I'm still working away - at the moment, there are some challenges in the server side configurations and its been tough to get server side support required to review these configurations and work out the solution.

6

I'm still working away - at the moment, there are some challenges in the server side configurations and its been tough to get server side support required to review these configurations and work out the solution.

7

The URL for jdbc4olap needs to be something like this:

jdbc:jdbc4olap:http://<server>:<port>/OLAP/msmdpump.dll

Disclaimer: Image taken from http://www.jdbc4olap.org/screenshots.html

1024×768

8

Thanks!

E

9

Hello there!  I'm receiving the following error message when trying to connect to a SQL Server Analysis Services Cube using the jdbc4olap driver as descrbed in the earlier part of this thread:

  • "could not create connection to database: org.apache.commons.codec.binary.Base64"

Any thoughts on why I would be receiving this and how I can solve it?

Thanks!

10

Hello all!

We try to use jdbc4olap driver to connect to SAP BW infoproviders (cubes and etc.)

And we faced with the same mistake as Austin writed :

"java.lang.NoClassDefFoundError: org/apache/commons/codec/binary/Base64"

We try to add in knime commons-codec from apache sites, and also jdbc.jar files different versions, but error still occure.

We even try to test on Knime 32 bit, but still have this error.

We shure what URL is correct. So error somewhere on knime | addons.

Please, help us!

Thanks in advance!

11

All,

Has anyone found a solution to this problem? I got to the same error as everyone else:

"java.lang.NoClassDefFoundError: org/apache/commons/codec/binary/Base64"

I want to know if anyone has been successful in getting this to work.

Thanks!

12

Hello,

the jdbc4olap driver requires several jars to be included in the java classpath. To do so in the KNIME Analytics Platform copy all files from the lib directory into the <KNIME INSTALLATION>jre/lib/ext folder prior registering the driver in KNIME as described in the database documentation.

Bye,

Tobias

1 Like
13

Thanks for this. The only issue I am having is that I can’t find any location that resembles <KNIME INSTALLATION>jre/lib/ext

Has this changed or are you able to clarify? I am using KNIME 3.5.2

14

You can ignore now. Think I found it in KNIME/plugins/installation/jre/lib/ext

15

Hi,
yes, the path has changed to plugins\org.knime.binary.jre.win32.x86\jre\lib\ext_.
However you no longer need to add the additional jars into the ext folder but can use the Add directory button in the Database Driver registry page.

image
image621×520 17.7 KB

You only need to make sure that all required files are present in this folder that should not contain any sub-folders.
Bye
Tobias

16

Thanks very much Tobias - this is reallyuseful.

I managed to get the connection attempted at last but am now suffering from some kind of security/other connectivity issue. Our Infrastructure manager thinks it may be related to http vs https.

I am (trying to) connect to Azure SSAS Tabular and get these other error messages (shown below).

In the mean time I’ve actual found a workaround by creating a Linked Server on our local SQL Server instance and then connecting KNIME to that. I can then send DAX/MDX to the SSAS Azure instance through a normal DB connection to SQL Server with openquery. Not ideal though.

The errors in the console are as follows - can you determine what the errors might relate to?
: -

ERROR Database Connector 0:1659 Execute failed: Could not create connection to database: Connect error: Error in soap call: java.security.PrivilegedActionException: com.sun.xml.messaging.saaj.SOAPExceptionImpl: Message send failed
Caused by: null
Cause Stack Trace: java.security.PrivilegedActionException: com.sun.xml.messaging.saaj.SOAPExceptionImpl: Message send failed
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.xml.messaging.saaj.client.p2p.HttpSOAPConnection.call(HttpSOAPConnection.java:140)
at org.jdbc4olap.xmla.XmlaConn.discover(XmlaConn.java:310)
at org.jdbc4olap.xmla.XmlaConn.discoverDatasource(XmlaConn.java:371)
at org.jdbc4olap.xmla.XmlaConn.(XmlaConn.java:110)