Forum Discussion

AndrewTobin's avatar
AndrewTobin
Frequent Visitor
6 years ago

Power BI Report Server Data Sources and Scheduled Refresh

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.

data sources
Report Server
scheduled refresh

5 Replies

  • AndrewTobin's avatar
    AndrewTobin
    Frequent Visitor
    6 years ago

    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's avatar
      AndrewTobin
      Frequent Visitor
      6 years ago

      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's avatar
        AndrewTobin
        Frequent Visitor
        6 years ago

        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.