I have successfully configured auto-refresh my PowerPivot workbook in SPBI. However, I noticed a painful behavior by SPBI.
There are 2 steps when we need to refresh report in Excel.
1. Refresh PowerPivot data from other data sources.
2. Refresh all reports by refreshing the connection - PowerPivot Data.
I noticed SPBI will refresh PowerPivot data automatically based on schedule. However, it doesn’t refresh reports automatically. In order to make reports showing latest data, SPBI will change the connection property and enable “Refresh data when opening file.”.Yes, it will enforce showing latest data but it is pretty painful to refresh reports every time when opening although we know data is not changed in PowerPivot cube. And even worse, it may take minutes to complete the refresh if I build a number of reports
in the workbook. It is very bad user experience to wait for opening a file for minutes.
Ideally, SPBI should refresh PowerPivot cube data, and then automatically trigger refreshing the connection and save in SPBI. So user will see latest data and can open the file instantly.