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
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.
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:
- Use a (commercial) driver - which I was unable to get to work
- 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?
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.
Yeah sure, thanks for your assistance, very much appreciated
did you solve it? otherwise try to add an extension: Microsoft JDBC Driver For SQL Server!!
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.
It’s doable but ugly, the rough sketch is:
Install MS Analysis Services Client Library in your dev environment and on KNIME Server.
Analysis Services client libraries | Microsoft Docs
Create a PowerShell Script to query AS (there are many tutorials)
For security ingest user/pass as arguments in the script
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.
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
All the best
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.