https://community.fabric.microsoft.com/t5/Developer/Best-practices-for-Direct-Query-Scaling/m-p/3349479#M43607 <P>Hello,</P><P>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.</P><P>The query is not terribly complex. It has a few subqueries, many joins on 10 tables, some of which are &gt; 1 million rows and outputs only ~5k rows.</P><P>What are some potential solutions and best practices given this situation? Here are some things I am considering<BR />-Use staging tables with SQL Server Agent job<BR />-Send data to our datalake in Snowflake instead of querying the production database (not sure how to productionalize this, I made a Python demo which sends the data to Snowflake, so in theory this could work). Maybe use third party service for this?<BR />-Enable CDC in database (again not sure which product(s) we would use after this is enabled)<BR />-Use Azure Synapse Analytics since it is purpose built to handle Direct Query at scale (what would be the best practice to get the data in Azure?)</P><P>Any help, ideas, suggestions are greatly appreciated.</P><P>Thanks for your time,<BR />Joe</P> Tue, 25 Jul 2023 19:27:29 GMT jgmendez 2023-07-25T19:27:29Z https://community.fabric.microsoft.com/t5/Developer/Best-practices-for-Direct-Query-Scaling/m-p/3349479#M43607 <P>Hello,</P><P>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.</P><P>The query is not terribly complex. It has a few subqueries, many joins on 10 tables, some of which are &gt; 1 million rows and outputs only ~5k rows.</P><P>What are some potential solutions and best practices given this situation? Here are some things I am considering<BR />-Use staging tables with SQL Server Agent job<BR />-Send data to our datalake in Snowflake instead of querying the production database (not sure how to productionalize this, I made a Python demo which sends the data to Snowflake, so in theory this could work). Maybe use third party service for this?<BR />-Enable CDC in database (again not sure which product(s) we would use after this is enabled)<BR />-Use Azure Synapse Analytics since it is purpose built to handle Direct Query at scale (what would be the best practice to get the data in Azure?)</P><P>Any help, ideas, suggestions are greatly appreciated.</P><P>Thanks for your time,<BR />Joe</P> Tue, 25 Jul 2023 19:27:29 GMT https://community.fabric.microsoft.com/t5/Developer/Best-practices-for-Direct-Query-Scaling/m-p/3349479#M43607 jgmendez 2023-07-25T19:27:29Z