Failed to load data

[linch]

I load 3 tables from the same Azure SQL database. one of the table can be loaded successfully but the other 2 tables can't be loaded. The report works well in desktop app, the issue only reported by PowerBI service.

[v-zhangti]

Hi, @linch 

Make sure that the connection mode of the 3 tables is the same? Is it possible that the tables that can be loaded are imported by you and the other two tables are connected by Direct Query?

When using DirectQuery with an Azure SQL Database and a private IP address, an on-premises gateway is required.

Refer to the following documentation to check if you have violated DirectQuery's precautions.

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs

Azure SQL Database with DirectQuery - Power BI | Microsoft Docs

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[linch]

From the setting page, I don't need a gateway.

Gateway connection

You don't need a gateway for this dataset, because all of its data sources are in the cloud, but you can use a gateway for enhanced control over how you connect. 

[linch]

Hi, Zhangli

Thanks a lot for your reply!
I created a blank dashboard to test the data sources connectivities. It turns out that the three tables I mentioned in the original question are all loaded failed. The credential of the Azure SQL db is AAD based, when I refresh the dataset, it will not report a refresh failure, but the table will not loaded. Here is the details:

Activity ID: f20bbd9c-c9a6-460e-9172-613180361401
Request ID: 0a0f97bd-6745-0bff-d5f1-cea5e15ec582
Correlation ID: ac785fe8-1f03-56a5-e3c0-18ed7e779479
Time: Mon Aug 29 2022 16:04:03 GMT+0800 (GMT+08:00)
Service version: 13.0.18988.36
Client version: 2208.5.13018-train
Cluster URI: https://df-msit-scus-redirect.analysis.windows.net/

Here is the refresh history of the dataset:

[v-zhangti]

Hi, @linch 

In your case, the refresh can be done, but the table cannot be loaded. Considering that data access is restricted, what are the permissions of your account that accesses the data source? Does the report itself use RLS?

Best Regards

[linch · Accepted solution]

Hi, v-zhangli, thanks for your help! I have figure out the gap of good data table and bad table. The data source of the bad table must be set as "Import" instead of "Direct Query". However, I have some other Azure SQL database data source in the same report work with "Direct Query". All the datasource are AAD credentials. Maybe it's due to different setting in sql db?