Possible to use SSAS Tabular On Prem connection with Excel pivot table deployed to SP Online?

[OneWithQuestion]

I understand that you can use Power BI to query back to on prem (or IaaS VMs) of SQL SSAS Tabular models.

Those will support security using the username of the user opening the model/connection.

However, can I deploye Excel workbooks with pivot charts/tables that use SSAS Tabular on prem as their data source into Office 365/SharePoint Online?

Can those Excel workbooks have live connections so when the user clicks refresh they query back to the SSAS Tabular on prem server?

Or do you have to have Power BI Pro to create and consume reports back to SSAS On prem using the AS connector?

Thanks!

[mitsu]

Yes this was suported in the older version of Power Bi i .e Power BI with o365 using Business data connectivity Service . However on the current version the Power BI does not support refresh of the Excel Pivot created Directly on the Tabular Model . The Power Bi would support the refresh of Excel through the Personal Gateway (Which is a Power BI Pro Feature) if the Excel Contains a Data Model . The way we consume our Tabular Model Data in Excel Pivot at this point is by using them via Power Pivot  i .e We create a Data Model in Excel Power Pivot with Analysis Service as a Source and create a Pivot Table Report  on top of the Excel Power Pivot Model .  

Please do refer to this thread and vote if the proposed idea works for you :

https://ideas.powerbi.com/forums/265200-power-bi/suggestions/8984308-ability-to-connect-excel-to-pow...

[OneWithQuestion]

So what you are saying is you take Excel, then build a Power Pivot model IN the Excel workbook (so the data is inside Excel).

Then upload that Excel workbook.

So while the Excel data model used SSAS Tabular as its data source, it isn't still connected to SSAS Tabular, it is showing charts/tables based off its internal PowerPivot model (not querying back out to SSAS Tabular for each data refresh, drill, etc...).

Is that correct?

So there is not a way to take an Excel workbook, point it to SSAS Tabular for a data source and then place it on SP Online (like you can on SP 2013 on prem) and have it refresh/connect to SSAS Tabular?

[mitsu]

Yes there is no way to refresh the excel pivots created directly on the on Prem Tab Models when uploaded on the Power BI cloud app  via O365 . It has to be a part of Excel Power Pivot or Power Query for it to be refreshed  on schedule .

The  Excel Pivot of the Power Pivots models is not connected live . However you can set up the data on the same to refresh hourly or Daily using personal Gateway .

It would have been  a Great Feature to have and we are hoping there will be something on this soon ...   keep voting on the idea .

[Greg_Deckler]

So, what you are asking to do was basically how the old Power BI for O365 worked, not sure about that as a go forward strategy though as I am unclear what the path forward for that is. But basically, Power BI for O365 worked as you described although I am not sure it ever supported SSAS tabular as a refresh option, I believe it did but would have to go find the old documentation on data sources supported for data refresh.