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.
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!
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.