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.
