Which is best option for an environment with analytic reporting to be done in the cloud and require on premise data and it require frequent updates during the day

Question

Hi,

I would like to understand what is the best solution to implement with the following requirements:

Cloud environment for analytical reporting where data will be sourced from on premise and then combined with data loaded from excel along with have frequent updates of extracts from on premise

Answer 1

with the current scenario of the your sources being an excel file in your local system and on prem database,

You can use ADF with the below aspect:

1. Set up a Self hosted IR within an Onprem server via which you can access the On Prem database
2. Set up a Self hosted IR on your local system/laptop containing the excl file (ideally would prefer having that file in sharepoint or some teams location)

Use ADF copy activity to copy from on prem database to blob location(intermediate)

Use ADF copy activity with File share as the source to copy the excel from local to blob(intermediate)

use data flow to merge both the sources and do the necessary transformations and use that as a source within your powerbi report which would contain your analytics