Importing/DirectQuerying MONSTROUS database

[Markets]

Hi! I made my first reports with PBID with any issues against my test SQL database. It works great, but when I launch it against the current SQL database (TOO BIG DB, millions of rows at some tables), the server can't afford it and shuts down, using import or direct query. I was reading about ways to improve performance and keep low latency, and I found some demos where reports use live connection against huge databases. I was trying to optimize my querys, using extrictly column and rows I really need, but it crashes in the same way.

The most common advice for this, is deploying one SSAS at my SQL server and model the data into multidimensional cubes, in order to consume that data from my reports, but I think that solution is not enough for my case.

Is there some way to deal with that trouble without going through SSAS and cubes?

Thanks a lot!

[Anonymous]

Hi @Markets,

There are some proposals for you troubleshoot the above issue.

1. Download the latest version of Power BI Desktop(2.42.4611.901) and install 64 bit Power BI Desktop.

2. Make optimization at SQL Server database level, for example, add proper indexes. For more details about optimization and performance turning tips, please review the following blogs.

https://host4asp.net/optimize-sql-databases/
https://www.codeproject.com/Articles/34372/Top-steps-to-optimize-data-access-in-SQL-Server

3. Connect to SQL Server database via DirectQuery mode with restricted rows and columns.

If Power BI Desktop still crashes, please go to File -> Options and settings -> Options -> Diagnostics in Power BI Desktop and click "enable tracing", then re-connect to SQL Server database and check the detailed logs in the traces folder. Also please help to post the detailed logs here.

Thanks,
Lydia Zhang