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.