Connecting to SQL Server Analysis Services

KNIME Analytics Platform
1

Hey everyone

I need to MDX query data from a SQL Server Analysis Services (SSAS) into my KNIME workflows.

This topic has been asked in the past, but it’s been a while ever since, so I hope there is a solution for that by now. I tried (old) suggestions for days, but I’ve not been able to establish a connection that allows me to launch an MDX query and receive back a table into my workflow.

Most older threads refer to either commercial JDBCs drivers (e.g. CData) or jdbc4olap - both I can’t get to work. Did anyone actually manage to connect to a SSAS?

Thanks in advance for any help and best regards
Jan

2

Hi @JanSteinwand,

guess you have seen this topic? how to connect analysis services on Knime

From it I would say @SimoneDePaoli managed to connect to it using DB Connector node.

Br,
Ivan

3

Hi @ipazin

Thank you for the reply and the link. I did indeed see this. I’m not sure, if I get it right, but I think in the linked thread @simonedepaoli describes that it did not solve the problem. It seems his and mine problem are identical.

There are two solutions suggested:

  1. Use a (commercial) driver - which I was unable to get to work
  2. Use the REST API - which is not accessible for the server I try to connect to

Any help would be appreciated. I wonder if this is such an exotic scenario for KNIME to connect to SSAS?

Best regards
Jan

4

Hi @JanSteinwand,

if I got it right he managed to connect to development instance. Anyways let’s wait for someone who can help you a bit more on this one as I don’t have SSAS to try it out.

Br,
Ivan

5

Hi @ipazin
Yeah sure, thanks for your assistance, very much appreciated :slight_smile:
Best regards
Jan

1 Like
6

hi,

did you solve it? otherwise try to add an extension: Microsoft JDBC Driver For SQL Server!!

1 Like
7

No, we unfortunately weren’t able to make this work.

The mentioned Microsoft Driver is - according to my knowledge - only for SQL Server access, I’m using that one to connect to the SQL interface in another use case, and that’s working fine.

This topic however was to to connect to SQL Server Analytics Service, which is more of an OLAP interface. The query language would be MDX instead of SQL.

As mentioned, we have given up on this topic, and we have some workarounds implemented for it.
Best
Jan

8

It’s doable but ugly, the rough sketch is:

  1. Install MS Analysis Services Client Library in your dev environment and on KNIME Server.
    Analysis Services client libraries | Microsoft Docs

  2. Create a PowerShell Script to query AS (there are many tutorials)

  3. For security ingest user/pass as arguments in the script

  4. Back in KNIME embed user/pass in a locked component so it’s encrypted, dynamically generate the Powershell script and save it in a temp directory, then call the script with the user/pass as arguments using External Tool.

Et voila.

1 Like
9

Hi good morning from Switzerland
Thank you very much for the provided information - much appreciated. I hope the SSAS integration will be improved a little in the future, but at least there is a possibility to do it :slight_smile:
All the best
Jan

1 Like
10

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.