topic tMondrianInput --> MS SSAS MDX? in Talend Studio

tMondrianInput --> MS SSAS MDX?

willm1 — Mon, 11 Aug 2014 18:11:43 GMT

Hello,
I'm trying to execute an MDX query against an MS SSAS OLAP cube using the tMondrianInput component.
Before I go further - I'd like to confirm the use case for tMondrianInput. The Help file states that tMondrianInput "... reads data from relational databases and produces multidimensional data sets based on an MDX query..." I've read the documentation on tMondrianInput and it appears it implements / supports olap4j, an open Java API for OLAP.

First question: Does this component only work against relational databases (taking a 2D dataset and making it multidimensional?) or can it natively execute an MDX query against an OLAP (multidimensional) database? I was presuming that I could do the latter (execute SSAS MDX query directly). I created a simple job to do this, but am getting the following error:

Exception in component tMondrianInput_1 mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:jtds:sqlserver://myserver.com:2383//some_cube_td; JdbcUser=_myuserid;
I'm wondering also whether since Mondrian is ROLAP, it requires a relational DB on the serverside in which it does calculations/rollups from the source olap system?
Second question: If this component can't connect to an MS SSAS OLAP cube - what's the best way to execute an MDX query against MS SSAS from Talend? I've read a lot of posts on the forum, but didn't get any conclusive recommendations...

Other alternatives I'm exploring is executing MDX as soap calls using XMLA - as described here...
Please advise... Thanks.

Re: tMondrianInput --> MS SSAS MDX?

Anonymous — Tue, 12 Aug 2014 10:40:49 GMT

Hi Will,

Another way - link ssas cube from excel through ISAPI. Using this you can directly query the excel file which automatically gets refreshed (Cube) when accessed.
http://msdn.microsoft.com/en-IN/library/gg492140.aspx
Difficult initially, however, once done you can export cube to excel and send that excel to users to access the data from outside.
See below link if it is useful..
http://social.msdn.microsoft.com/forums/sqlserver/en-US/ed6d7e90-0ca0-4b4b-8c5a-9e3757100800/can-we-connect-to-analysis-services-using-java
Hope this helps.
Thanks
Vaibhav

Re: tMondrianInput --> MS SSAS MDX?

Anonymous — Tue, 12 Aug 2014 13:43:10 GMT

Hi Will,
yes Mondrian is a Relational-OLAP tool and translates the MDX query into a SQL query. It looks like there is a configuration issue in the data base configuration of the mondrian cube. The problem is not your job!

Re: tMondrianInput --> MS SSAS MDX?

Anonymous — Tue, 25 Nov 2014 10:54:25 GMT

Hi willm,
I tried some testing since I am also in need of connecting from Talend Open Studio directly to an OLAP Cube stored on a multidimensional database like SSAS.
Taking a look at Microsoft SQL Server Profiler, one can see that tMondrianInput does not connect directly to the cube but to the relational database instead. It translates the MDX query to SQL and returns the output as a multidimensional result set. As jlolling already stated, your error message implies that your connection configuration is not setup properly.
Regarding your second question, as of now the other possibility is to write some java code that uses the OLAP4j API and run it in talend using the tJava component.

Re: tMondrianInput --> MS SSAS MDX?

archienesss — Thu, 12 Mar 2020 20:00:07 GMT

Hello chuynh,

May I please know how were you able to connect to an OLAP cube from Talend? Thanks!