Suppose I have hosted on the same Resource on Azure 4 Web APIS on .NET Core with SQL Server. Each system has their own SQL Server Database on Azure. The systems are communicated using Azure Service Bus.
My objetive is to use Power BI or a similar software to create dynamic reports without impacting the performance of each application.
But the issue that I am facing is that I dont know where and how to store that (replicated) data that I want to view in a future with the previously mentioned Software.
I dont need all the data of all databases, only a few tables of each system.
For example: From System 1, I want the Data of the table Clients, from System 2 I want the data of the table Transactions and so on.
I dont want to affect the performance of the Databases of each system when I generate the reports, so I thought that maybe Azure has a way to replicate that tables in some place automatically.
To do this, what Azure Service do I need? Azure Synapse Analytic (DW)? Also do I need another product? Or maybe is there a better approach?
I will really appreciate some info to achieve what I want using Azure
Thanks in advance,