Forum Discussion

odraode's avatar
odraode
Frequent Visitor
10 years ago

Excel Pivot connect to SSAS Tabular doesen't work in Power BI Service

I have an worksheet in Excel, with one pivot table, connected to a tabular model deployed to a server. I am not able to navigate it through PowerBi.com

I did it the following steps:

 

  • I uploaded the file to One Drive for Business and  opened  it through PowerBI.com using the "Connect, Manage and View Excel in Power BI" option.
  • When I try to navigate it (from the Reports section), it gives me a warning that says: Be careful. The query to get the data might be unsafe so you should only refresh the workbook if you trust its source. Do you want to refresh the workbook? The options are "Yes" and "No", I click on the "Yes" button.
  • Once I click on the "Yes" button, an error message this time pops up: External Data Refresh Failed. We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh: NameOfMyConnectionToTabularModelInExcel. The only option: OK.

I don’t see any trace in SSAS Profiler (it could be that the request doesn’t arrive to Analysis Services anymore?!).

I do have a gateway setup between PowerBI.com and my tabular model server. I can consume all the reports realized with PowerBI Desktop from that same server through PowerBi.com.

Also, If I use an Excel file with a pivot table using a SQL Server Connection instead of a Tabular one, I am able to navigate it (drillthrough, filter etc).

Is it a way to manipulate the connection behind this excel in order to refresh the spreadsheet?

Thanks in advance for your help. 

Need Help

6 Replies

  • odraode's avatar
    odraode
    Frequent Visitor
    10 years ago

    Hello, there is someone that can help me on this?

     

    Thanks!

  • mitsu's avatar
    mitsu
    Icon for Resolver IV rankResolver IV
    10 years ago

    At this point the excel pivot created on top of the Tabular Model cannot be refreshed in the Power BI.com , which why you see the mentioned error . One would need to use the SSAS as a source on Power Pivot or Power Query create a Pivot on the Power Pivot Model and upload the same on one drive and connect to same using the 'view excel' for the Excel based Pivots to be refreshable in powerbi .com , using the personal gateway . 

     

    However based on the ideas forum the Power Bi team is already working on this . So we should have something to handle this soon .

     

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

     

     

    Hope this helps .

     

     


    Thanks ,
    Mitsu

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      New Analyze in Excel function still does not solve issue of uploading an Exel workbook with a Pivot table connected to a SSAS Model.  Figured new feature + Enterprise Gateway would fix this...any help?

      • xxcorpxx's avatar
        xxcorpxx
        Icon for Advocate V rankAdvocate V
        10 years ago

        I'm still searching for a work around for this.

        There has to be a way to connect an SSAS connected Power Pivot file to Powerbi.com so that it refreshes and funtions properly.

        I'd like to use the charts I have from Excel in Powerbi.com.

         

         

    • odraode's avatar
      odraode
      Frequent Visitor
      10 years ago

      Clear, thanks! Added 3 votes to this!