Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Need a workaround for Direct Query causing server to crash

We are running a dashboard which pulls data from an SQL database. We need the data to be live, or almost live (45 minute syncing would be fine). Currently we use Direct Query, which because of the amount of data, crashes the SQL server, and we have been informed by the server hosting company that switching to import mode would likely cause the same problem because the dataset is massive. 

 

I was thinking that a possible approach might be to pull down all the data we need into an Excel spreadsheet, which Power Bi uses. And then every 45 mins, we update said spreadsheet with only new records so that we minimise the amount of demand on the SQL server. 

 

Is my idea even possible?

Need Help

1 Reply

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    Have you already considered incremental refresh to greatly reduce the refresh time, so you could use import mode? If refresh takes >45 min, you could also consider using a hybrid table (if you have Premium or PPU).

     

    Pat