THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Common request: export #Tabular model and data to #PowerPivot

I received this request in many courses, messages and also forum discussions: having an Analysis Services Tabular model, it would be nice being able to extract a correspondent PowerPivot data model. In order of priority, here are the specific feature people (including me) would like to see:

  • Create an empty PowerPivot workbook with the same data model of a Tabular model
  • Change the connections of the tables in the PowerPivot workbook extracting data from the Tabular data model
    • Every table should have an EVALUATE ‘TableName’ query in DAX
  • Apply a filter to data extracted from every table
    • For example, you might want to extract all data for a single country or year or customer group
    • Using the same technique of applying filter used for role based security would be nice
  • Expose an API to automate the process of creating a PowerPivot workbook
    • Use case: prepare one workbook for every employee containing only its data, that he can use offline
    • Common request for salespeople who want a mini-BI tool to use in front of the customer/lead/supplier, regardless of a connection available

This feature would increase the adoption of PowerPivot and Tabular (and, therefore, Business Intelligence licenses instead of Standard), and would probably raise the sales of Office 2013 / Office 365 driven by ISV, who are the companies who requests this feature more. If Microsoft would do this, it would be acceptable it only works on Office 2013. But if a third-party will do that, it will make sense (for their revenues) to cover both Excel 2010 and Excel 2013.

Another important reason for this feature is that the “Offline cube” feature that you have in Excel is not available when your PivotTable is connected to a Tabular model, but it can only be used when you connect to Analysis Services Multidimensional.

If you think this is an important features, you can vote this Connect item.

Published Wednesday, June 19, 2013 3:04 PM by Marco Russo (SQLBI)



Greg Galloway said:

I completely agree. Voted.

With Excel 2013 and VBA, you can automate the import of basic tables. Certainly not a full solution, but until we get something better, it's something:

June 19, 2013 10:03 PM

Marco Russo (SQLBI) said:

Greg - yes, I know, but the problem is creating at least the measures!

Not to mention other properties of columns and measures (I think column formatting cannot be customized in Excel)


June 20, 2013 2:55 AM

Pablo Doval said:

This would be a wonderful addition; for example, to be able to use Geoflow with the data from a tabular source.

This one's got my vote! thanks for opening the ítem, Marco!

June 20, 2013 2:19 PM

Di said:

Agree and voted.

This is what I am looking for quite a while. This is very useful in my business environment because we can use Tabular for entire company. Meanwhile, we can provide same PowerPivot with special customization to fulfill special needs within a small group. So that everybody referring same common data.

And also, PowerPivot can fulfill the needs of offline cube so that people esp. sales, who are on the road w/o internet access, can still do quick analysis.

Thank you Marco.

June 20, 2013 11:55 PM

Mark Fitzpatrick said:

Completely agree. I was hoping that with the availability of the PowerPivot API from excel we could just use some code in excel to access the tabular model and instantiate a PowerPivot model in excel dropping off the unsupported features as we go, but alas a whole bunch of the key objects are read-only.


July 15, 2013 11:12 PM

Tas Ranson said:

This is just what we need too.  Please

April 7, 2014 1:15 AM

Tommy said:

Hi Marco,

Any idea if i could export the powerpivot table from the excel 2013 to SQL server 2012?

June 12, 2014 4:17 AM

Marco Russo (SQLBI) said:

Tommy, this is supported in both SQL Server Management Studio (Restore from PowerPivot) and SQL Server Data Tools (Create Project from Power Pivot).

June 12, 2014 4:29 AM

Tommy said:

Hi  Marco,

Thanks for your advice. My SQL SERVER  2012 is the  Express with Advance Services version with no SSAS installed. When I attempting the first option of "Restore from Powerpivot"  I can't  see that option available when right click the Databases in SSMS. I guess if that because my SQL server version is Express Version?

June 12, 2014 9:11 PM

Marco Russo (SQLBI) said:


yes, Analysis Services Tabular is only included in Business Intelligence and Enterprise versions of SQL Server.


June 12, 2014 9:13 PM

Hans Peeters said:

Hi Marco,

Do you have an idea if and when above functionalities are going to be added to the Tabular / PowerPivot model? I would really like the offline mode in combination with the existing row level security. But I cant find anything of the roadmap microsoft is following.

Thanks in advance!

December 12, 2014 7:50 AM

Marco Russo (SQLBI) said:

Sorry, no news about that.

December 12, 2014 8:01 AM

Vivek said:

In the absence of this functionality, is there a solution using Powerpivot that can provide row level security i.e. When user refreshes powerpivot model, powerpivot should download data pertaining to that user alone?

December 30, 2015 7:02 AM

Marco Russo (SQLBI) said:

You have to implement that on the SQL Server side. SQL Server 2016 has row level security, you can create one schema per user with a different version of the view that loads data for every user - then load the view without specifying the schema in Power Pivot and use the default schema for the user at the logon - every user will load data from its own version of the view.

December 30, 2015 7:11 AM

Vivek said:

Thanks Marco for your quick response.

Must say, I'm quite surprised that no one has had to build something that achieves row level security in some way for power pivot.  

In effect, all that is needed is having an ability to pass a parameter(user id) to a where clause of a base query.

How about Power Query... can it fetch data from SQL Server based on a user's Windows login?

December 30, 2015 8:26 AM

Marco Russo (SQLBI) said:

Vivek, in terms of security, is not a feature that you can build client side (it would not be secure at all...), it has to be built server-side. As I said in my previous reply, it can be implemented in SQL 2016 using row-level security, or through views tied to default schema per-user in previous versions of SQL Server.

December 30, 2015 8:43 AM

Colin said:

November 16, 2016 12:07 AM

John said:

Hi Colin

I have tried the link you posted; I cannot get it to work; I think my issue is my tabular model was created in SQL 2016.  On opening the workbook, and attempting to connect to the PowerPivot model I receive an error message implying the model is in some way corrupt.

September 4, 2017 6:25 AM
New Comments to this post are disabled

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



Privacy Statement