How to get data from AWS SQL Server to Azure for scaling Power BI Direct Query Reports

Question

Hello,

We have developed some Direct Query Power BI reports which connect to our AWS SQL Server database and refresh every 5 minutes. Leadership loved the POC and wanted to scale to multiple locations, but now we are finding it creates to heavy a load on the server so have paused the project.

The query is not terribly complex. It has a few subqueries, many joins on 10 tables, some of which are > 1 million rows and outputs only ~5k rows.

After doing some research on potential solutions, it seems Azure Synapse Analytics might be a great option since it is purpose built to handle Direct Query at scale.

What would be the best practice to get the data in Azure so I can scale these direct query reports? Would it be better to enable CDC and then stream the data to Azure?

Not sure what steps I would take to accomplish this or the specific products I would need to use.

Any help, ideas, suggestions are greatly appreciated.

Thanks for your time,

Joe

Answer 1

Hi Joe Mendez,

Thank you for posting query in Microsoft Q&A Platform.

Yes, Azure Synapse Analytics is a great option for handling Direct Query at scale. To get the data into Azure Synapse Analytics, there are a few different options you can consider:

Use Azure Data Factory to copy the data from your AWS SQL Server database to Azure Synapse Analytics. You can use the Azure Synapse Analytics connector in Azure Data Factory to copy the data. This approach is best if you need to copy the entire database or a large amount of data.

Use Azure Databricks to stream the data from your AWS SQL Server database to Azure Synapse Analytics. You can use the JDBC connector in Azure Databricks to connect to your AWS SQL Server database and stream the data to Azure Synapse Analytics. This approach is best if you need to stream the data in real-time or near real-time.

Use Change Data Capture (CDC) to capture changes in your AWS SQL Server database and stream them to Azure Synapse Analytics. You can use the Azure Stream Analytics connector in Azure Synapse Analytics to capture the changes and stream them to Azure Synapse Analytics. This approach is best if you only need to capture changes to the data and not the entire database.

Once you have the data in Azure Synapse Analytics, you can use it as a data source for your Direct Query Power BI reports. You can also use Azure Synapse Analytics to perform data transformations and aggregations to optimize the performance of your reports.

Here are some steps you can follow to implement the first option using Azure Data Factory:

Create an Azure Data Factory instance in your Azure subscription.

Create a linked service for your AWS SQL Server database in Azure Data Factory. You can use the Azure SQL Database connector in Azure Data Factory to create the linked service.

Create a linked service for your Azure Synapse Analytics workspace in Azure Data Factory. You can use the Azure Synapse Analytics connector in Azure Data Factory to create the linked service.

Create a pipeline in Azure Data Factory to copy the data from your AWS SQL Server database to Azure Synapse Analytics. You can use the Copy Data activity in Azure Data Factory to copy the data.

Schedule the pipeline to run on a regular basis, such as every 5 minutes, to keep the data in Azure Synapse Analytics up-to-date.

Once you have completed these steps, you can use Azure Synapse Analytics as a data source for your Direct Query Power BI reports.

Hope this helps. Please let me know if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well.