THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

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

Published Thursday, January 12, 2012 3:12 PM by Marco Russo (SQLBI)
Filed under: , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

tova said:

we use "molap.sql" to extract detailed data from subcube (budget`s etl)

January 24, 2012 2:25 AM
 

Marco Russo (SQLBI) said:

But do you use that on a Tabular model?

In Tabular you have DAX too.

January 24, 2012 3:24 AM
 

arun said:

The table name is 'Employee' with the columns

Empname Sal Age

A 8000 25

B 7000 24

C 6000 26

D 9000 25

what is the syntax used to find the 3rd highest. I have tried with all the syntax but no gain.

February 6, 2012 9:27 AM
 

Marco Russo (SQLBI) said:

You should write something like this DAX query

EVALUATE

FILTER(

   ADDCOLUMNS(

       Employee,

       "Rank", RANKX( Employee, Employee[Sal] )

   ),

   [Rank] = 3

)

February 6, 2012 11:38 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement