DataFlow based Data Warehouse vs Azure SQL Best Practices

[Imrans123]

Hey All, 

Was wondering if anyone could help. Currently I have multuple SQL Server Sources which I all append together to create my data source. For each SQL Source, I create a dataflow with the relevant gateway and I use a bunch of native queries to bring in the data in the format I want. Then I create another data flow appending them all together.

Was wondering if anyone could help. I have been having a lot of speed issue with making changes to data flows as I have many unloaded queries which all append to consolidated queries. I was wondering whether creating a datawarehouse without apppending  everything in dataflows would speed things up?

If that's the case, if I were to use Azure SQL and Azure Data Factory, would I be able to use the existing On-Premise Data Gateway installed to query data into Azure SQL? 

Note: All tables put together we are talking about 15-16 million rows of data.

[edhans]

It depends on where you store your data, but Azure SQL doesn't need a gateway, nor do most Azure * sources.

The below article has more info, but you will see most Azure services allow a gateway, but don't require one. Depends on your setup.

Power BI data sources - Power BI | Microsoft Docs

[Imrans123]

Hey Edhans, 

Thanks for your answer. I would get an Azure SQL Subscription where I would store all my data and use that as my Power BI Data source. 

The reason why I bring up the gateway is because currently, I have some remote computers with SQL server installed. I am querying data from those remote servers into power bi and then appending them together within power BI dataflows. I am able to connect to these remote servers through power BI dataflows as I have on-premise gateway installed on each of these remote servers.

If I were to shift to appending this data on my Azure SQL instance, how would I query the data from the remote source?

[edhans]

Azure SQL is a cloud source, so you'd just log in using your MS Account. No gateway needed. But if you went extra steps with security, put it behind a VPN, etc. then you'd need to access it via the VPN on your dev machine, and a gateway would be necessary then for the service.