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

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.

Published Friday, August 19, 2011 3:33 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

 

Nick said:

It should be:

EVALUATE 'Date'

August 19, 2011 6:02 PM
 

Marco Russo (SQLBI) said:

Nick, you're right - I changed the original version

EVALUATE ( Date )

to

EVALUATE Date

Of course, if the name of the table contain some space you have to delimit that name with single quotes.

Thanks,

Marco

August 19, 2011 6:36 PM
 

Tom Snoeren said:

I am familiar with PowerPivot but still have to start exploring the BISM. I am very surprised that the BISM can only be exposed to Excel as 'read-only'(no PowerPivot table but a regular PivotTable). I had expected a way of iterative development where end-users could work on (copies of) BISM in PowerPivot and developers could then import the PowerPivot model into the BISM Development environment to enrich / improve it (e.g. performance tuning or adding role based security for which I understand this can only be done in BISM). Missed change I would say? Importing the data as you describe will not expose calculation formulas which is very limiting if you want to built further on them. Or am i missing something?

January 26, 2012 10:32 AM
 

Marco Russo (SQLBI) said:

Tom,

I feel your pain, but this is how it works now.

I hope in future release they will move towards a model similar to the one you described.

Marco

January 26, 2012 11:56 AM
 

Mark Stacey said:

Has this functionality changed in PowerPivot version 2?

If I take a working version of, eg, EVALUATES 'Dates' which works in SSMS against the tabular mode cube, it doesn't work in the PowerPivot MDX editor

August 26, 2012 2:35 PM
 

Mark Stacey said:

Sorry, never mind: Just type it in the query, not in the query editor.

*Feel free to delete the stupid question*

August 26, 2012 2:38 PM
 

Ilias said:

are there any updates here with Excel 2013? This is a serious limitation for typical SSBI scenarios, where IT has deployed a solution in SSAS and business users want to "play" with the data, which involves editing the model, using Power Pivot.

Any info is much appreciated

September 21, 2012 8:14 AM
 

Marco Russo said:

I'm worried there are no good news in Excel 2013. You can already test the beta and the user interface of this feature didn't changed.

September 21, 2012 1:28 PM
 

Binh Cao said:

Marco might have updated this somewhere but just in case somebody landed on this old page, here is how you can connect to tabular SSAS model you created on the server from excel 2013....

1. from excel 2013, on the DATA menu on top, choose "From Other Sources"

2. Select "From Analysis Services"

3. Put in your SSAS Tabular instance name and use "Windows Authentication"

4. choose "Model" as your cube (unless you renamed the tabular model to something else, the default is Model)

5.  Click on "Finish"

6.  choose to view this data via "Power View Report"

   and click on "OK"

7. you should now see Power View report with all the tabular model tables and field lists on the right-hand side.  Now you can work with your tabular model data that is deployed to the server from within Excel Client 2013.

March 7, 2013 3:59 PM
 

Marco Russo (SQLBI) said:

Binh, in this way you are connecting Power View to a Tabular model, but you are not importing data in a PowerPivot model - it's not the same thing.

March 8, 2013 8:32 AM
 

chaitu said:

Hi,

Is there any way to get only few years data from a tabular model cube into excel pivot?

(my tabular model has two perspectives and no partitions)

I don’t want to filter the entire dataset in excel, for better performance I want to get only few years data into excel pivot while loading

Thanks in advance.

September 9, 2014 12:27 AM
 

Marco Russo (SQLBI) said:

You should use CALCULATETABLE applying a filter. You can also use FILTER, but CALCULATETABLE usually provides better performance, even if not always (see http://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/ for more details).

September 9, 2014 1:48 AM
 

chaitu said:

But I cannot make any change to existing tabular model, any change /filter should be applied to excel pivot while pulling data from tabular cube.

September 9, 2014 2:10 AM
 

Marco Russo (SQLBI) said:

If you extract data from Tabular into a Power Pivot tabular model, you have to edit the DAX Query for doing that.

If you are connecting a Pivot Table to a Tabular model, just apply a filter to the pivot table.

September 9, 2014 2:46 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