Sanjay Shah, Pune, India on 24 Mar 2016 18:31:16
The latest update of Excel and PowerBI on the Insight Summit is good. But what we also need is the ability to work with an Excel Pivot Table, Pivot Chart where the data resides on premises, as opposed to data being exported or uploaded to the service.
Administrator on 29 Mar 2016 15:55:32
could you provide some more details? where on premises the data will reside, and use the Pivot tables in PBI Excel reports?
- Comments (2)
RE: Excel Pivot Tables in PowerBI with on-prem data
The scenario I see as not being available is having Excel tied to an on premise SSAS Tabular model, publish the Excel to Power BI Service, then being able to render the Excel workbook in the Power BI portal (will use Excel Services) and use the gateway to tie back to the Tabular model.
Currently this is not supported and Excel will throw and error instead of trying to go back to the SSAS Tabular source.
Looks like the gateway is not supported for Excel using Live Connect to SSAS.
RE: Excel Pivot Tables in PowerBI with on-prem data
Hi
1 Thanks for your response.
2 Just as PowerBI dashboards are able to connect to the on prem Tabular Model or the Multi-Dim model using the Enterprise gateway similarly the Excel Pivot Table should be able to connect to the on-prem Tabular / Multi Dim models,
3 Excel Pivot Tables provide a much more efficient way to see large columns of numbers and also the ablity to drill down much more efficiently to the much lower level of details. The formatting capabilities, the total/sub total functionalities and drag and drop abilities are beyond compare.
4 It should also be possible to drill down from a dashboard or a PBI report to an Excel Pivot Table while passing the required filters.
Look fwd to hearing from you for any further doubts.
Best Regards
Sanjay Shah (CA, MCP)
CEO, Prosys Infotech Private Limited, Pune, India.
Microsoft Gold Partner – BI and Data Analytics
9422034643