Importing tables from #BISM Tabular model in #PowerPivot

A BISM Tabular model can be browsed in Excel by using just the standard PivotTable like it was a regular Analysis Services cube (that now we call a Multidimensional model). If you are used to PowerPivot, this seems a limitation, because you cannot interact with your data by adding linked tables and modifying the data model. Moreover, because you cannot use the PowerPivot Field List pane when you are connected to a Tabular model, you also lose the ability to define implicit measures on the fly and to move attributes to slicers.

If you are interested in doing these things, you might consider importing data (or a subset of) into a local PowerPivot workbook. To do that, you might use the standard user interface for importing data from Analysis Services, writing an MDX query that is used to extract data. However, there are many issues when you import data from Analysis Services to PowerPivot that are still not solved in CTP3. For example, measures are imported as text and you have to lose time by correcting data type and then import data again in order to get the right data in your model. However, there is a completely different approach that you can use: you can use DAX instead of MDX. The clear advantage of using DAX is that you get better performance and a mode direct access to the underlying Tabular structure. For example, if you want to import all the columns and rows from a Date table published in a BISM Tabular model, you can simply write:

EVALUATE Date

This DAX query can be written instead of an MDX one. Of course, you don’t have a visual editor for DAX but if you look at the documentation of Table Query Syntax, you will discover that the syntax is pretty easy for simple queries.

 

By using DAX, you get the right metadata for the returned column and you don’t have to lose time cleaning the data type for all the returned columns. Not to mention that DAX performances for these queries are very good. In the (near) future I would like to see an “Import with DAX” dedicated dialog box for drillthrough actions definition.