Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
anuradha12345
New Member

Power bi Desktop in Power BI Report Server

Hello All,

 

Can we connect 2 or more different SQL Servers with same Direct query mode in Power Bi desktop for Power BI report server 

Issue : When i try to connect first SQL Server with direct query , the second SQL Server connection automatically switches to IMPORT  mode 

 

 

Thanks & Regards's

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @anuradha12345 ,

PowerBI offers two data access mechanisms; Import and DirectQuery.

DirectQuery provides a range of benefits, the chief of which is that data is automatically refreshed. DirectQuery doesn't import any data into PowerBI, instead it queries the data sources as you interact with visualisations, making it the best option for larger datasets.

However, it is only possible to connect to one SQL Database as a data source when you use DirectQuery. If you try to add a second database as a data source, you'll be notified that it would be necessary to switch to Import mode to continue. This is the case even if both databases are on the same server.

vluwangmsft_0-1648108031816.png

 

Azure SQL Database Elastic Query provides a way round this problem. It allows you to run T-SQL queries across multiple databases, though adding an external table to one of the databases, which draws its data from a table in the other database. By setting up an external table in a database, you can create a PowerBI report which uses DirectQuery, but can indirectly access data from another database through it.

 

To learn more details ,refer:

https://endjin.com/blog/2017/01/creating-a-powerbi-report-with-directquery-and-multiple-sql-database... 

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @anuradha12345 ,

PowerBI offers two data access mechanisms; Import and DirectQuery.

DirectQuery provides a range of benefits, the chief of which is that data is automatically refreshed. DirectQuery doesn't import any data into PowerBI, instead it queries the data sources as you interact with visualisations, making it the best option for larger datasets.

However, it is only possible to connect to one SQL Database as a data source when you use DirectQuery. If you try to add a second database as a data source, you'll be notified that it would be necessary to switch to Import mode to continue. This is the case even if both databases are on the same server.

vluwangmsft_0-1648108031816.png

 

Azure SQL Database Elastic Query provides a way round this problem. It allows you to run T-SQL queries across multiple databases, though adding an external table to one of the databases, which draws its data from a table in the other database. By setting up an external table in a database, you can create a PowerBI report which uses DirectQuery, but can indirectly access data from another database through it.

 

To learn more details ,refer:

https://endjin.com/blog/2017/01/creating-a-powerbi-report-with-directquery-and-multiple-sql-database... 

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

d_gosbell
Super User
Super User

No I think Direct Query only supports a since data source. This is because Power BI attempts to minimize the number of queries and the amount of data sent back and forth by doing as much work as possible on the source server. It does this by an operation called query folding where if you drag 2 columns from 2 different tables it can generate a SQL query with the appropriate JOIN and GROUP BY clauses.

 

One possible work around would be to create a linked server on one of your SQL Servers to the database on the other SQL Server then create a views in the first database that references each of the tables you need from the second. Just be warned that this will probably work OK if the tables in the second database are not too big, but if you have large tables in both databases this approach will generate a lot of data transfer between the two servers and the performance could be really bad. In this case looking for an approach using import mode would probably be best.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors