locked
Power BI Desktop with Power Pivot as backend RRS feed

  • Question

  • Hi Team,

    Greetings!

    We are trying to build dashboards using Power BI desktop version with Power Pivot data model as a back end. To import the data to Power BI,we used Import ->Excel workbook contents option and successfully imported the model to Power BI .

    When I tried to refresh the Power BI,It's hitting the backend tables available in the power Pivot model. But my requirement is I need to refresh the Power BI from the power Pivot Datamodel(I'm expecting an option like Import from Power Pivot in Tableau).

    Any help on this is highly appreciated.

    Thanks,

    Balaji


    Tuesday, September 29, 2015 12:14 PM

Answers

  • I was apparently wrong. A Power Pivot workbook can be a refreshable data source when hosted on OneDrive. See here.

    GNet Group BI Consultant

    • Proposed as answer by greggyb Monday, October 5, 2015 11:24 PM
    • Marked as answer by Charlie Liao Thursday, October 8, 2015 9:14 AM
    Monday, October 5, 2015 11:24 PM

All replies

  • Where is the actual source data? Did you create Power Query queries to import into the Power Pivot data model? You are saying "Power BI desktop version" but are then saying you imported from Excel. Are you using Excel with the Power plug-ins or using Power BI Desktop?
    Tuesday, September 29, 2015 8:36 PM
  • Hi Greg,

    We are trying to build POWER BI reports with Power Pivot as a backend(should act like a Datasource). Is it possible to achieve this?

    Thanks,

    Balaji

    Wednesday, September 30, 2015 9:18 AM
  • Why not create the model directly in Power BI Desktop? It offers the full range of querying and data modeling capabilities that you get in Power Pivot for Excel.


    GNet Group BI Consultant

    Wednesday, September 30, 2015 8:13 PM
  • Hi Greg,

    We are trying to build POWER BI reports with Power Pivot as a backend(should act like a Datasource). Is it possible to achieve this?

    Thanks,

    Balaji

    Hi Balaji,

    According to your description, you need to know if we can create a dashboard in Power BI desktop by using PowerPivot as the data source, right?

    With Power BI Desktop, you can connect to data from many different sources. The File category provides the following data connections:

    • Excel
    • CSV
    • XML
    • Text
    • Folder

    Please refer to the link below to see the details.
    https://support.powerbi.com/knowledgebase/articles/471643-data-sources-in-power-bi-desktop

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, October 2, 2015 6:14 AM
  • I had the same problem and I think what Balaji is trying to get is this. 

    If you use the import function in Power BI Desktop to import an Excel file which already contains a data model then it will generally (with some caveats) import that data model fully and the data model becomes replicated in Power BI Desktop. You can then use Power BI Desktop and create dashboards and reports that you publish to PowerBI.com.


    The problem he is having, and that I had is that the import is one-way. If you go and change your data model in the original excel then refresh your data in Power BI Desktop the Power Bi Desktop data model does notg get updated. What he would like, and myself also, is a live connection where the data model is bought in and kept in sync with the Excel file. 

    Why?

    In my case I wanted this because I needed to do more in depth analysis in the Excel file that I couldn't do in PowerBI, I wanted to create numerous pivot tables that I did not want to appear in reports and I wanted to review and modify outputted data in a way that didn't make snese in Power BI desktop. 

    Instead what I am having to do, and it is extremely frustrating, is maintain two identical Data Models. One in the Excel file and one in Power BI Desktop. 

    Now I fully believe there is a more efficient way of doing this but I haven't yet discovered it, any pointers would be more than welcome. 




    Friday, October 2, 2015 11:05 AM
  • The simpler solution is to just build your model in a proper data source, like SSAS Tabular that can back-end both Power Pivot and Power BI.

    Alternately, publish the Excel model to a Power BI site, and use the browser-based report designer. When the Excel model is updated, simply re-upload.



    GNet Group BI Consultant

    Friday, October 2, 2015 1:38 PM
  • Sure, you can use Power BI Desktop or you can use Excel. If you use Excel, make sure in your Power Query to choose "Only create a connection" and import it to the data model, not tables. If you upload that Excel file to Power BI service, you can create reports from the fields that you have imported into the data model.
    Friday, October 2, 2015 2:23 PM
  • Hi Greg,

    Business team wants to retain existing Power Pivot reports and also expecting us to create new visualizations from Power BI. In this case,We need to refresh both Power Pivot & Power BI on every refresh. To avoid this,we are trying to use power pivot as backend.

    Thanks,

    Balaji

    Saturday, October 3, 2015 6:46 PM
  • As I mentioned above, you can publish an Excel Power Pivot workbook to a Power BI site and use the report designer in the browser. In this way you can make updates only to the Power Pivot model and just upload new versions as you develop them. They will seamlessly replace the old version.

    This is the best option for your requirements as you've described them, as there is no way that I am aware of to perform direct queries against a Power Pivot model from Power BI.

    GNet Group BI Consultant

    Sunday, October 4, 2015 2:01 PM
  • I was apparently wrong. A Power Pivot workbook can be a refreshable data source when hosted on OneDrive. See here.

    GNet Group BI Consultant

    • Proposed as answer by greggyb Monday, October 5, 2015 11:24 PM
    • Marked as answer by Charlie Liao Thursday, October 8, 2015 9:14 AM
    Monday, October 5, 2015 11:24 PM