Forum Discussion

OneWithQuestion's avatar
OneWithQuestion
Icon for Post Prodigy rankPost Prodigy
10 years ago

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

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. ...
Need Help
mitsu's avatar
mitsu
Icon for Resolver IV rankResolver IV
10 years ago

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-power-bi-data-model-an

 

 

 

 

 

  • OneWithQuestion's avatar
    OneWithQuestion
    Icon for Post Prodigy rankPost Prodigy
    10 years ago

    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's avatar
      mitsu
      Icon for Resolver IV rankResolver IV
      10 years ago

      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 .