Querying Tabular Cubes in SSMS
With SQL 2012 looming on the horizon, there is LOTS of buzz around a brand new method of creating SSAS cubes — Tabular Mode. Tabular mode cubes are largely derivative of PowerPivot models, in fact, if you know how to build a PowerPivot model then you know how to build a Tabular Mode cube! It’s exactly the same process.
Now, as the title of this post alludes, I’m not going to talk about how to *build* a Tabular Mode cube, but rather, how we can interact with it in SSMS. You’ll notice when we connect to a Tabular Mode instance of SQL Server 2012, there is a new icon — — to denote that we are looking at a Tabular instance, NOT a multidimensional one.
When I create a New Query, you’ll notice that it defaults to an MDX query. Writing valid MDX works as expected:
Since this is a Tabular Mode cube, I should be able to write a DAX query, right? You’ll notice that there is no option to create a new DAX Query window. Does this mean that DAX can’t be used in SSMS? I sure hope not, I find SSMS to be a great tool to build queries that will be used in a cube calculation. It’s a great environment to slowly build a query from the ground up, and doesn’t require a deployment to be completed to test. So, how can I write a DAX query in SSMS? As it turns out, there is a way — and that way is through the use of the EVALUATE() function. This function basically commands SSMS to evaluate the included DAX query against the currently selected dataset. Using the EVALUATE() function as shown below will enable you to write DAX queries in SSMS, no magic and no third party add-ins required
The ability to write either MDX or DAX queries against the same data source (and get the same results) is a great proof of the BISM architecture in SQL 2012.