This project has moved and is read-only. For the latest updates, please go here.

Example of Querying from Tabular Server?

May 9, 2013 at 9:33 PM
I've seen the following example in a few blog posts:
evaluate

values( SomeTableName[SomeColumnName] )
And it seems to work great if DAX Studio detects a PowerPivot column. I can't seem to get it to work though when connecting directly to our Tabular server. Am I missing some syntax for specifying the cube?
May 10, 2013 at 12:12 AM
The syntax is identical for querying PowerPivot or Tabular servers.

Do you get an error when you try to run the query? Have you tried running the same query in an MDX or DMX window in SSMS?

There is no concept of a "cube" as such in tabular when using DAX you simply have a database with tables. The only slight difference when querying a tabular server is that you may need to choose which database to query using the database combo box in the toolbar because a tabular server can have multiple databases where as a PowerPivot workbook only has the one database.
May 10, 2013 at 1:25 PM
Hey dgosbell, thanks for your quick reply (and your awesome project!)

It's weird, when I drag over a column and drop it into the values( ) function, it works great when I'm pulling from a PivotTable. I just can't seem to get it to work when connected directly to the Tabular database. This is true in SSMS and Dax Studio -- sorry, should have checked SSMS before.

The error I get is,
Query (3, 1) Parser: The syntax for 'values' is incorrect.
Here's a screenshot,
Image

You can probably tell that I work in Higher Education. Still, I wonder why it doesn't work? I can't seem to find much documentation on the EVALUATE statement.
May 10, 2013 at 2:31 PM
Even though I thought I heard our IT guys say that the instance of Analysis Services was running in Tabular mode -- I'm wondering if it could be in Multidimensional instead. I'm going to throw on my own installed of SQL server and try writing queries against that.
May 10, 2013 at 3:04 PM
I can see that your version in the lower left corner is 10.50 this would make your server 2008R2 - so the only way it could be in a tabular like mode would be if it was installed in Sharepoint as a PowerPivot instance. Even then DAX queries using evaluate were only first support on SQL 2012. We probably should add some checking for that.
May 10, 2013 at 3:06 PM
@dgosbell Thanks so much for your time!