locked
Changing data source fails RRS feed

  • Question

  • I am using Power Pivot and I developed a model on the DEV server. Using the Existing Connections icon I edited the server and database names to point the model at the LIVE server and database. When I click the Test Connection button it says it works, but when I click the Refresh All icon I get an error saying it can't connect and to check the connection in the data source connection (i.e. Existing Connections I assume).

    Also, if I open Management Studio using the same user I can connect to the SQL database and query the views, so this suggests it is a Power BI issue, not a user permission issue.

    Any ideas?


    Propdev

    Monday, April 13, 2015 4:19 PM

Answers

  • Hi Propdev,

    According to your description, you connect to another database by changing the existing connection, the issue is that you cannot refresh the data by using Refresh All button, right?

    In a PowerPivot data model, after you have created a connection to an external data source in a Data Model, you can use the Power Pivot add-in to change connection string and table and column mapping. In your scenario, it's hard to give you the root reason that cause this issue based on the limited information, please refer to the links below to see how to troubleshoot PowerPivot refresh data error.
    https://support.office.com/en-sg/article/Make-changes-to-an-existing-data-source-in-Power-Pivot-9cb508d3-1a83-4d83-a944-700ce41332b6
    http://social.technet.microsoft.com/wiki/contents/articles/3870.troubleshoot-powerpivot-data-refresh.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Fiddler69 Monday, April 20, 2015 7:50 AM
    Monday, April 20, 2015 2:10 AM
  • Hi Charlie

    I tried the resources you mention but it wasn't working.

    However, it was fixed late Friday afternoon. It turns out there was something in the way the user was set up that was affecting it. I'm not sure what, as the user could access the data via SQL Server Management Studio. Anyway, it is now fixed and the process as per the links you mentioned now works and I can repoint the model between different environments.


    Propdev

    • Marked as answer by Charlie Liao Tuesday, April 21, 2015 1:00 AM
    Monday, April 20, 2015 7:50 AM

All replies

  • Hi Propdev,

    According to your description, you connect to another database by changing the existing connection, the issue is that you cannot refresh the data by using Refresh All button, right?

    In a PowerPivot data model, after you have created a connection to an external data source in a Data Model, you can use the Power Pivot add-in to change connection string and table and column mapping. In your scenario, it's hard to give you the root reason that cause this issue based on the limited information, please refer to the links below to see how to troubleshoot PowerPivot refresh data error.
    https://support.office.com/en-sg/article/Make-changes-to-an-existing-data-source-in-Power-Pivot-9cb508d3-1a83-4d83-a944-700ce41332b6
    http://social.technet.microsoft.com/wiki/contents/articles/3870.troubleshoot-powerpivot-data-refresh.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Fiddler69 Monday, April 20, 2015 7:50 AM
    Monday, April 20, 2015 2:10 AM
  • Hi Charlie

    I tried the resources you mention but it wasn't working.

    However, it was fixed late Friday afternoon. It turns out there was something in the way the user was set up that was affecting it. I'm not sure what, as the user could access the data via SQL Server Management Studio. Anyway, it is now fixed and the process as per the links you mentioned now works and I can repoint the model between different environments.


    Propdev

    • Marked as answer by Charlie Liao Tuesday, April 21, 2015 1:00 AM
    Monday, April 20, 2015 7:50 AM