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

  • Question

  • 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.

    • Moved by pituachMVP Wednesday, January 23, 2019 12:28 PM Was asked in Azure forum and the user agreed it better fit for Power BI
    Tuesday, January 15, 2019 2:33 PM

Answers

  • Hi BharathRS. You can do this in PQ inside PBIDesktop by importing the individual Customer tables, each as its own query. Then disable the loading of these queries. Finally, append them together into a combined Customer query that is loaded.

    This will result in only one dataset for the combined customer data.

    Ehren

    Thursday, January 24, 2019 7:46 PM
    Owner

All replies

  • 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.

    Good day Bharath,

    This forum is for Azure SQL Database and it seems like your question is not related to this topic but to Power BI. Please confirm, and we will move the trhead to the right forum


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, January 15, 2019 5:45 PM
  • Hi Sorry for the delayed answer. Yes please change forum and sorry for confusion.
    Wednesday, January 23, 2019 6:56 AM
  • Hi BharathRS. You can do this in PQ inside PBIDesktop by importing the individual Customer tables, each as its own query. Then disable the loading of these queries. Finally, append them together into a combined Customer query that is loaded.

    This will result in only one dataset for the combined customer data.

    Ehren

    Thursday, January 24, 2019 7:46 PM
    Owner
  • You could use a SQL-script with a UNION so that the tables will come from the server(s) as you like already or use the following technique: https://www.howtoexcel.org/power-query/how-to-extract-data-from-multiple-webpages/ 

    Just replace the list/table of URLs with a list/table of SQL-Server-connections strings (use that as the variable in the function you define in step 1) 


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, February 3, 2019 6:23 PM
    Moderator