Run a MDX/DAX query on SQL Server database in analysis mode (SSAS)?

Matillion ETL
1

Hi - we have a SQL Server database in analysis mode that we need to connect to and query to return its calculated results. The query is MDX - is it possible to use the Database Query component to run this type of query?

2

Hi @Singularity​,

Interesting question... Matillion's Database Query component can connect to anything which has a JDBC Type 4 driver. So it's actually a question of whether or not there's a Type 4 JDBC driver for SSAS. A few years ago I would have said a cautious yes in the form of the olap4j driver. But it looks like that no longer exists, and I don't know of an alternative.

From an architectural perspective, you might be better to hit the SQL Server source in SQL mode (rather than MDX, which is not relational).. then let your CDW (e.g. Synapse, or Snowflake) do the aggregations in a highly scalable way.

Best regards,

Ian

3

One option would be to get a 3rd party JDBC driver from someone like CDATA (https://www.cdata.com/drivers/ssas/jdbc/). I have used their drivers in years past for projects. I don't know how good this particular driver is or how much it costs but it might be worth a look. BTW: I have no affiliation with them, I have just used other drivers of theirs in the past with success.

4

Thanks @ian.funnell (Matillion Limited)​ and agreed that hitting it in SQL mode for the underlying data to do our own aggregations would be the most ideal way in the long term, but it is the aggregations performed on the database we're connecting to that we're looking to leverage. Will explore some options.

5

Thanks @Bryan​,great idea. I'm definitely going to reach out to them and see what the options are. Presumably this would need to be run in a custom Java application outside of the Matillion instance but triggered by a Matillion job via bash script component? The Java application would post the query results to S3 that the rest of the Matillion job could then read in to CDW (Snowflake)?

6

*** I will preface the next statement with the fact that it works with the typical database systems but I am uncertain of SSAS as it is a little different than your typical database.

 

You should be able to upload the JDBC driver into Matillion's Database Driver Manager and then use it within the Database Query component.

 

If the above doesn't work then yes, you would likely need a separate application to read the data. The other approach could be to create an SSIS package that would read the data and output it to a file which could be moved to S3 for loading purposes.

 

I hope this helps.

7

@Bryan​ yes, that is very helpful. I will follow up once I explore more with Cdata, hopefully with positive news.