topic Re: When using Direct Query to SQL Server - the visual are not loading in the service in Developer

When using Direct Query to SQL Server - the visual are not loading in the service

NithyaKB — Fri, 17 Jun 2022 14:07:58 GMT

Hi
I am using direct query connection mode for SQL Server to Power BI .
The visual load good in desktop.The issue is only when we publish the report to service. I am getting the below error message:

I could see all my gateway is associated with the dataset is online and the refresh is also happening successfully.
Any thoughts?

Re: When using Direct Query to SQL Server - the visual are not loading in the service

Daryl-Lynch-Bzy — Fri, 17 Jun 2022 17:48:47 GMT

Hi @NithyaKB - from the Power BI Desktop use the Performance Analyser to identify the DAX and Direct Query that are being issued. You need to run the queries in DAX Studio to understand how many Storage Engine and Formula Engine queries are being executed. You might find that too many queries are being executed. The Data Gateway might be struggling with the amount and volume of queries. Note that it might not be one visual that is causing the issue, you have to consider all the visuals an how these are concurrently running. If this is not an issue, you then need to consider the Data Gateway logs and check the network latency.

Re: When using Direct Query to SQL Server - the visual are not loading in the service

NithyaKB — Fri, 17 Jun 2022 18:25:10 GMT

Hi @Daryl-Lynch-Bzy

My report has only one 2 visuals which has only 20 rows of data.with no calculations .Its just a direct column from.sql server I have used for testing purpose .My query is as below..

Select name from db.table name...

So here I assume the data is not huge.

Re: When using Direct Query to SQL Server - the visual are not loading in the service

NithyaKB — Fri, 17 Jun 2022 18:26:33 GMT

Hi @Daryl-Lynch-Bzy

Also..my.visuals are loading.fine in desktop .I am facing the issue only when loading the reports in service.

Re: When using Direct Query to SQL Server - the visual are not loading in the service

Anonymous — Tue, 21 Jun 2022 02:33:05 GMT

Hi @NithyaKB ,

First, you can review the following blog about explanation of DirectQuery for Power BI dataset and check if any step or setting is missing to cause the error...

DirectQuery for Power BI dataset: How does it work?

Note: Please make sure the privacy level setting and the credential info in Service is same with the one in Power BI Desktop....

In addition, the following ones are the similar problems as yours. Please check their solutions, hope they also can help you resolve the problem.

"Couldn't load the data for this visual" error with Composite Dataset in direct query mode

Report couldn't access data source in Direct Query mode

Best Regards

Re: When using Direct Query to SQL Server - the visual are not loading in the service

Anonymous — Thu, 30 Jun 2022 03:03:56 GMT

Hi @NithyaKB ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

Re: When using Direct Query to SQL Server - the visual are not loading in the service

NithyaKB — Thu, 30 Jun 2022 05:25:49 GMT

Hi
My issue has been fixed.The problem was the gateway was not in the same instance as of sql server.Once the gateway was created in the same machine it fixed my issue.