Service Report stops returning data

[Anonymous]

Hi

We have a Sevice report. This is working fine,  then stops returning any data. The report refreshes without error but returns no data.

The fix is to download the report to desktop, refresh then up load. Then it works for a while then stops again.

The report uses a SQL server account and connects to an azure SQL DB using direct query. 

Any ideas why this happens and how we can fix ?

Thanks in advance.

[Anonymous]

This continues to be a problem. Each month we make a change to some flags in the date dimension, current month, current week. It is a though the date dimension is not refreshed because when the flags change data is no longer returned. (there is a currentmonth=Y in the report filter.

The fix is to download the report refresh and upload. The client is doing this manually every month. I have tried tweeking the sql account. As said in posts above there are a number of schemas in the database. we have not given access to some schemas. I am goin to take that away and give db read to all schemas as I have a hunch that the bug is there.

Next will be be db owner and then full admin 😉 I wonder if the fix could be to not use sql authentication .... can we use an AAD account ?

[Anonymous · Accepted solution]

Finally I think this has been solved. This is a direct query report, so the filter below requires a download and refresh 

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInCurrentMonth([Date])) 

The solution is to change the logic so the query is passed through to the database

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([ThisMonthFlag] = "Y"))

This makes sense now. but I am not sure if this behaviour is by design ?

[v-shex-msft]

HI @Anonymous,

Did you use azure sql datasource?

If this is a case, current it seems like a known issue on azure datasource, I think your scenario may due to engineers fix operations.

Reference:

Power bi Support

Users may not be able to set credentials if they are using OAuth2 for SQL Azure or AS Azure data sources. Engineers are aware of the issue and are working on a fix . Next Update @06/08/2018 14:30 PST

Regards,
Xiaoxin Sheng

[Anonymous]

Thanks Xiaozin,

Yes it is a Azure SQL Datasource. Would the fix you suggested apply when using an SQL account?

[v-shex-msft]

Hi @Anonymous,

This issue seems closed on support page, did this works on your side?

Regards,
Xiaoxin Sheneg

[Anonymous]

Hi,

It has just happened again. Report is working fine then stops returning data. I download the dataset, renenter the SQL creadentials and publish. All is well again.

One observation I could make is that (on this accasion) the report is using a mixture of dbo tables and another schema. On this report I noticed that the dbo schema was returning data and not the other schema. Maybe this is a known issue ?

Thanks

[Anonymous]

Just happened again. Data is only returned from the dbo tables. Download the dataset and refresh then upload to service. All is working again. Client is not happy. Anyone any ideas? Can anyone at Microsoft pick this up ?

[TheNov3mberist]

Been having the same issue. It broke earlier this week when the issues began. Now that it's "resolved", I had to delete the data source from service and republish from the desktop. It appears to be working okay now but we'll see how it goes.