Using SQL to query Multidimensional and Tabular models #dax #sql #mdx #ssas

Analysis Services answers to query in MDX, in DAX (by now just for Tabular models) and has a limited capability to answer SQL queries. It is not useful for any development or client tool, but I wanted to write a blog post on it in order to be able to retrieve these information I gathered during study of DAX and MDX queries sent to Tabular models.

If you create an OLE DB connection using the MSOLAP driver you can send queries to BISM models according to the following schema:

• Multidimensional (aka UDM/OLAP)
  • MDX
  • SQL
• Tabular
  • DAX
  • MDX
  • SQL

The SQL support exists mainly for creating local cubes from a Multidimensional model and it is also used by BISM Tabular model editor. The feature is not documented and not supported outside the syntax that is required for supporting local cubes (for example, it is used when you create an Offline cube in Excel).

You can see some examples of SQL syntax used to query an OLAP cube in two blog post of 2005/2006: one from Mat Stephen and the other from Chris Webb. You can use the same syntax also to query a BISM Tabular model. In such a case, you can also add the SQLQueryMode=DataKeys in the connection strings in order to improve performance, because there is a more direct translation into a Vertipaq request balanced by further restrictions in SQL syntax.

I haven’t found a good reason to use such a SQL syntax and the fact that it is not documented and not completely supported means that it is not something that should be used anyway. However, I think it is good to know what is the SQL support existent in Analysis Services (someone might remember some presentation of UDM in early days of SQL 2005 beta) and I’d like to get your feedback about reasons for which this feature should be supported (but before answering, please remember this is a very particular SQL dialect, with syntax like NATURAL JOIN and no support for many standard ANSI constructs).