Solved:Refresh fails online for report with data source:Sharepoint Online List + SQL server database

[Anonymous]

Hi,

I have a report where data comes from Sharepoint Online List and on-premise SQL server database. In Power BI Desktop, refresh happens successfully. But in Power BI Services or online, I get the following error when trying to refresh: 

Enterprise Gateway is configured and reused for other sets of reports having SQL Server database as data source, but none using Sharepoint Lists.

Question: Is the combination of Sharepoint Online List and Database not supported for refresh?

If no, what is the workaround? 

Please help!

Thanks and regards,

Deb

[Anonymous · Accepted solution]

Hi,

Thanks to everybody for your tips in problem resolution. It helped me to narrow down to the actual problem while ticking the checklist.

The problem actually had been that I had used 'Merge Queries' to combine data from Sharepoint list and SQL Server database in the Power Query (Transform Data). It works perfectly in Power BI desktop, no problem with data model, reporting and data refresh. But when published, data refresh always failed. So the solution was not to use the 'Merge Queries' option in this case with data from different sources. Then everything worked fine both in Desktop and Reporting Services. 

Best regards,

Deb

[Anonymous]

Have the same problem.

Unfortunately, I can't avoid merge

[v-luwang-msft]

Hi @Anonymous ,

Has your question solved?

The following reason may cause gateway refresh error：

1. Data loading is parallel by default, when PowerBI needs to refresh many Query, refreshing all of them may cause PowerBI to occupy too much system memory and error occurs
2. The gateway may not be the latest version
3. The data source may be missing or changed
4. Unsupported data sources may be refreshed
5. Password changes or expired credentials can cause data refresh failure
6. The desktop version may be out of date due to a problem with the M script in the Power BI Desktop file or Excel workbook
7. The amount of uncompressed data imported is too large to exceed the 1GB limit
8. Problems with Gateway, may be no access, offline, unable to log in, outbound port has abnormalities, etc.
9. Data source definition may be wrongly written

Refre:

Troubleshoot gateways - Power BI

Wish it is helpul for you!

Best Regards

Lucien

[ranbeermakin]

@Anonymous in manage your gateway settings, for this gateway, make sure the below is selected...

[Anonymous]

Hi @ranbeermakin,

I checked that those options that you named are already selected. 

But, then I saw this asking for gateway update to a newer version. So, maybe the gateway update would help resolve the problem. Although I still doubt then why the other datasets using this gateway are getting refreshed successfully.

However, @ranbeermakin and @selimovd , it was relieving to know that creating a data model from sharepoint online list + on-premise sql server database is then supported even in Power BI Report Services.

I will update the issue here once gateway update is done after approval. Thanks to both of you.

Regards,

Deb

[selimovd]

Hey @Anonymous ,

for me it looks like there is an error with Sharepoint Online.

That wouldn't require a gateway. Can you check if it works if you just use the Gateway for the on premises SQL Server and not for Sharepoint?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi Denis,

I have not added Sharepoint list to the on-premise gateway (although tried even with that). Independently, Sharepoint Online List based report and on-premise SQL Server database based report functions perfectly. Only for this report having data model with both as data sources cannot be refreshed at all. 

Best regards,

Deb