Excel File published to PBI won't refresh SSAS connections

[AnniePower]

We publish both PBIX and Excel/PowerPivot files to the service.  We have an enterprise gateway set up connected to SQL Server, AS-MD & AS-Tabular. 

The PBIX files will refresh to all 3. 

Excel files will refresh when connected to the database, but not SSAS.  It prompts you to install a personal gateway.  So I tried that & it worked!  Why does the personal gateway accept the connection, when the enterprise gateway does not??  I thought enterprise gateway did everything the personal gateway could do?  If I uninstall the personal gateway on the same machine and install an enterprise gateway, it will not refresh.  What am I missing?

[AnniePower]

Something seems to have changed, now we cannot refresh ANY excel files in Power BI, regardless of source and gateway used.  When will there be a fix for this?? 

[flemaistre]

Hello

I have exactly the same issue

Excel 2016 file with powerpivot data model loading data from on-prem SSAS 2016, uploaded into PowerBI won't refresh with the enterprise data gateway. It works fine if the powerpivot data model loads data from SQL 2016, but not SSAS 2016.

It works fine as well with the Personal data gateway. I believe this could be brought up to Microsoft as a potential bug

Thanks

[BhaveshPatel]

All of the connections via the Enterprise Gateway are live connections to the underlying server, so there is no need to have a scheduled refresh. After all you are always using the connection to live connect to the server accessing the data. Enterprise Gateway imports nothing, so if you have really large databases which you are reporting upon, it probably makes sense to user the Enterprise Gateway as nothing gets copied. If you have a security policy which forbids storing data in the cloud, Enterprise Gateway meets that requirement as all of the data is stored locally and is merely accessed when needed, like a web page.

The Personal Gateway takes the data and imports it into Power BI. If you want to extract data from a variety of different places such as an Oracle Database, and Excel Spreadsheets, the Personal Gateway will support this, and the Enterprise Gateway won’t.   Remember the Enterprise Gateway only connects to three different data sources, and Excel and Oracle are not on that list. If you want to manage connection and refresh of the data as the administrator or provide access to the data to everyone who needs it, use the Personal Gateway.

[AnniePower]

Both gateways support live and import for SSAS.  We are using both options for the PBIX files.  Excel files do not have a live option. 

The issue is with a PowerPivot model inside an Excel file, uploaded to the service.  The data source is SSAS.  It will refresh with the Personal Gateway, not the Enterprise Gateway. 

Excel files with PowerPivot models, using SQL Server Database as a source, refresh fine in either gateway.