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

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)

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

 

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:

http://www.powerpivotblog.nl/import-data-into-the-model-using-a-macro-in-excel-2013

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)

Marco

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.

Voted!

July 15, 2013 11:12 PM
 

Tas Ranson said:

This is just what we need too.  Please

April 7, 2014 1:15 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