Hi there,

I am currently writing a paper on Microsoft SSAS and I was wondering if I could ask the Microsoft moderators and the community for some information concerning the languages supported by SSAS.

I understand that SSAS supports both MDX for multidimensional and DAX for tabular models. Hoever, can I use any other language to query data?

For instance? Is it fair to sayt that I could I use T-SQL to query data? If so, do I need to have a specific storage mode configured (i.e.: ROLAP) or could I use T-SQL for queries regardless if I am using ROLAP, HOLAP or even MOLAP?

Also, is it correct to include LINQ in this category, as well as XML for analysis?

If you could provide some literature in your answer that would be great (but no sweat if you can't).

Best Regards,
P.

Hello,

LINQ (Language-Integrated Query) is a .NET programming technology, it has nothing (directly) to do with databases / cubes.

XML for Analysis (XMLA) is an other way to create object / query data from SSAS.

Thanks for that.

What about T-SQL?

Transact-SQL is only for the relation database engine.

In SSAS you can run "SQL like" queries, but only against DMV; see Use Dynamic Management Views (DMVs) to Monitor Analysis Services

So I can't use SQL to query the underlying data? From what I have read on the Internet, it seems that I can use SQL.

http://cwebbbi.wordpress.com/2006/06/14/can-i-run-sql-against-an-analysis-services-cube/

http://sqlblogcasts.com/blogs/drjohn/archive/2008/09/27/mdx-and-sql-combining-relational-and-multi-dimensional-data-into-one-query-result-set.aspx

Hi Pmdci,

Traditionally this was only useful when you wanted to create local cubes from server cubes and the subset of SQL supported by AS was so limited you wouldnt want to use it for anything else. And this stuff isnt documented anywhere, possibly intentionally, didnt help.

Although there are some things that are easier to express in SQL than MDX the acutal subset of SQL that is implemented in here is crippled in some important respects. MDX is so much better suited for BI queries.

As you can see on Chris Webb's Blog, we can use SQL against an Analysis Services cube. However, we can only use it in a very limited way. MDX is much better suited for SSAS cube than a SQL.

Regards,

Yes, the dialect of SQL supported is very, very limited.

Don't forget that SSAS also supports DMX for data mining. You can also use this to query your cube, but again it's not pretty - see http://cwebbbi.wordpress.com/2009/05/20/joining-the-results-of-two-mdx-queries-together/ for example.

Regarding LINQ, there's at least one third-party product that allows you to use it on top of cubes: http://www.agiledesignllc.com/Products.htm

Finally, the very latest versions of SSAS 2012 Multidimenionsional can be queried with DAX as well as MDX (which means that both Tabular and Multidimensional models can be queried with either DAX or MDX): http://cwebbbi.wordpress.com/2013/06/02/analysis-services-multidimensional-now-works-with-power-viewand-why-thats-important/

Chris