Visualizations not Loading

[supton]

Hello! So I seem to have run into an issue. I have Direct Query set up in my Power BI environment, and I am able to connect to an Azure SQL database just fine. However, for one particular table with >1 Million records, none of the visualizations load. The visualizations state "The operation was cancelled because of locking conflicts". I am wondering if this has to do with the amount of data that is in that particular table and I am looking for suggestions about the best way to report (using direct query) on large sets of data like this. What is the best workaround so that my visualizations will load? I noticed here that there is a 1 million record data limit that gets returned (https://community.powerbi.com/t5/Service/direct-query-limitation/m-p/122894/highlight/true#M18973). But visualizations seem to still be failing for me even when I am performing a calculation on the visualization (for example, dealing with a sum of one of the columns). And does this 1 million limit prevent anything from being returned? And what would be the best way around that? Thank you!

[buzz196270]

It seems like we're just maxing out the program. Microsoft should proactively program around this by offering adaptable memory for big data. 

It's not server side if you get this error;

1. If you receive it, you probably know what you're doing to get into this trouble anyway

2. I was linking too many CSV's together (not even using a server connection)

Make a better program (?)

[v-qiuyu-msft]

Hi @supton,

From the error message, I guess the issue might be related to Azure SQL database. In your scenario, assume you specify the query to get data from Azure SQL database, please run the same query in Power BI desktop or SSMS to see if the same error pops out. You can also contact with your DBA to verify if any issue happened to Azure SQL database around this time.

Best Regards,
Qiuyun Yu

[supton]

Thank you for the response! I ran the query on both the azure sql database as well as power BI, and it does take a significant amount of time to run which I am assuming is the issue with the query. But what is a way around this? How do users manage large sets of data then using direct query? It doesnt quite make sense to me that I cant use the import functionality on large data sets because they exceed the data limit but I also can't seem to report on large data sets using direct query. 

[v-qiuyu-msft]

Hi @supton,

You can take a look at this article to know consideration when using DirectQuery mode: Use DirectQuery in Power BI Desktop.

In your scenario, please check if the query runs on the Azure SQL database side and Power BI service can work fine at the same time.  If the query fails on Power BI, I would suggest you create a support ticket to look into your real environment.

You can also import data from Azure SQL database to Power BI desktop, as long as the .pbix file is not more than 1G, you can publish report to service. See: File size limit increase to 1 GB.

Best Regards,
Qiuyun Yu

[haditabani]

Hi @supton Did you figure out a solution to this problem? We were facing the same issue but then just removed some of the relationships we were using which made everything load up quicker.