Import method - SQL Server connection - Query to join data from two different servers?

[Anonymous]

Hi,

I'm looking to build a datamodel in Power BI to provide the end-user for them to build their reporting upon. The data for this data model comes from two different SQL databases, on different servers.

The long-term plan is to integrate this data into Hadoop and them build a single datamart than contains the data from both SQL databases (SQL >> Hadoop >> Power BI). With this approach the end-user will receive a datamart that holds a single flat-table.

The data integration to Hadoop will not happen soon, but the end-user requires the data now to build their reports. A short-term plan will be to import (Import method) the data from the two separate SQL databases (different servers) into Power BI.

Is there a way to import the SQL data using a query than will bring in the data as single flat-table? The SQL Server connection specifies a single server/database per connection.
If I can do this then the dataset will enter Power BI in the same format as it will when it comes from the Hadoop datamart. Therefore I should simply be able to change the connection string from being a SQL connection to a Hadoop connection and everything on top will remain the same. This includes any data modeling and calculations done by the end-user.

I'm open to ideas on what is the best approach other than my suggested one??
I know a flat-table isn't ideal but once brought in, by using a SQL connection or Hadoop connection, it can be normalised. The whole approach is dictated by how Hadoop will provide the data which will be a single flat-table.

Thanks.

[v-yuezhe-msft · Accepted solution]

@Anonymous,


You can create link server in SQL Server,then use Join query to combine data from two databases of different SQL Server instances into a flat-table. There is an example for your reference.



Regards,
Lydia

[Anonymous]

@v-yuezhe-msft Thanks Lydia.