Best Practices for working with multiple Databases

[Imrans123]

Hello, 

Was wondering if anyone could help suggest best practices for a new project I am doing.

I have multiple SQL Server sources

For each source, I am creating a DataFlow and writing SQL Queries for each table, to transform the data to the column names that I want in my Power BI report and thereby avoiding most, if not all Power Query transformation (i.e. adding custom column, concatenating etc all done on the SQL Query). For this phase, my question is 

1. I am currently using Native Query (i.e. from within power BI, I select New Data Source > SQL Server > and in Advanced Options I am typing in my query.) Is it faster to have a saved view in the SQL Server Management Studio from where I am querying? Or is Native Query the same thing with respect to scalibility?

ONce I bring in my data from all my SQL Sources, all transformed to have the same column names, I will create a consolidated DataFlow where I append every table from all sources to create consolidated tables. Here my question is,

2. Is it faster to import all the data from all my SQL Sources into another Database instance like SQL Server, AzureSQl etc. and query from that consolidated Database? Or is appending the same thing with refards to Scalibity. 

I am looking into possibility multi million rows when consolidating all my SQL Sources, so hoping to start off with the best practices so it can scale smoothly. 

Thank you!

[aj1973 · Accepted solution]

Hi @Imrans123 

1- Yes it is always better to get a view directly from the source, faster to import a saved view.

2- Yes it is better to consolidate all the source into a Data Lake type of things.

[Imrans123]

Thanks AJ. Just out of curiosity, would you happen to know the refresh procedure with Power BI's native query is incremental?

Let's say with the query, I have loaded 100,000 rows. Before my next refresh, another 100 rows of data has been added in the source database. If I were to refresh the query, would Power BI Dataflows load the 100 new rows of data over the existing 100,000 rows, or will it load 100,100 as new.

[aj1973]

Yes, that's called incremental refresh feature in Power Bi. It is used for that purpose however you will need Premium Capacity License for that or PPU Premium per user, it depends on your needs.