Microsoft SharePoint 2013 Excel Services & External Data Refresh Failed Message

Microsoft SharePoint 2013 Excel Services - External Data Refresh Failed Message

Here is that scary message:

External Data Refresh Failed

An error occurred while working on the Data Model in the workbook.

Please try again.

We were unable to refresh one or more data connections in this workbook.

The following connections failed to refresh:

Resolution:

1) Access the PWA from your server

2) Download any built in report from Reports section (in your case Project Server - Issue Data Project Server - Risk Data) to your server.

3) Open report in excel, go to Data ->Connections

4) In Workbook Connection dialog open "Project Server - Issue Data" go to Properties.

5) Here Select Usage & check all (tick) options in Usage -> Refresh Control

6) Now select tab 'Definition' under connection file remove tick for 'Always use connection file' & Tweak your connection string and tick Save Password option

7) Now select Authentication Settings in Excel Services Area

8) In Authentication Settings select Windows Authentication. This will use your Windows based authentication to access Database and files over the network, if you have been assigned appropriate permissions this whole process will help you get rid of that "Refresh Message".

9) Now, save & publish this report on PWA.