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

[NithyaKB]

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?

[Anonymous]

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

[Anonymous]

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

[Daryl-Lynch-Bzy]

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.

[NithyaKB]

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.

[NithyaKB]

Hi @Daryl-Lynch-Bzy 

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

[NithyaKB · Accepted solution]

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.