Can a data source have multiple servers connection string in Power BI?

1
votes

I have multiple databases which have similar schemas. I need to combine the data from all these databases and do reporting over it.

For example - Customer table in AdventureWorks in Server 1 Customer table in AdventureWorks in Server 2 Customer table in AdventureWorks in Server 3

Now in Power BI i will have a data set called Customer. The data for this needs to come from all the 3 servers mentioned above. I know I can do it using merge queries in Power BI but it means I will have to pull the data from different server as different datasets in power bi and merge which I want to avoid.

Do let me know if there is any other way to do this.

1
powerbipowerbi-desktoppowerbi-datasource
Are these servers all the same type of database? If so, you could probably link them together and create a view in one database to the other databases. Add the tag of the database type that you are using to get assistance from those knowledgeable. – Degan
First, you need to append queries, not merge them, and second - why do you want to avoid this? Your model is the place where you combine the data from different data sources. – Andrey Nikolov

1 Answers

0
votes

You must use Edit queries -> Append queries (or Append queries as new) to combine the data from these data sources into one table:

enter image description here

I find Append queries as new more meaningful in this case. It will create a new (fourth) table, which will contain all the rows from the other 3 tables. Select Three or more tables and select all customers tables from your data sources:

enter image description here

Then use this new table in the report.

You should not worry that duplicating the data will lead to increased size of the data. The data itself will be reused and not copied twice.

Your model is the right place for combining data from different data sources in one place. Maybe this should not be your report, but one dataset shared between your reports, or a SSAS model. Combining the data on SQL Server level (e.g. partitioned view over 3 databases) is not a good idea. Combining these in your model also gives you the option to combine data from different types of data sources, e.g. Customers 1 is in SQL Server, Customers 2 is .csv file in OneDrive and Customers 3 is coming from a web service call.