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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.