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 ...
data sources
Report Server
scheduled refresh
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.

  • AndrewTobin's avatar
    AndrewTobin
    Frequent Visitor
    6 years ago

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

      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.