locked
Remember a connection RRS feed

  • Question

  • Hello. Is there a way to get power pivot or power query to "remember" a database connection so I don't have to reconnect every time I open up excel? 
    Wednesday, November 23, 2016 9:56 PM

Answers

All replies

  • Hello Alex,

    Not very clear, MS Excel / Power Pivot already saves the connection informations and when you click on data refresh it automatically re-connects to the saved data source.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 24, 2016 7:44 AM
  • Hmmm, I see. Let's say I connect to a database, does power pivot mirror that entire database? Can't that data overwhelm power pivot? When you refresh your data does it reimport everything or just the content that is not new? Let's say the database was 30 days, and now a month has passed and I just want new information updated. Thank you. 
    Thursday, November 24, 2016 11:45 AM
  • Power Pivot loads the complete data into it's model, an incremental update is not possible, yet; you can vote for such a feature at MS Connect: Powerpivot incremental data refresh

    Power Pivot uses the xVelocity in-memory engine which has a high performing data compression. You can easily load millions of data row to it, but of cause it's limited by the memory of your client and if you use 32 or 64 bit MS Excel / Power Pivot.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 24, 2016 5:41 PM
  • I see. But if I have a data analysis and then I want to repeat two days later grabbing from the same database connection it refreshes ALL of the data? I suppose this doesn't matter but the SQL analyst said that is inefficient so was just curious as to why she said that? Thanks. 
    Monday, November 28, 2016 11:01 AM
  • Yes, when you refresh the data for your Power Pivot model, then it loads all the data completly again, it does not perform an incremental load = load only new data.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, November 28, 2016 3:23 PM