Multiple databases connection

[Mbaktha91]

Hi BI family , 

I have a question about connecting to multiple databases. So I have a SQL query lot of joins to connect tables from 3 different databases so I can generate tables based on that . But I am trying to do visualization based on this in power bI. But I get to connect to only one database at a time . Is there a solution for this? 

[rubayatyasmin]

Hi, @Mbaktha91 

1. Query Folding: Use Power Query in Power BI to connect to multiple databases. But remember, it doesn't support joining tables across different databases.

2. ETL Approach: Create a separate process to extract data from your databases, perform the joins, and load results into a consolidated database or data warehouse. Then, connect Power BI to this source.

3. Power BI Dataflows: Connect to multiple sources, transform data, and load into a common model. Useful for joining tables across databases, but has its own limitations.

4. Custom SQL Query: If connecting to a SQL Server database, use a custom SQL query to JOIN tables from different databases. Works only when databases are on the same SQL Server instance.

Helper document:  

https://learn.microsoft.com/en-us/power-query/power-query-folding

https://community.fabric.microsoft.com/t5/Desktop/Custom-SQL-instead-of-table-or-view/m-p/96463

https://learn.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-introduction-self-ser...