cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Markets Frequent Visitor
Frequent Visitor

Importing/DirectQuerying MONSTROUS database

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!

1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Importing/DirectQuerying MONSTROUS database

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)