Power BI Report Server Data Sources and Scheduled Refresh

[AndrewTobin]

I'm having several problems with Power BI Report Server that I hope someone can help with.

When I first uploaded my model, the rendered page was completely blank, and when I went to the Settings to adjust the Data Sources I was getting "System.InvalidOperationException: Authentication type Integrated is not supported."

I then upgraded from June 2019 to September 2019 release and rebooted the server.

It was still not working, so I deleted the model from the server, checked the data sources and re-uploaded the model.  The data sources then appeared, and allowed me to change them, but the Scheduled Refresh pane gave me an issue saying (paraphrasing, unfortunately I no longer have this message): "Cannot refresh when there is a DirectQuery data source"

I double-checked the model and there were no DirectQuery data sources.

I then recreated the data sources in the model, it is a mix of SQL (on premise) and Excel, and I have even removed the Excel data sources just to make sure it was only the Import SQL sources, and re-uploaded.

Now, there are no Data Sources showing in the settings for the model on the server.  The section for Data Sources is inactive, and cannot be clicked, and the error message on the scheduled refresh pane is "Scheduled Data Refresh is not available for reports. The report does not have any external data connections defined or is using an unsupported data source."

The report is rendering, but we want it to refresh at 2am each day and so this is causing a lot of frustration, and a lot of annoyance with our management.

Tangential: It is also really frustration that most errors, including why a report won't upload from Power BI Desktop to the server, show no appropriate messages, and the underlying issue needs to be investigated against a log on the server.  When a problem occurs uploading the user only gets a message that it couldn't be uploaded, and not why.  Any errors to do with the server are opaque and again you need to read the logs.  Really poor experience.

[AndrewTobin]

I have deleted the file on the server, and re-uploaded via file upload instead of from the Desktop client and the Data Sources have re-appeared.

The problem is, the rendered model is blank again.

I have tried to change the data sources, and I am getting an error: "Keyword not supported" due to incorrectly formed connection strings from the UI.

Keyword not supported: 'SERVERNAME-04\reports;reports;user id'.

And again, the Scheduled Refresh has an error: "Scheduled Data Refresh is not available for reports using Direct Query or a Live Connection to SQL Server Analysis Services."

There are no Direct Query connections in my data sources, and both SQL and Excel are supported as refresh enabled data sources according to the Docs documentation.

[AndrewTobin]

I profiled the query from the server in SQL Profiler and found the underlying table - the datasources were set to DirectQuery, even though they are definitely set as Import on the model in Power BI Desktop.

I manually altered them to Import and I can now change the datasources, but on the Scheduled Refresh it is still saying that there are DirectQuery sources and it can't be refreshed.

SELECT *
FROM DataModelDataSource
WHERE ItemId = '9BB99F05-962A-4672-B5CC-33421417BB0D'

--UPDATE DataModelDataSource
--SET DSType = 'Import'
--WHERE ItemId = '9BB99F05-962A-4672-B5CC-33421417BB0D'

[AndrewTobin]

So, there was one more reference in the Catalog properties for <HasDirectQuery>True</HasDirectQuery> that I had to change.

I have no idea why the model is so persistent in thinking it has DirectQuery.  Zero, none, nada, zilch of the queries point to a data source with Direct Query.  They're all Import.  I've re-created them all.

I wonder if there's something in the model that if it ever has a DirectQuery that it's always flagged as DirectQuery.  I would love if anyone with some knowledge could explain this to me.  If there is some magical flag on the model in Power BI Desktop that I've completely overlooked, then I'd love to know about it.  I would super love to never have to go thru this again.

[AndrewTobin]

So, while that seems to solve it temporarily - even if you download the file back from the server the next time you upload it again it switches back to DirectQuery.

Am I just doing this completely wrong? I really need this to refresh and short of re-developing a huge solution from scratch again, I can't think of anything to do.  And there's no guarantee that would solve it either.

[robertjordanmmc]

Here is how I was able to resolve this issue:

export Report as [reportName].pbit (can use .pbix as well, just smaller)

rename [reportName].pbit to [reportName].zip

extract all

edit "DataModelSchema" with notepad

update "defaultMode": "directQuery", to "defaultMode": "import",

Highlight all files, Right Click-> Send to -> Compressed (zipped) folder

rename resulting zip to [reportName].pbit

Now when it is deployed, it no longer has any issues. Re-downloading should now reflect the fix.